In this blog we will look at different ways to load CSV file into a database table, using MySQL and PostgreSQL databases.
Alternatively we will also explore a scripting approach to convert the CSV into a SQL Insert statements, so that it can be directly run on any database.
We have a CSV file with employee data and we want to load it into
employee database table
Suppose we have a sample CSV file like this
In order to load the csv to database, we first need to map which fields of CSV map to which columns of database. Something like this below
Now that we have done the field mapping also, lets see how we can run some tools/commands to load the CSV data into a database table
2.0 CSV Load in MySQL
Lets first see how to do it in Mysql.
2.1 Using MySQL Workbench
In first and easiest approach we will see how we can do it via MySQL Workbench
Open MySQL Workbench -> Login to the Database -> Go to Schema Browser -> Right click on table and Select
Table Data Import Wizard
Select the CSV file to import
Choose existing table and optionally you can choose to
Truncate the existing table data
Workbench will auto-detect CSV fields and Database columns, and you can then choose the field to column mapping.
Run the import
You will get message like below, once import completes successfully
Select data from the table to verify the imported data.
2.2 Using LOAD Data INFILE
The above option of using MySQL Workbench is great, but is mostly suited for local databases.
What if, you want to run this import on a server where you dont have MySQL Workbench.
Worry not !
MySQL provide a
LOAD DATA using which you can import the file from command line.
- You can run the
LOAD DATAfrom the client machine and load into the database server
- OR you can run the
LOAD DATAfrom the server machine itself.
If you do the former, you will need to specify
LOAD DATA LOCAL
Lets see a sample file to load CSV file into table
LOAD DATA LOCAL INFILE 'sample.csv' INTO TABLE `employee_data` FIELDS TERMINATED by ',' LINES TERMINATED by '\n' IGNORE 1 LINES (`name`, `city`, `state`, `joining_date`, `review_score`);
We specify the following in above sql command
- CSV File path –
- Destination Table –
INTO TABLE employee_data
- CSV Delimiter –
FIELDS TERMINATED by ','
- New line character in csv file –
LINES TERMINATED by '\n'
- Ignore Header rows –
IGNORE 1 LINES
- Provide table column mapping –
LOCAL data load is disabled in MySQL
You can check it by running command –
show variables like '%infile';
To support LOCAL data load, we need to do following
- Provide argument
--local-infile=1while running mysql command
local-infilevariable by running sql –
SET GLOBAL local_infile = 'ON';
- And then load the csv file
There will be scenarios, when you cannot enable
LOCALdata load OR you dont have access to MySQL Workbench.
We will look at another approach in last section to get around those problems and load data into MySQL.
3.0 CSV Load in PostgreSQL
Lets load the CSV file now into PostgreSQL DB
3.1 Using \COPY command
COPY command to load CSV into database table.
\COPY employee_data(name, city, state, joining_date, review_score) FROM '/sample.csv' with (format csv, header true)
\COPY– execute this command from
psqlto load datay
employee_data(name, city, state, joining_date, review_score)– specify the destination table and columns. Skip the
idcolumn as its data type is
SERIALand it will be auto-generated.
with (format csv, header true)– Specifies CSV file format with headers
\COPYcommand in one line only. Line break is the command termination for it
See below example
3.2 Using pgAdmin
We can do the same data load using pgAdmin also.
4.0 Generic Script to convert CSV into Insert SQL statements for database.
We have seen that both Mysql and PostgreSQL provide multiple ways to load the CSV file.
But what if, there are are some limitations or configurations that do not allow us to run data load using above commands?
Wouldn’t it be better if i could convert my CSV file to a standard SQL Insert statement that can be run directly on any RDBMS.
I have already developed a shell script for doing this. You can check it out at following Github repository – https://github.com/chatterjeesunit/tools-scripts/tree/master/load-csv-to-database
This is how the script works