How to import csv file to sqlite with correct data types
When importing csv files, SQLite assumes all fields are text fields. So you need to perform some extra steps in order to set the correct data types.
However, it is my understanding that you cannot use the ALTER TABLE
statement to modify a column in SQLite. Instead, you will need to rename the table, create a new table, and copy the data into the new table.
https://www.techonthenet.com/sqlite/tables/alter_table.php
So suppose I have an employees.csv file I want to import into SQLite database with the correct data types.
employee_id,last_name,first_name,hire_date1001,adams,john,2010-12-121234,griffin,meg,2000-01-012233,simpson,bart,1990-02-23
First, create a SQLite database called mydb.sqlite
and import employees.csv
into a SQLite table called employees
.
# create sqlite database called mydb.sqlite# import data from 'employees.csv' into a SQLite table called 'employees'# unfortunately, sqlite assumes all fields are text fields$ sqlite3 mydb.sqlitesqlite> .mode csvsqlite> .import employees.csv employeessqlite> .quit
At this point, the data is imported as text. Let's first get the employees
schema from the database and save it to employees.sql
.We can use this to create a new script that would rename the table, create a new table, and copy the data into the new table.
$ sqlite3 mydb.sqlitesqlite> .once employees.sqlsqlite> .schema employeessqlite> .quit
You should now have employees.sql with the following schema:
CREATE TABLE employees( "employee_id" TEXT, "last_name" TEXT, "first_name" TEXT, "hire_date" TEXT);
Let's now create a SQL filed called alterTable.sql that would rename the table, create a new table, and copy the data into the new table.
alterTable.sql
PRAGMA foreign_keys=off;BEGIN TRANSACTION;ALTER TABLE employees RENAME TO _employees_old;CREATE TABLE employees( "employee_id" INTEGER, "last_name" TEXT, "first_name" TEXT, "hire_date" NUMERIC);INSERT INTO employees ("employee_id", "last_name", "first_name", "hire_date") SELECT "employee_id", "last_name", "first_name", "hire_date" FROM _employees_old;COMMIT;PRAGMA foreign_keys=on;
Finally, we can execute SQL in alterTable.sql
and drop the old renamed table
$ sqlite3 mydb.sqlitesqlite> .read alterTable.sqlsqlite> drop table _employees_old;
At this point, the imported employee data should have the correct data types instead of the default text field.
If you do it this way, you don't have to worry about headers in csv file being imported as data. Other methods might require you delete the header either before or after importing the csv file.
You just need to create the table first with correct types and then the CSV-import will keep this types, because the table already exists.
Here a sample:
create table table1(name TEXT, wert INT);.mode csv.separator ";".import "d:/temp/test.csv" table1
If you need to delete an imported header-line then use something like this after the import:
delete from table1 where rowid=1;
or use this in case you already did multiple imports into the same table:
delete from [table1] where "name"='name'; -- try to use a name of an INT-column for this.
at the end you can just check the correct import like this:
.header ONselect * from table1 order by wert;