11 Jan

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

In this article I will explain you about all different scenarios and explain you how to get the data loaded directly from csv to MySQL database using single command.

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,…]

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

** Excel file can be easily exported as comma separated / delimited file (csv) by File-Save As option to load data.

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

#table structure: example

col-1 col-2 col-3

Considering our MySQL table having same column sequence we can issue:

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

This is a very common and simple scenario.

– Ofcourse, 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 datafile 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 ;

– 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

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);

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

#table structure: example

col-1 col-2 col-3 col-4

Consider, col-1 is auto-increment 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;

Passing null value will make col-1 to take 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 perticular task and set a value.

eg. SET col-x=rand();

4. Filling the extra date columns:

This is very similar to 3. Here, we are required col-4 to be filled with 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;

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;

It should automatically fill the current_timestamp values for us.

#table: example

col-1 col-2 col-3 col-4

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;

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

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

Consider you’ve got a csv with col1 and col2 data and the 3rd column data is availble in another table. You can load the referenced data using a sub-query as follows. You have to make sure you get single row in return may be by using distinct or limit clause.

LOAD DATA INFILE ‘path/to/example.csv’ INTO TABLE example FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES (col-1,col-2) SET col-3 = (SELECT field-name FROM linkedTable where keyfield=col-1);

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

We may need to do: REPAIR TABLE csv_foo;

Well, this is not a “good” way though.

Documentation says that MYSQL LOAD DATA will not be able to do it for us.

We have a separate option available for the same, mysqlimport.

You may want to refer: mysql-import

Conclusion: I hope we have covered common scenarios which shall mostly help; rest will always be answered here.

Go through user comments below, you may get some specific use cases for for typical load data or csv to mysql conversion questions and probable solutions too.

Also refer to  the post where I’ve attempted to create a simple excel speadsheet to generate the Load Data Infile MySQL Syntax.

## 96 thoughts on “Load Delimited Data – CSV to MySQL, Excel to MySQL Database”

1. Hey Kedar, i have tried asking for help at many places and was impressed that you reach out to so many people and answer their questions. Keep it up! My question, however is as follows:

