Search code examples
csvsqlitesqlite-shell

import csv to sqlite3, but change the type of column before import

I am working with one csv, which has many columns. I decided I'll import the data via sqlite3 shell and I found this very useful:

.mode CSV
.import my_table.csv my_sqlite_table

This saves me a lot of work, on the other hand it gives me no control over column and value characteristic as all the data is TEXT

Is there any elegant way within shell to first address what type the column should be or to fix particular blank values with null?

Solution

  • Values in CSV files always are strings.

    To change that, import into a temporary table, and then modify the values appropriately:

    INSERT INTO the_actual_table(a, b, c)
SELECT a, CAST(b AS INTEGER), nullif(c, '') FROM temp_table;