Notes

SQLite - Import a CSV file into a SQLite database

author: Paul Kim

categories: sqlite

tags: sqlite

SQLite

In this post, I will go over how to import a CSV file into a SQLite database from the command line.

Here is the sample CSV file: CADRUproj_2010-2060.csv

And here is how we import the CSV file into a SQLite database called pop_proj.sqlite in a table called firstTable:

# if a sqlite database called `pop_proj.sqlite` exists, open it
# otherwise, create it
sqlite3 pop_proj.sqlite

# show a list of all databases open in the current connection
sqlite> .databases

# import csv data into a SQLite table called 'firstTable'
sqlite> .mode csv
sqlite> .import CA_DRU_proj_2010-2016.csv firstTable

# exit
sqlite> .quit

When importing CSV files, SQLite assumes all fields are text fields. We need to perform some extra steps in order to set the correct data types. Basically, we need to recreate the table with the correct data types and import our data to the new table.

First, we need to get the schema for the table so we can recreate the schema with the correct data type. The following commands will create a new file called alterTable.sql which contains the schema for firstTable from the pop_proj.sqlite database.

# if a sqlite database called `pop_proj.sqlite` exists, open it
# otherwise, create it
sqlite3 pop_proj.sqlite

# output the next SQL command to 'alterTable.sql'
sqlite> .once alterTable.sql

# output the schema for 'firstTable'
sqlite> .schema firstTable

# exit
sqlite> .quit

Here are the contents of alterTable.sql. As you can see, all fields are of type TEXT:

CREATE TABLE firstTable(
  "County Code" TEXT,
  "County Name" TEXT,
  "Year" TEXT,
  "Race Code" TEXT,
  "Race Name" TEXT,
  "Gender" TEXT,
  "Age" TEXT,
  "Population" TEXT
);

We will now modify alterTable.sql so that the table schema has the correct data and so that we can execute the script to migrate our data to the correct schema.

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE firstTable RENAME TO _firstTable_old;

CREATE TABLE firstTable(
  "County Code" INTEGER,
  "County Name" TEXT,
  "Year" TEXT,
  "Race Code" REAL,
  "Race Name" TEXT,
  "Gender" TEXT,
  "Age" INTEGER,
  "Population" INTEGER
);

INSERT INTO firstTable ("County Code", "County Name", "Year", "Race Code", "Race Name", "Gender", "Age", "Population")
  SELECT "County Code", "County Name", "Year", "Race Code", "Race Name", "Gender", "Age", "Population"
  FROM _firstTable_old;

COMMIT;

PRAGMA foreign_keys=on;

Now, we can execute SQL in alterTable.sql and drop the old renamed table:

# if a sqlite database called `pop_proj.sqlite` exists, open it
# otherwise, create it
sqlite3 pop_proj.sqlite

# execute the SQL in 'alterTable.sql'
sqlite> .read alterTable.sql

# delete old table
sqlite> drop table _firstTable_old;

At this point, we have imported our data into SQLite database using the correct data types.

Source

ChromeEdgeFirefoxOpera

© 2021 paulkode.com. All rights reserved.