Load Delimited Data into MySQL Server: mysql

Loading csv or delimited data files to MySQL database is a very common task frequently questioned about and almost every time LOAD DATA INFILE comes to the rescue.

Here we will try to understand some of the very common scenarios for loading data into a MySQL Database.

The Load Data Syntax:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

Select all Open in new window

Consider we have to load file with following contents:
#File-name: example.csv

col-1,col-2,col-3
a,2,3
b,4,5
c,6,7

1:
2:
3:
4:

Select all Open in new window

1. A simple comma-separated file with column header:

#table structure: example 
col-1	col-2	col-3

1:
2:

Select all Open in new window

Considering our MySQL table having the same column sequence as the csv file above, we can issue the following SQL statement:

LOAD DATA INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES ;

1:

Select all Open in new window

This is a very common and simple scenario.

Quick Notes:

Of course, if we don't have column headers (col-1,col-2,col-3) in example.csv, IGNORE 1 LINES is not required.

Note the file path. Here you should make sure your slashes are proper.
You may give path as: C:\\path\\file.csv or C:/path/file.csv.

If we have a data file to be loaded stored on client ( Not on server ), we will add LOCAL keyword as given in Syntax.

So, the command will become:

LOAD DATA LOCAL INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES ;

1:

Select all Open in new window

- If we want to replace existing data by data being loaded from file, we will add REPLACE keyword before INTO TABLE.
Similarly if we want input rows that duplicate an existing row on a unique key value to be skipped, we will use IGNORE keyword before INTO TABLE.

2. Column sequence in file and table are different.

#table structure: example 
col-2	col-1	col-3

1:
2:

Select all Open in new window

In this case we need to specify column-name sequence of csv file in order to get data loaded in to proper columns.

LOAD DATA INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (col-1,col-2,col-3);

1:

Select all Open in new window

3. csv / load data file have lesser number of columns than targeted table

#table structure: example 
col-1	col-2	col-3	col-4

1:
2:

Select all Open in new window

Consider, col-1 is an auto-increment column and not provided in csv.

LOAD DATA INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (col-2,col-3,col-4) set col-1=null;

1:

Select all Open in new window

Passing null value will cause col-1 to utilize an auto-increment value.
Using SET you can assign values to those columns which were not available in csv and are not-null.
You may also use a function for doing some particular task and set a value.
e.g.,. SET col-x=rand();

4. Filling the extra date columns:

This is very similar to 3. Here, we require col-4 to be filled with the present timestamp value: a very simple way to do is altering table. :)

ALTER TABLE example CHANGE COLUMN col-4 col-4 TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

1:

Select all Open in new window

And then:

LOAD DATA INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (col-1,col-2,col-2=3) set col-4=null;

1:

Select all Open in new window

It should automatically fill the current_timestamp values for us.

5. Loading data with calculated columns:

#table: example 
col-1	col-2	col-3	col-4

1:
2:

Select all Open in new window

LOAD DATA INFILE 'path/to/example.csv' INTO TABLE example FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES (col-1,col-2,col-3, @var1)
  SET col-4 = @var1/100;

1:
2:

Select all Open in new window

Similarly we can alter a string variable as well by altering the variable as follows:

SET col-4 = replace(@var1,"find","replace")

1:

Select all Open in new window

6. Other ways of loading separated files to MySQL:

CREATE TABLE csv_foo LIKE foo;

ALTER TABLE csv_foo MODIFY COLUMN id INT(10) UNSIGNED NOT NULL;
// remove auto increment

ALTER TABLE csv_foo DROP PRIMARY KEY;
// drop key as no keys are supported in csv storage engine

Alternatively you may do:
CREATE TABLE csv_foo AS SELECT * FROM FOO LIMIT 0;
// Ignores key definitions and auto-increment
// Make sure you don't have any nullable columns.

Now,
STOP MYSQL SERVER

under data directory replace csv_foo.csv file by available data-file.csv. (Rename it to csv_foo.csv)

START MYSQL SERVER

you may need to do: REPAIR TABLE csv_foo;

You're done.

Well, this is not a "good" way though.

7. Loading multiple files:

Documentation says that MYSQL LOAD DATA will not be able to do it for us.
We have a separate option available for the same.
Refer: mysqlimport

Conclusion:
I hope we have covered common scenarios which shall mostly help; rest will always be answered on EE or here.
Finally, If you want to load data to MySQL Server, LOAD DATA

Load Delimited Data into MySQL Server

mysql

MySQL Server

About The Author

More Articles From theGhost_k8

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top MySQL Server Experts