How to import csv file into sqlite with correct datatypes
In SQLite, you cannot change the affinity type of columns. Therefore, you have to create
your table and then .import
your CSV file to the table. If your CSV file has a header, it will be treated as data when imported. You can remove the header before import (in a CSV file) or remove the header after import (in a table). Since typeof
all header fields will be TEXT
, you can easily find this header in a table where some columns have numeric affinity types.
source to share
When importing CSV files, SQLite assumes that all fields are text fields. Therefore, you need to follow a few extra steps to set the correct data types.
However, as far as I understand, you cannot use a statement ALTER TABLE
to change a column in SQLite. Instead, you will need to rename the table, create a new table, and copy the data to the new table.
https://www.techonthenet.com/sqlite/tables/alter_table.php
Suppose I have an employee.csv file that I want to import into a SQLite database with the correct data types.
employee_id,last_name,first_name,hire_date 1001,adams,john,2010-12-12 1234,griffin,meg,2000-01-01 2233,simpson,bart,1990-02-23
First, create a named SQLite database mydb.sqlite
and import employees.csv
into a named SQLite table 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.sqlite
sqlite> .mode csv
sqlite> .import employees.csv employees
sqlite> .quit
At this point, the data is imported as text. Let's first get the schema employees
from the database and store it in. employees.sql
We can use this to create a new script that will rename the table, create a new table and copy the data to the new table.
$ sqlite3 mydb.sqlite
sqlite> .once employees.sql
sqlite> .schema employees
sqlite> .quit
You should now have employee.sql with the following schema:
CREATE TABLE employees(
"employee_id" TEXT,
"last_name" TEXT,
"first_name" TEXT,
"hire_date" TEXT
);
Now let's create a SQL file called alterTable.sql that will 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 delete the old renamed table
$ sqlite3 mydb.sqlite
sqlite> .read alterTable.sql
sqlite> drop table _employees_old;
At this point, the imported employee data should have the correct data types instead of the default text box.
If you do it this way, you don't need to worry about the headers in the CSV file being imported as data. Other methods may require you to remove the header either before or after importing the CSV file.
source to share
You just need to create the table with the correct types first, and then CSV-import will keep those types, because the table already exists.
Here's a sample:
create table table1(name TEXT, wert INT);
.mode csv
.separator ";"
.import "d:/temp/test.csv" table1
If you need to remove the imported title bar, use something like this after import:
delete from table1 where rowid=1;
or use this if you have already done 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 if the import is correct like this:
.header ON
select * from table1 order by wert;
source to share