Csv2Sql - Load csv files to database

Hi, everyone. I am an elixir nooby and have been learning elixir for about 6 months now. I would like to share a new project I have been working on recently.

My work involved loading lots of CSV files into MySQL databases, this was very problematic, although it was possible to read the CSV files and load it into the database using some tools or scripts, the main problem was to create the database tables before inserting the CSVs. Manually going through each CSV and preparing the query to create the corresponding table was very tedious and time taking. There were some popular tools that I found, but none of them could solve my problem completely, additionally, I also wanted to automate the whole process. So, I decided to solve the problem using elixir, this is how I made csv2sql.

01-16-03-repo_image908×280 14.8 KB

Csv2Sql is a blazing fast fully automated tool to load huge CSV files into a MySQL database.

Csv2Sql can automatically…

• Read CSV files and infer the database table structure

• Create the database and the required tables

• Insert all the CSV into the database

• Validate that all the CSVs have been correctly imported to the database.

I have made an escript, which accepts command-line arguments so you can load a directory full of CSVs into the database just using a single command like:

./csv2sql --source-csv-directory "/home/user/Desktop/csvs" --db-connection-string "root:mysql@localhost/test_csv"

I used nimble CSV which is super fast when parsing the CSV files, I used ecto to easily bulk insert the CSVs into the database, but the best part is everything is done in parallel taking full advantage of elixirs cheap processes, gen servers, and supervisors. That is, multiple CSVs are processed in parallel, inferring the database schema by reading each CSV is also done in parallel, inserting the CSVs into the database is done parallelly, this makes the app very fast and makes full use of the power of the processor. I have used streams, to lazily read huge CSV files, thus it has minimal memory footprint.

I am sure that there is lots of room for improvements and bugs that will surface as I test the app further, but I wanted to share my project with the community.

Check out the project here.

Any suggestions, advice, bug report, or comments are welcome .