Convert CSV(s) to a SQLite database
Overview
This article is a “quick and dirty” on how to convert a directory of CSVs to a SQLite database.
The reason for this article is because I was in a situation at work where I had to analyze several CSVs, however, the issue is that I’m better/faster with SQL and Python than I am with Excel 😅, so I wrote a script to do the conversion.
I’m aware that there are several solutions online, but there’s more than one way to skin a cat. This is just my solution. Hopefully, it makes someone else’s job easier/faster.
Github gist
Code Breakdown
First, we’ll need to create a connection to a database — for that, we’ll use SQLAlchemy. Note the extension on the database name is .sqlite.
Next, assuming we are in the directory with the CSV file(s), we’ll get the path of the current working directory. Using glob, we’ll get a list of CSV file(s) in the directory. From there, we can iterate over that list.
For each file, split it based on the \\ and take the last element (the actual filename) and create a pandas dataframe from the CSV.
The next two commands do a bit of clean up for the headers and table name (you may need to customize this part to suite your needs).
Finally, we write the dataframe to the SQLite database.
Basically, a new table is created for each CSV in the directory.
Extras
Also, I found a neat little VSCode extension that lets you view a SQLite database within VSCode as long as it has a .sqlite extension
