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.

1.0 Requirement

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

And suppose we have a database table like this below (Create table script for Mysql / Postgresql)

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.

We already have a sample CSV file, and the employee table already exists in our MySQL database.

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 DATA from the client machine and load into the database server
  • OR you can run the LOAD DATA from 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 – INFILE 'sample.csv'
  • 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 – (name, city, state, joining_date, review_score);

By default 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=1 while running mysql command
  • Enable local-infile variable by running sql – SET GLOBAL local_infile = 'ON';
  • And then load the csv file

See below

There will be scenarios, when you cannot enable LOCAL data 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

We already have a sample CSV file, and the employee table already exists in our MySQL database.

3.1 Using \COPY command

PostgreSQL provides 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 psql to load datay
  • employee_data(name, city, state, joining_date, review_score) – specify the destination table and columns. Skip the id column as its data type is SERIAL and it will be auto-generated.
  • with (format csv, header true) – Specifies CSV file format with headers

Run above \COPY command 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