Converting CSV to a SQLite Database

| programming | databases |

As a part of my data science course on EdX we have been working with a lot of csv files. I spoke SQL long before I spoke Pandas and I find that it is much easier to do initial exploration of the data using raw SQL queries compared to the Pandas DSL.

Kaggle is a great repository full of useful data sets that are ripe for exploration. While a lot of these data sets come in both csv and sql flavors, some of them are CSV only. Using SQLit we are able to easily import these csv files into a database and then run queries for further data exploration. 

Im going to use the kickstarter data set for this tutorial, feel free to download the csv files from kaggle so that you can follow along. 

Pre Requisites

Make sure that you have SQLite installed before getting started with this tutorial. 

Steps to Convert CSV to SQLite

First, Download the data set from kaggle, this will come in the form of a zip file. Unzip this and open up a terminal in the directory where you have the new unzipped kickstarter-projects folder. 

In your terminal open up a new sqlite session followed by the name of the file that you want to save your new database to. 

sqlite3 ks.db

Inside of the sqlite shell, change the mode to csv. 

.mode csv

Import the csv file, and add the name of the table that you want the data to be imported into. 

.import kickstarter-projects/ks-projects-201801.csv ks

Verify that everything was imported correctly. Take a look at the schema, and first couple of rows. Your output should look something like this: 

sqlite> .schema ks

CREATE TABLE ks(
"ID" TEXT,
"name" TEXT,
"category" TEXT,
"main_category" TEXT,
"currency" TEXT,
"deadline" TEXT,
"goal" TEXT,
"launched" TEXT,
"pledged" TEXT,
"state" TEXT,
"backers" TEXT,
"country" TEXT,
"usd pledged" TEXT,
"usd_pledged_real" TEXT,
"usd_goal_real" TEXT
);


sqlite> select * from ks limit 5;

1000002330|The Songs of Adelaide & Abullah|Poetry|Publishing|GBP|2015-10-09|1000.00|2015-08-11 12:12:28|0.00|failed|0|GB|0.00|0.00|1533.95
1000003930|Greeting From Earth: ZGAC Arts Capsule For ET|Narrative Film|Film & Video|USD|2017-11-01|30000.00|2017-09-02 04:43:57|2421.00|failed|15|US|100.00|2421.00|30000.00
1000004038|Where is Hank?|Narrative Film|Film & Video|USD|2013-02-26|45000.00|2013-01-12 00:20:50|220.00|failed|3|US|220.00|220.00|45000.00
1000007540|ToshiCapital Rekordz Needs Help to Complete Album|Music|Music|USD|2012-04-16|5000.00|2012-03-17 03:24:11|1.00|failed|1|US|1.00|1.00|5000.00
1000011046|Community Film Project: The Art of Neighborhood Filmmaking|Film & Video|Film & Video|USD|2015-08-29|19500.00|2015-07-04 08:35:03|1283.00|canceled|14|US|1283.00|1283.00|19500.00

Excellent! Now you can query this entire data set as your normally would. Happy data exploration! 

Thank you for reading! Share your thoughts with me on bluesky, mastodon, or via email.

Check out some more stuff to read down below.

Most popular posts this month

Recent Favorite Blog Posts

This is a collection of the last 8 posts that I bookmarked.

Articles from blogs I follow around the net

MusicBrainz Picard identifies songs from *.mp3 files and automatically fixes metadata

In my first attempt to switch from streaming to move back to listening to *.mp3 files, one of the issues I encountered was organization: how to standardize the metadata of the songs? The solution I was familiar with at the time — manually editing each son…

via Manual do Usuário April 24, 2025

Google's control of the web could be coming to an end

It's been hard to avoid the US government's antitrust case against Meta lately, since CEO Mark Zuckerberg spent three days in front of the cameras in Congress, testifying about his company's alleged anti-competitive tactics. But another equall…

via The Torment Nexus April 24, 2025

$5 million in tokens stolen from ZKsync

An attacker compromised an admin account belonging to the ZKsync Ethereum layer-2 project, which is built by Matter Labs. By doing so, they were able to steal approximately $5 million worth of the ZK token, which the project said wer…

via Web3 is Going Just Great April 24, 2025

Generated by openring