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.
The example below uses Crunchbase sample CSV export. You can get it here.
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.
The result of our low-code effort is a SQLite database containing CSV files turned into tables.
SQLite format has several advantages:
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 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 (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.
Data21.io in your inbox
By subscribing, you agree to receive emails from Data21.io. For details, read our Privacy Policy.