i have table in mysql
(sku(varchar(50),ItemName(varchar(200)), ItemDesc(varchar(500)), upc(varchar(20))).

I also have a csv file, sample record:

sku, itemname,itemdescription,upc
DD-33-XY, Coke diet, “enjoy taste of coke with 0 sugar”, 322234343
CC-32-UU, Fanta, “fresh orange sparkling drink”, 523243243
and so on. I a trying to import this csv file into database through C#. My question is that is my query written correctly:

LOAD DATA INFILE test.csv INTO TABLE testimport FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ IGNORE 1 LINES (sku,ItemName,ItemDescription,upc)

• Vishaal, that was good way to start a question 🙂

So, the easiest way to find it out is run it!!! Does mysql execute the statement – without error? Does your data get populated correctly?

At glace it looks like you need “optionally enclosed” and also full file path.

I did try to create a syntax generator earlier, see if that helps you.

Cheers,
Kedar.

2. Every time i run “Load data infile…”should replace table data with uploaded file data. How can this be achieved??

• Darsini,
You can use REPLACE keyword to replace the duplicate rows (meaning, duplicate PK/UK value). If you donot have PK/UK then you might want to backup &/ truncate your table!

Thanks.

3. Can we load a csv file(just one or two column with some rows) into a part of table by selecting specific rows and columns?

4. Kindly help me debug my code. I want to read data from a csv file and store in a temporary table. Then I use the data in temp table to update my main table after 10 minutes. The reason why I want this is because the CSV file runs in a different server and it keeps on changing. here is my code. The error I get is ERROR 1314 (0A000): LOAD DATA is not allowed in stored procedures
Here is my code

DELIMITER $$CREATE EVENT ev_update_from_London_Stock_exchange_csv ON SCHEDULE EVERY 10 MINUTE DO BEGIN LOAD DATA LOCAL INFILE ‘https://www.quandl.com/api/v3/… INTO TABLE temptable (Date,Open,High,Low,Close,Volume,Ex_Dividend,Split_Ratio,Adj_open,Adj_High,Adj_Low,Adj_Close,Adj_Volume) FIELDS TERMINETED BY ‘,’ LINES TERMINETRD BY ‘\n’ IGNORE 1 LINES; INSERT INTO LondonStockExchange (Date,Open,High,Low,Close,Volume,Ex_Dividend,Split_Ratio,Adj_open,Adj_High,Adj_Low,Adj_Close,Adj_Volume) SELECT (Date,Open,High,Low,Close,Volume,Ex_Dividend,Split_Ratio,Adj_open,Adj_High,Adj_Low,Adj_Close,Adj_Volume) From temptable TRUNCATE temptable END$$

DELIMITER;

5. whether can choose path by using load data local infile in vb.net

LOAD DATA LOCAL INFILE ‘c:/3.csv’ INTO TABLE student.meter FIELDS TERMINATED BY ‘\t’ LINES TERMINATED BY ‘\n’;”

instead of file name, can we get it textbox?

6. I am trying to load data from csv with the set inner query for second column…

LOAD DATA LOCAL INFILE ‘/home/imagetec/public_html/uploads_csv/Best Sellers JAN 2015.csv’ INTO TABLE Best_Sellers_JAN_2015_csv_03_02_2015_22_37_45 FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ IGNORE 1 LINES (sku,avail_sku) SET avail_sku=(select distinct(ld.sku) from load_system_cost_detail ld where ld.sku=sku)

giving error like —

Subquery returns more than 1 rowcheck load file error

Is there any way i can do this??

• So as the error suggests “(select distinct(ld.sku) from load_system_cost_detail ld where ld.sku=sku)” This query is returning >1 row while we only need single value for avail_sku!!! Check, correct & let me know. 🙂
Cheers.

7. Hi Kedar,

I’m trying to load a csv that has a column with file paths. Some of this use the unix format with forward slash and some of them use windows with a backslash. When I run the load data local infile, all the backslashes are removed making the path incorrect! I know you could use the backslash keyword to ask to escape but I’m not sure of the syntax and also if this keyword works for the contents of the column?

id filename filepath
123 file1 relative\path\to\file1
456 file2 relative\path\to\file2
789 file3 relative/path/to/file3

Thanks!

• Hey Raj,

I know it’s difficult to cover these many cases in my post but thanks for raising them.
This is again the “sort-of-case” #5, using replace function with special-appearance-of ESCAPED BY “”
[This way we’re specifying that we’re not using “\” to escape anything]

LOAD DATA INFILE ‘/tmp/file’ INTO TABLE FIELDS TERMINATED BY ” ” ESCAPED BY “” LINES TERMINATED BY ‘\n’ IGNORE 1 LINES set =REPLACE(,”\\”,”/”);

cheers!

8. No sabes cuanto te agradezco!!
Muy buena información!!!

Aunque deberías poner el enclosed by
Muchas gracias!

9. Hi Kedar,
I would need your help. it’s very urgent.
i have an csv file which have data to be inserted into multiple lookup tables in mysqlworkbench.
Suppose i have office name which needs to go to office table and department name which needs to go to dept table. but i need to load data when the my sql table does not have that record.

i would greatly appreciate ur help.
Thanks,

• Hello D,

If it’s not late for your urgency, it’d be quicker to send sample data and output requirement.
eg. “w,x,y,z data required to be loaded in tables T1 and T2 in such a way that …”
About ignoring duplicates we have “IGNORE” option but that requires unique key defined.

Let me know,
Kedar.

10. Thanx Sir,

But trim function worked for me. For one record it didn’t worked.. I saw the original xls file(from which i got csv) and there was hyperlink for that record… I’m not sure but that might be the reason…

I guess replace will remove all spaces.. I mean
San Francisco will be SanFrancisco i guess ….which i don’t want.

I just wanted to remove white space from beginning and end of the record..
After the data gets loaded in mysql table I wrote separate query..

UPDATE sample SET COUNTRY = TRIM(COUNTRY);

where sample is my table and COUNTRY is the column whoz records I wanted to trim…

Executing this removed all white spaces from beginning and end.

Thanx for your quick and kind help Sir….

Regards

This site uses Akismet to reduce spam. Learn how your comment data is processed.

-- Kedar Vaijanapurkar --