Csv2db is a free tool to inject CSV data in MySQL, Oracle, PostgreSQL databases. With this tool, you can easily inject a data from CSV file into any table of database by running a simple command. This is a pretty handy tool which you can use to quickly connect to a database and inject data which is validates automatically. Currently, this tool supports Oracle, MySQL, PostgreSQL, and DB2 like databases.
To make it work, you just need drivers for the target database in which you want to load CSV file. But for this post, I will demonstrate functionality of this tool for a MySQL database. This is a command line tool in Python, so before getting it, you will need Python installed on your PC.
There are some MySQL clients that you can use to do the same thing, but this is quite intelligent. Here you do not have to check data in CSV before loading that into a database. It automatically identifies the columns in the target table and input CSV and then loads data accordingly. Apart from this, there is an option to generate a staging table using the data from the CSV file. It generates SQL Create command from the CSV file and it uses VARCHAR as the default data type for the headers which you can modify.
How to Inject CSV Data in MySQL, Oracle, PostgreSQL Databases?
Using this tool on your PC is very simple. You just have to download this repository and then extract it somewhere you like. After that, open command prompt or terminal in that folder and install database drivers. For this post, I will be using MySQL database and to install drivers for that, you run the following command.
pip install mysql-connector-python
Now, you are ready to use this tool to insert data in table of MySQL database pretty easily. You have to construct the command whose syntax is given below. In this, specify the type of database you want to use, user, password, and table an DB name in which you want to add the CSV data. Do note that, your CSV file must have same columns as they’re in the database. However, their order can be different and also the headers in the CSV file can have spaces as it automatically find them before adding data in the database.
Syntax: python csv2db.py load -f PathToCSV --dbtype DbType -t TableName -u DB_User -p DB_Passwd -d Database
Example: python csv2db.py load -f test.csv --dbtype mysql -t test -u root -p "" -d gogs
After you run above command, you will either see the success status or an error. If the command finishes successfully then its good otherwise you will have to inspect the error. The error will mainly arise due to invalid column in CSV or there is a connectivity problem. You have to make sure that credentials you have used are correct and database is up and running.
In this way, you can use this handy command line tool to inject CSV data in a database. The above process was for MySQL but the approach is same for other database. For using other databases, you will have to install its driver via PiP and change database type in the command accordingly. For advanced usage and technical details, you can go to its GitHub page and go through the “Readme.md” file.
Closing thoughts
Csv2db is a handy tool for database programmers to quickly add values in database right from command line. You can use it on any platform in the same way. You just have to make sure that database drivers are installed and your database is up. And then you just execute a single command to load data in to the database.
