Import CSV with spatialite, NULL values (TEXT)

[Peter]

Hi!

I want to import a csv in my database using command line (spatialite.exe).

This CSV has been exported from an Oracle DB so NULL values are empty strings in CSV.

I use ".nullvalue NULL" but when i import my CSV i see empty strings... 

is there any way to see NULL instead of empty strings??

Thanks!

[a.fu...@lqt.it]

Hi Peter,

there are many different ways allowing to import some
CSV/TXT file in SQLite/SpatiaLite.

one is sqlite3 specific, based on the ".import" dot macro
supported by both sqlite3.exe and spatialite.exe

http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles

the other [completely different implementation] is
SpatiaLite-specific, and is available as a dialog box
when using spatialite_gui; the same code base supports
the VirtualText driver as well.


http://www.gaia-gis.it/spatialite-2.3.1/spatialite-tutorial-2.3.1.html#t7

representing NULLs in a portable way isn't a supported
option for any text-based exchange format (the interpretation
would always be purely conventional, euristic and
implementation-depending).
anyway you can choose between many different alternatives,
and at least one of them could probably resolve your problem.

bye Sandro

[Peter]

Hi Sandro,

Thanks for your answer.

I have been checked VirtualText. I see if i use VirtualText to load a .csv, Empty strings become NULL like i want. 

CREATE VIRTUAL TABLE test USING VirtualText(file.csv,'1',COMMA,DOUBLEQUOTE,';')

However when i try to insert this virtual table in my table, i don't get because ROWID is created with VirtualText, so VirtualTable has one more column that my table.

INSERT INTO myTable SELECT * FROM test

Can i solve this problem with rowid? Thanks a lot Sandro!

PD: Sorry for answering three times but i haven't ever used google groups and i don't find an edit option.

[a.fu...@lqt.it]

On Mon, 23 Jun 2014 07:25:02 -0700 (PDT), Peter wrote:
> Hi Sandro,
>
> Thanks for your answer.
>
> I have been checked VirtualText. I see if i use VirtualText to load a
> .csv, Empty strings become NULL like i want.
>

> CREATE VIRTUAL TABLE TEST USING
> VIRTUALTEXT(FILE.CSV,'1',COMMA,DOUBLEQUOTE,';')

>
> However when i try to insert this virtual table in my table, i don't
> get because ROWID is created with VirtualText, so VirtualTable has
> one
> more column that my table.
>

> Can i solve this problem with rowid? Thanks a lot Sandro!
>

Hi Peter,

just creating a VirtualTable isn't really an "import" operation.
all data will still continue to be stored on the external file,
and the VirtualText driver will simply translate the content of
this file as if it was a standard SQL table.

so refining / completing your data import shouldn't be difficult
at all:

a) create your destination table, by specifying an eventual
Primary Key and so on.

b) copy all data from the VirtualText table into the destination
table: you simply have to execute a statement like this:

INSERT INTO zzzzz (fld1, fld2, ..... fldN)
SELECT col1, col2, ... colN
FROM virt_zzzzz;

please note: rearranging column names, ignoring useless
columns and alike would be a really simple task during
this step.


c) and finally you can drop the now useless VirtualText table;
all done.

bye sandro

[Peter]

Thanks for your time Sandro!

I did what you said and it's work Ok!

Thanks! :D