How to import csv file to sqlite with correct data types

Question

When I import a csv file to sqlite database, it imports number as string to integer column, how can I fix this? A line from my csv file like this:

 31,c,BB ROSE - 031,c31,,9,7,0,"142,000",0

Raymund

Please include the schema of your table. – Fibril

csvsql from csvkit almost does it nicely: csvsql --db sqlite:///main.sqlite3 --insert main.csv but it's type inference is terrible (1, 2, 3 get FLAT not INTEGER) and I don't see how to set it explicitly. – Casual

Answer 1

CSV files do no have data types; everything is a string.

To convert all values in a column into a number, use something like this:

UPDATE MyTable SET MyColumn = CAST(MyColumn AS INTEGER)

Chorale

This doesn't work. You can't modify a column like that in SQLite. The column is unchanged. – Uppercase

@Uppercase It's likely that you imported into a table where the column was declared with a text affinity. – Chorale

I didn't import into any existing table - you get text tables if you import CSVs into a new table, which is what the question was. The fix is to create the table first - and then you'd do it with the right types and not need to do any UPDATEs anyway. – Uppercase

Answer 2

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_date
1001,adams,john,2010-12-12
1234,griffin,meg,2000-01-01
2233,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.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 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.sqlite
sqlite> .once employees.sql
sqlite> .schema employees
sqlite> .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.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 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.

Quantum

Answer 3

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 ON
select * from table1 order by wert;

Interlude

You can now skip the header with .import --csv --skip 1. – Casual

Answer 4

In SQLite, you cannot change the type affinities of columns. Therefore you should create your table and then .import your CSV file into the table. If your CSV file has a header, that will be treated as data upon import. You can either delete the header before importing (in the CSV file), or delete the header after import (in the table). Since the typeof all the header fields will be TEXT, you can easily find this header in a table where some columns have numeric type affinities.

Semipalatinsk

In case you are interested in how to do that: See if you would delete the right thing (safety first) SELECT * FROM MyTable WHERE MyColumn = 'HeaderName', then delete DELETE FROM MyTable WHERE MyColumn = 'HeaderName' – Donn

Answer 5

1. Import CSV file into SQLite.
2. Go to Database Structure and select imported CSV file
3. select modify table from the tab
4. select field one and change name to desired name of column.
5. Next select the desired data type from the drop down menu. You can now change from Text to Integer or Numeric depending on the data you are working with

Tenpins

Answer 6

I am using sqlite 3.39.4, I would do as follows:

as suggested above create a new table 'newtable' with the right types, then to import data from your 'mycsvtable.csv', type

.mode csv
.import --skip 1 mycsvtable.csv newtable

the --skip 1 avoids the first row if you have headers in your csv

Pham

Answer 7

Some tests on .import --csv with pre-existing integer column

This technique was mentioned at: https://mcmap.net/q/531987/-how-to-import-csv-file-to-sqlite-with-correct-data-types but I wanted to make some quick edge case tests to understand better what works and what doesn't:

tmp.csv

1
01
10
abc
1.1
0xa

Then import:

sqlite3 tmp.sqlite 'create table t(x integer)'
sqlite3 tmp.sqlite '.import --csv tmp.csv t'
sqlite3 tmp.sqlite '.mode line' 'select x, x+1, x*2, x=0, x=1 from t'

Output:

    x = 1
  x+1 = 2
  x*2 = 2
  x=0 = 0
  x=1 = 1

    x = 1
  x+1 = 2
  x*2 = 2
  x=0 = 0
  x=1 = 1

    x = 10
  x+1 = 11
  x*2 = 20
  x=0 = 0
  x=1 = 0

    x = abc
  x+1 = 1
  x*2 = 0
  x=0 = 0
  x=1 = 0

    x = 1.1
  x+1 = 2.1
  x*2 = 2.2
  x=0 = 0
  x=1 = 0

    x = 0xa
  x+1 = 1
  x*2 = 0
  x=0 = 0
  x=1 = 0

So 1 and 01 both appear to be the correct numerical 1 as desired as far as I can tell.

Hex 0xa doesn't work.

Anything else becomes random binary noise that I don't fully understand or care about right now.

Tested on SQLite 3.40.1, Ubuntu 23.04.

Do it in Python

Python CSV to SQLite

Perhaps this will be the best method if you need anything slightly more flexible. The big question is if whether this will be slower than other methods on a large table, but I'm lazy to benchmark now.

Casual

Answer 8

I found I could live with the TEXT values, as all I needed to do to get them to sort as numbers was to use e.g., "ORDER BY longitude + 0". Voila, no matter negative or positive, my longitudes sorted correctly.

Yes, the "+ 0" conversion isn't for everybody. But if all you are trying to do is a quick correct ORDER BY, perhaps that's all you need!

Mendelson