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.

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

1. Just tried it on my site. Works flawlessly. Thanks a bunch!

2. Hi paul,

Good to hear your successful try.
You can of-course go through other topics if you already haven’t.

3. how can i convert date from excel csv to date in mysql.
i have to put column in mysql as VARCHAR so then it can read from csv. but if i change to DATE it read as 00-00-0000.
any idea?

4. Hi mcheali!
I’m not sure in what format you have your date stored in csv.
If you have csv as:
#x.csv
1,2010-05-21
2,2010-05-22
3,2010-05-20

#table x:
CREATE TABLE x (
a int(10) NOT NULL,
b date default NULL
) ENGINE=MyISAM;

then a very simple load data query will fill the date values correctly.

#Query:
LOAD DATA INFILE “c:/x.txt” INTO TABLE x
FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n';

5. ….greetings….

i have bunch of csv file where some of the files has column name order differ from each other and also differ from the table in database..

how can i load the data into the correct field?
the table have about 50 columns and each csv file have about 20-30 columns only..

any suggestion?

6. Greetings zeera,
Well I believe if you have column order different from each other and table as well, it’s case #3 describled above. You have to find out proper field positions and then changing the order in load data command should work. It’s like you must specify a column list if the order of the fields in the input file differs from the order of the columns in the table.

Eg.
LOAD DATA INFILE ‘file.csv’ INTO TABLE tablename (col1,col3,col5,col2…);

About 50 vs 20/30 Columns, if you have nullable columns, things will be fine.

7. thanks Kedar… i know it will work..

how am i supposely know the table order?

and i won’t know what column is nullable for the file..

– i’m using php with mysql –

any idea to solve my solution? thanks in advance

8. sorry for the wrong word..

-what if the file is uploded by the user? how to know the column order?

9. Zeera,
– You should ask a formatted input, may be you can publish a template and reject user uploads which are not as per template.
– You can use ‘DESC tablename’, will show you column names in order. It will also show you data type information and whether the column is ‘allow-null’ or not.
– About parsing user inputs I believe instead of using “load data” you might need some data parsing script when your inputes are not consistent.

Hope this helps.

10. thanks…. but i’m developing new system since here they only use excel to store evrythng..
anyway thanks for the idea..

–> just wanna ask if u know how to autogenerate the excel file either to text or csv with the font color as one column after the next? mmm.. example is like below:

i want output in csv like this: (is it possible)

Zeera, blue, 22, blue, London, black

its mean Zeera and 22 are in blue color while London in black color..

i need to make comparison afterwords..

11. Hi,
I’m not much into dev but I can sense it’s possible. There are PHP classes to generate Excel-worksheets available. “Google”ing right keywords will give you the solution.

12. Thanks Kedar..

I managed to make it using perl.. just not yet captured the font color..

13. morning Kedar..

LOAD DATA INFILE ‘file.csv’ INTO TABLE tablename (col1,col3,col5,col2…);

i do it but it still not in order.. it does not follow the (col1,col3,col5,…..).. it just insert from col1,col2 until the end…

any suggestion?

