To import a csv file with headers into an existing table, you can use
.import '| tail -n +2 yourfile.csv' yourtable
to skip the header line.
On Sat, Feb 29, 2020, 4:30 AM Christof Böckler wrote:
> Hi,
>
> I want to share some thoughts and make some suggestions about the SQLite
> 3 command line interface (CLI) tool, especially its behaviour when
> importing CSV files.
>
> CSV files are probably even more common for data exchange than SQLite
> database files. I consider it to be good practice to include a header
> line with column names in every CSV file. Metadata should go with the
> data. This prevents me from mixing up two columns that contain numbers
> with similiar value distributions.
>
> Let’s look at an example. A file named data.csv contains three lines:
>
> id,val1,val2
> A,27,8
> B,3,12
>
> Now …
> sqlite3
> sqlite> .import data.csv tab
>
> works and looks good at first, but there is a problem with numerical data.
>
> sqlite> SELECT * FROM tab ORDER BY val2;
> B,3,12
> A,27,8
>
> This is because on import all three columns were created with affinity
> (not to say data type) TEXT (see .schema). As a consequence all numbers
> were imported as strings.
> '12' < '8' is lexicographically OK, but not so in a mathematical sense.
> Having the CSV file in mind I clearly expect to see 8 on the first line
> of the above result.
>
> How to work around this? Just define the table in advance with
> appropriate data types (affinity INTEGER):
>
> sqlite> CREATE TABLE tab (id TEXT, val1 INT, val2 INT);
>
> But now the above .import command will not work as expected, because it
> will result in three rows in our table tab. The first row contains the
> header line.
>
> Two different workarounds come to my mind:
> a) sqlite> DELETE FROM tab WHERE rowid = 1; -- Dirty hack!
> b) sqlite> .import data.csv temp_tab
> sqlite> INSERT INTO tab SELECT * FROM temp_tab;
> sqlite> DROP TABLE temp_tab; -- Two tedious extra lines
>
> Both approaches are not very appealing to me. To make CSV files with a
> header line first class citizens, I suggest this instead:
> sqlite> .import -h data.csv tab
> should ignore the first line of data.csv. This import should fail if
> table tab does not already exist.
> This is both shorter and more elegant than both workarounds.
>
>
> Now on to a second issue. Let’s assume you have sucessfully imported a
> file containing these four lines:
> id,val1,val2
> A,27,8
> B,3,12
> C,,1
> into the table tab mentioned above, resulting in three rows. Notice the
> missing value in column val1 on the last line. This missing value is
> imported as an empty string '' regardlesse of the affinity of column val1.
>
> That leads to (mathematically) unexpected results from aggregate functions:
> sqlite> SELECT SUM(val1) FROM tab;
> 30-- Well, treating '' like 0 is OK in this case
> sqlite> SELECT COUNT(val1) FROM tab;
> 3 -- but not so here; only two values/numbers were given in data.csv
> sqlite> SELECT AVG(val1) FROM tab;
> 10-- the average of 3 and 27 is 15
> sqlite> SELECT MAX(val1) FROM tab;
> ''-- not to be expected when looking at data.csv
>
> OK, I hear you say, what about this workaround:
> sqlite> UPDATE tab SET val1 = NULL WHERE val1 = '';
> This makes the surprises above go away, but it is again tedious to do for
> all columns containing only numbers.
>
> My suggestion is: If someone goes the extra mile and defines a table in
> advance in order to have an appropriate numerical affinity (INTEGER, REAL
> or NUMERIC) for a column, then it is OK to encode a missing value as NULL
> instead of ''. It seems right though to keep the current behaviour for
> columns with affinity TEXT (the default) or BLOB.
>
> To sum things up:
> 1. There should be no penalty for using header lines in CSV files. Thus a
> new flag -h for .import is much appreciated.
> 2. Missing values in columns with numerical affinity should show up as
> NULL values.
>
> Thanks for reading, I look forward to your opinions about these issues.
>
> Greetings
> Christof
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
