Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to import csv file to sqlite with correct data types

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
like image 868
Popoyo Avatar asked Nov 27 '13 10:11

Popoyo


People also ask

How do I import a CSV file into SQLite database?

First, from the menu choose tool menu item. Second, choose the database and table that you want to import data then click the Next button. Third, choose CSV as the data source type, choose the CSV file in the Input file field, and choose the ,(comma) option as the Field separator as shown in the picture below.

Which command is used to import CSV into sqlite3?

You can import a CSV file into SQLite table by using sqlite3 tool and . import command. This command accepts a file name, and a table name.


2 Answers

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)
like image 164
CL. Avatar answered Sep 23 '22 09:09

CL.


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.

like image 25
kimbaudi Avatar answered Sep 20 '22 09:09

kimbaudi