See below example:
Table:
——
CREATE TABLE mytable (
a int(11) default NULL,
b varchar(5) default NULL,
c varchar(5) default NULL,
d varchar(5) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CSV:
—-
1,b,d,c
1,bb,dd,cc
1,bbb,ddd,ccc

Syntax:
——-
Load data infile ‘d:/test.csv’ into table mytable fields terminated by ‘,’ optionally enclosed by ‘”‘ lines terminated by ‘\r\n’ (a,b,d,c);

This loads data in proper order.

15. ok.. lets say i use ur example.. only the syntax i choose some column..

load data infile into table mytable fields terminated by ‘,’ (a,c,d);

–> it insert into a,b,c column instead

16. how can i insert into another table when compare data from 2 table?

example:
——-
table a table b
——- ——-
col1 | col2 | col3 | col4 col1 | col2 | col3 | col4
————————- ————————-
a | b | c | d a | b | e | d
w | x | y | z w | x | m | n

the compare table – the result after compare two table
——————————————————

col1 | col2 | col3 | col4
————————-
True | True | False| True
True | True | False| False

–can anyone help me here? (T_T)

17. how can i insert into another table when compare data from 2 table?

example:
——-
table a
——-
col1 | col2 | col3 | col4
————————-
a | b | e | d
w | x | y | z

table b
——-
col1 | col2 | col3 | col4
————————-
a | b | c | d
w | x | m | n

the compare table – the result after compare two table
——————————————————

col1 | col2 | col3 | col4
————————-
True | True | False| True
True | True | False| False

–can anyone help me here? (T_T)

18. Yes… I try to help whenever possible and available.

mysql> load data infile ‘d:/test.csv’ into table my2 fields terminated by ‘,’ (a
,c,d);
Query OK, 3 rows affected, 3 warnings (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 3

mysql> select * from my2;
+——+——+——+——+
| a | b | c | d |
+——+——+——+——+
| 1 | NULL | b | d |
| 1 | NULL | bb | dd |
| 1 | NULL | bbb | ddd |
+——+——+——+——+
3 rows in set (0.00 sec)

I’m not sure what you’re upto and what are you doing.

2. About ur true/false output you can join both tables:

select if(t1.col1=t2.col1,’true’,’false’) as Col1 from table1 t1,table2 t2 where t1.SMCOL=t2.SMCOL;

You may further mail me your exact requirements.

19. this is my query:

select
if(t1.StepName=t2.StepName,’true’,’false’) as StepName
from ProcessSpec t1,VerifiedData t2
where t1.StepName=t2.StepName;

it give all the output as true. if i write ‘false’,’true’
it will give all the output as false..

As you’re joining on StepName, you will get data where StepName from t1 and t2 will be same. So, IF condition is always going to fall in true part. So, when you write “false,true” it will show false and the reverse.

21. about my T/F table.. is it a problem as i don’t have a key for each tables? i’m not sure what id can i use since table a and b can be uploaded at different time

22. In my CSV file, I have an boolean column that is store like this : ‘false’ or ‘true’. How to tell MySQL to convert false = 0 and true = 1? Cause when importing the file, I get warning sucha as : Incorrect Integer value : ‘false’.

Thanks!

23. Hey Bich,
I believe case file will help here…
You should try something like

load data infile ‘path/to/file.csv’ into table TABLENAME fields terminated by ‘,’ (col1,col2,@TRUEFALSECOLVAR) set TRUEFALSECOL=case @TRUEFALSECOLVAR when ‘true’ then 0 when ‘false’ then ‘1’ end;

You may even try with if(@variable=’true’,0,1).

Hope this helps,
Thanks.

24. hi all..

is it possible to select only not same record?

table1
=====

col1 col2 col3
—————————-
1 2 3
1 2 3
1 2 4

expected output
===========
col1 col2 col3
———————————
1 2 4

i’ve try selfjoin query but it not give the result i want

select distinct p1.col1,p1.col2,p2.col3
from BOEdata as p1, BOEdata as p2
where p1.col1 = p2.col1 and p1.col2=p2.Scol2 and p2.col3 != p1.col3

it give this output
——————-
col1 col2 col3
—————————-
1 2 3
1 2 4

25. Zeera, Check this example:

create table z
(
a int,
b int,
c int
);

insert into z values (1,2,3), (1,2,3), (1,2,4);

select * from z group by a,b,c having count(*)=1;

26. kedar.. thanks alot.. its work..

27. by chances.. is count(*)=1 only read 1 line of record?

if a have table as below:

col1 col2 col3
———————
1 2 3
1 2 3
1 2 3
1 2 4
1 2 5
1 2 4

can it show? ->

col1 col2 col3
———————
1 2 4
1 2 5
1 2 4

if i use ur coding it will only return 1 record which is:

col1 col2 col3
———————
1 2 5

any suggestion? i’m dying here

28. sowie 4 the icon.. i don’t know how its there..

29. Zeera,

Please understand what group by is doing. count(*) is not limiting number of records to one, but its taking all records which are not duplicated in table.

Below query’s result will give you more understanding:

select *,count(*) as occurrences from z group by a,b,c;

30. Since load data infile is prohibited in a stored procedure, what would be the workaround to import a csv file into MySQL d/b?

31. You keep using words like ‘simple’ and ‘easy’, all the while providing the most convoluted explanation possible…

• Hey Chris,
Things will be Simple & Easy once we do it… those words you may consider as placeholders to reduce the complexity
Importantly, let me know how can I help you, if anything is unclear!
Cheers.

32. http://www.agilefuseconsulting.co.cc/csvtomysql/

This is the CSV to MySQL tool. The tool was developed through the use of html5 and file api. The tool works best on firefox and google chrome browser(latest version). Be that as it may,pls. be advised that importation of large data would require a substantial amount of time to complete, and may also cause memory limit error so use the tool with caution.

33. Is there any way to select particular form of data from csv file using regular expression in “load data infile” query and using SET we assign that value into column in which we want that data ???

34. hi,
i want to know how to save fuzzy number to my sql.
example :
Equally important = (1,1,3)
how to save three value into one field, equally important.

35. i got problem with INFILE user previlege, on a shared hosting.

36. I have stumbled upon an unexpected problem…

Is there a way of handling input data with missing values?

for example, what if a line had a missing value which should ideally be signified by a zero? Is there a way of making the 1st input below read: a 2 0

col-1,col-2,col-3

a,2,

Likewise, what about a zero value in the middle of the input line, like this,

col-1,col-2,col-3

a,,3

Thanks if you’re able to offer any ideas on this!!

• First case you can do:

LOAD DATA INFILE ‘c:/example.csv’ INTO TABLE example FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ set col3=if(col3=”,0,col3);

and same for the next.

Hope this works… I’m in bit hurry

37. Hi Kedar,

Nice work! I’m having a doubt regarding values from the file columns that have characters like ‘,’ (comma). how to handle these kind of columns while uploading?
E.g. If there is file Example.csv which has col1: item1 col2:”Some description like a,b,c”. We are uploading based on ‘,’ delimiter. So, how to ignore ‘,’ from the values from a given column?

Thanks,
Vaibhav.

38. Help! I cannot get the SET command to work for my upload. I have tried just about everything that I can think of and found via web research. Not sure what I am missing. Everything works perfectly when the SET command is not present.

load data local infile ‘/uploaded_files/aging_file.txt’ into table aging_pre_2007 fields terminated by ‘\t’ escaped by ” lines terminated by ‘\r\n’ (branch_number, customer_number, document_number, apply_to, order_number, document_date, due_date, order_type, line_amount, freight_tax) set aging_date = ‘2007-09-30′;

Table Details:

aging_number int(11) NOT NULL auto_increment,
aging_date date default NULL,
branch_number char(2) default NULL,
customer_number varchar(6) default NULL,
document_number varchar(6) default NULL,
apply_to varchar(6) default NULL,
order_number varchar(6) default NULL,
document_date date default NULL,
due_date date default NULL,
order_type varchar(6) default NULL,
line_amount double(15,2) default NULL,
freight_tax double(15,2) default NULL,
PRIMARY KEY (aging_number),
UNIQUE KEY aging_number (aging_number)

Tab delimited text file includes branch_number, customer_number, document_number, apply_to, order_number, document_date, due_date, order_type, line_amount and freight_tax. I need to add the aging date because it cannot be added to the text file, but I know what it is.

Thanks.

• James,

Your syntax appears proper to me and works perfectly when I tried! I hope you’ve your file at proper place with right permission for mysql to get hold of it.

In the mean time if you’re in too hurry to find & shoot the problem with LOAD DATA, I can suggest you a quick work-around:
1.
– alter your table -> aging_date date default ‘2007-09-30′
– run you syntax above without passing aging_date, it will be populated as default.

2. if you can’t alter, just fire an update query.

39. Hey Kedar,

But the thing is I am using Windows XP. Can you tell me what should i do to make this work in Windows? I am a new bee to my sql.

40. Hi Kedar,

First great appreciation for your nice work.

I am having CSV file which holding column like

col-1:”TC1″,col-2:”TC2″,col-3:”id1,id2″col-4:”TC4″

Note that col-3 has 2 values, i have to add each rows into table and repeat the other column values for col-3 each value

Isn’t any possible to do this?.

41. Hi Kedar,
I had a csv file containing 4 columns,
and my table has 3 columns.
is it possible to insert only 3 columns by ignoring any column from .csv file (say ignoring 4th column)?

• Hey aijaz,

I assume if you use simple load command, it will insert the data ignoring the extra column. You should get warning! This is the case when you want to ignore last (4th) column.

Now there is very tricky but easy solution for inserting last 3 values and ignoring 1st column
What you’ll do here is specify columns:
 load data infile ........ fields terminated by 'STRING' ... (col1,col1,col2,col3); 

 I'll update this in main post too! 

Cheers, Kedar.

• Kebman,

load data infile ‘FILEPATH/FILENAME’ IGNORE into tablename…

Make sure you have key column defined for the stuff to work.

42. hai kedar….

i had csv file where date is in dd:mm:yyyy format and time in hh:mm format can u plz suggest me a LOAD command to load the csv file to mysql table…..

• Sampath,

Sorry for the delayed responce due to some “tech” issues on d’ blog I couldn’t answer you on timely basis.
Btw surely you can use strtotime function to convert it into date-time value.

Cheers,
Kedar.

43. Hi,
You were a great help thanks alot, I found some things here which I couldnt find any where..
well
here is a query please i need to fix this ASAP

LOAD DATA INFILE ‘C://graphs data//chk.txt’ INTO TABLE movingaverages_ten FIELDS TERMINATED BY ‘,’LINES

TERMINATED BY ‘\n’
(Id,days,DateTime,stockId) SET ma = (SELECT SUM( Price ) FROM (Select Price from closingprice_ten WHERE

closingprice_ten.StockId = stockId and ‘DateTime’ > ‘closingprice_ten.DateTime’ ORDER BY

closingprice_ten.DateTime DESC limit days) ptscore);

so the error is like :
ERROR 1327 (42000): Undeclared variable: days

Can you help me with this! please

• Thanks sohaib,

I’m not sure what are you trying to do
“… DESC limit days” << HERE Is that by mistake or you need that value generated dynamicall? Hope I’m not too late in replying… Regards, Kedar.

• no its never too late,

Limit days ,
its like limit 20 or 50 or blabla..
yea its dynamic

See I guess now I have got the issue, but still with no clue.

(Id,days,DT,stockId) SET ma = (SELECT SUM( Price ) FROM (Select Price from closingprice_ten WHERE
DT > closingprice_ten.DateTime
Look here, I am now getting this error.
ERROR 1054 (42S22): Unknown column ‘DT’ in ‘where clause’

• Sohaib,

< > You must find a way to pass a value to variable days. Eg. if days is something you want to use from the file you’re loading => days value from the table => you can use
< <... order by movingaverages_ten.days>>

———————–

consider example
Table ld with columns (id, val, calc)
Generate value of calc from another table named “b” using value we passed in text.

We can do this as follows:

load data infile ‘/tmp/a.txt’ into table ld fields terminated by ‘,’ lines terminated by ‘\n’ (id, val) set calc=(select id from b where b.id=ld.id);

I’m not sure what are you trying to do!!

Understand that “(Id,days,DT,stockId)” these will be seen as columns by MySQL and hence you’ve got the error unknown column error.

If you’re not using MySQL 5.5 it’s not possible for you to use variable-limit clause.
Also you probably don’t need a variable-limit as you’re using aggregate function.

Try with this query:
Select sum(Price) from closingprice_ten WHERE
closingprice_ten.StockId = stockId and ‘DateTime’ > ‘closingprice_ten.DateTime’ ORDER BY closingprice_ten.DateTime DESC

If this doesn’t work, mail me your problem in detail at kedar@nitty-witty.com.

– Kedar.

• That’s what keep this going

Happy “MySQL”ing…

• That’s what keep this going

Happy “MySQL”ing… Andrea.

44. Dear KEDAR,
I’m not a programmer and I need to edit and write again
a csv file in PHP.
I need to remove the ‘,’ from the end of each line.
If you copy and save as CSV the following rows that’s the csv file.

I’ve tried to use substr ($xxxx[$c],0,-1);
But I don’t know how write back the modified file.

“Cod. Meccanografico”,”Tessera”,”Nuo./Rin.”,”Cognome”,”Nome”,”Sesso”,”Data Nascita”,”Luogo Nascita”,”Indirizzo”,”Cap”,”Città”,”Provincia”,”Data Rilascio”,”Cod. MC”,”Motoclub”,”Telefono”,”Cellulare”,”Email”,”Cittadinanza”,”Residenza”,”Dirigente”,
” 10102685″,”12110285″,”R”,”ABATI”,”GABRIELE”,”M”,”27081974″,”BERGAMO”,”VIA DELLA GRUMELLINA 41″,”24127″,”BERGAMO”,”BG”,”10012012″,”0179″,”BERGAMO “,,”3402865027″,,”I”,”S”,,
” 12910500″,”12910500″,”N”,”ACERBIS”,”DAVIDE”,”M”,”11042000″,”ALZANO LOMBARDO”,”VIA PICCINELLI, 8″,”24020″,”SCANZOROSCIATE”,”BG”,”23022012″,”0179″,”BERGAMO “,,”3381082603″,,”I”,”S”,,
” 03515015″,”12110160″,”R”,”ACERBIS”,”FRANCO”,”M”,”12091946″,”ALBINO”,”VIA PEZZOTTA 3″,”24021″,”ALBINO”,”BG”,”06122011″,”0179″,”BERGAMO “,”035773577″,,”valeria.a@acerbis.it”,”I”,”S”,”CS”,

Many thanks
Giovanni

• Giovanni,

well I’m no php expert too but google told me to:

$file = file_get_contents(‘filename.csv’); // replace the data$file = str_replace(‘folder|oldValue’, ‘folder|newValue’, $file); // write the file file_put_contents(‘filename.csv’,$file);

Instead of str_replace you may also use preg_replace to find your pattern.

Let me know if this doesn’t help.
Thanks.

• Hi Yogesh,

May be you can detect pattern in the csv and make the LOAD DATA to generate err and thus that line will be ignored!!
Well, I’m not much sure about what you’re trying so if you can provide a sample, we may find some way out…

Actually I took some different approach I need not ignore column now.
But i got some other issue.
Thing is that, records in my CSV file have some blank spaces.. I know i can use trim function in php and remove the white spaces at the beginning and end of a string… but now since my data is getting inserted in database table using load data infile…how to remove that blank space across records while insertion…. because load data infile will insert every record as it is without trimming anything…

Thanks.

• Sure Yogesh,

Check case #5.
You may find and replace spaces for specific columns – use the replace function.

Cheers.

46. 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

47. 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.

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

Aunque deberías poner el enclosed by
Muchas gracias!

49. 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!

50. 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.

51. 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?

52. 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;

53. 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?

54. 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.

55. 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.

56. I have a lot of data in txt file. I want to delete that data from mysql table.
can u suggest me how can i delete data with the help of txt file from one table?

57. I have an csv file having 3 col. and want to import only first and third col. into mysql database using load data infile…Mysql database having only two required col.

-- Kedar Vaijanapurkar --