The simplest way to turn multiple CSVs into SQL

A low-code and fast way to transform your plain text files to more useful data structures (read tables).

Team Data21 on csv sql import python

This is very likely the simplest method to import multiple CSV files into SQL database.

Once you have your data in the tables, you can unleash your inner Data Analyst and start getting useful insights using good ol' SQL.

Sample data

The example below uses Crunchbase sample CSV export. You can get it here.

Python + Pandas = 💪

Everyone who works with data should be grateful for Python and Pandas. And if you add SQL to your tool set, you'll become an invincible Data Analyst, Data Engineer, Data Scientist or whatever the title du jour is.

Python reads almost like a plain English. Even if you are not a programmer, you should understand the following code.

from glob import glob
import os
import pandas as pd
import sqlite3

# Useful variables
source_files = './*.csv'  # All CSVs in current folder
destination_file = 'crunchbase.db'  # SQLite DB file

# Create SQLite connection and file at the same time
with sqlite3.connect(destination_file) as con:
  # Traverse all CSV files
  for csv_file in glob(source_files):
    # Use pandas for reading
    df = pd.read_csv(csv_file)
    # Get filename
    file_name, file_ext = os.path.splitext(os.path.basename(csv_file))
    print(f'Processing {file_name}...')
    # Create or replace the table
    df.to_sql(file_name, con, if_exists='replace', index=False)

print('Done')

Although the code above is obvious we still decided to enhance it with the comments. We strongly believe in commented code no matter what some people say.

What you get

The result of our low-code effort is a SQLite database containing CSV files turned into tables.

SQLite format has several advantages:

1. One file contains everything.
2. It can be used locally.
3. It can be easily shared with your colleagues.
4. There are many online and offline GUI tools available.
5. Even the command line (CLI).
6. It can store hundreds of terabytes of data. Yes, terabytes!

Tools

We used following free tools to make it as simple as possible. The tools work on all major OS's and are easy to use.

Thonny (Python)

Thonny refers to itself as Python IDE for beginners. Our experience is it can serve well even for advanced users and data analysts.

The great thing is it already contains built-in Python. Therefore the user does not have to install Python. Good-bye to bloated Python distributions!

DB Browser for SQLite (SQL)

DB Browser for SQLite (DB4S) is easy yet powerful tool with tongue-twisting name. It allows you to work with SQLite databases and do SQL analysis on your local machine.

It can also display some basic visualization. All that with small footprint on your operating system.

If you are currently in search for skilled Data Analysts, we might be able to help you. Contact us to discuss your needs.