Convert multiple CSV files into multiple db files under different folder structure

[GmGniap]

Hello Simon & Everyone ,
I'm not sure it's the right place to discuss my topic that's mostly related to sqlite-utils tool. There isn't discussion session for sqlite-utils repo , that's why I typed in here. I'm making a Datasette project that built with multiple datasets. My dataset folder structure is like this -

=> Main Folder --> Folder1 --> multiple csv
=> Main Folder --> Folder2 --> multiple csv 

So , I would like to convert these multiple csv into related FolderName.db file. This idea can be similar to Simonw's fivethirtyeight but a little difference in db creating part. If I continue to use sqlite-utils command keys , I've to type tons of lines (maybe, for hundreds of folders) manually. Could I solve that problem in Python code or Bash script and how it'll looks like? I'm loss where I should start. Please , May I request your suggestions for possible solutions?

Sorry if I made dummy question. Thanks for your time.
🙏

[rayvoelker]

Hi GmGniap,

I guess the answer to your question is ... it depends ;-) The good news is that you have lots of options for building your SQLite DB. For me, I've found the easiest pattern is to use some combination of tools like pandas, sqlalchemy (primarily for being able to create the SQLite dialect engine object that pandas can utilize), sqlite-utils, and the other tools from the Python standard library for working with files and file objects

Basically, something like this type of workflow for pulling in multiple .csv files in a Python script (a Jupyter Notebook works great for this task by the way):

1. create a basic table structure in an SQLite DB that will match your .csv files column data
2. build a python list object that consists of your target .csv file names
3. loop through that list, create a new pandas DataFrame object from the .csv files (now is a good time to do any data transformations--use pandas to format dates, normalize currency data to numeric formats, etc.). Use the DataFrame object's to_sql method to connect to your SQLite DB, and then append the data to your SQLite DB.
4. If you want to perform additional operations on your newly formed SQLite DB, like enabling full-text search, extracting columns into separate tables (to normalize your primary database table), and I'm sure a number of other tasks--sqlite-utils greatly reduces the complexity there

Hope that helps a bit ... Good luck!

[plpxsk]

I think you can pass folders directly into csvs-to-sqlite such as:

csvs-to-sqlite folder/*

They will then be nested into folders in the database as well