load data infile syntax in mysql

Question

I am using LOAD DATA local INFILE command to upload big csv file in mysql db. The field is an integer in the mysql table and so it will accept only integer.

Now if the csv file contains numbers like below then the sql doesn't work.The format of my csv file is

ID
"322"
"445"
"211"

So it is inserting 0 in the table. But if the csv file contains no double quotes then it's fine.

How can I tell "load data infile" command to ignore double quotes( as sometimes it is too difficult to remove double quotes from csv file which has got about 1 million records).My Sql is

$insert_query = "LOAD DATA local INFILE '".$target_path."'
                INTO table master_huts
                IGNORE 1 LINES
                (hids)
                ";

possible duplicate of Parse error for load data infile in mysql & php — user1864610
– user1864610, Commented Jun 24, 2014 at 4:23

spencer7593 · Accepted Answer · 2014-06-23 15:47:37Z

1

Try adding this:

FIELDS OPTIONALLY ENCLOSED BY '"'

before:

IGNORE 1 LINES

answered Jun 23, 2014 at 15:47

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3671491 Over a year ago

Thanks.Now getting php parsing error.If you see my sql it starts with "

user3671491 Over a year ago

Solved the parsing issue by using '\"' but now getting sql error now.

user3671491 Over a year ago

I am sooo stupid...How can I do such idiotic mistake.I am so sorry @spencer7593 and thank you so much for your help.Accepted the answer.

spencer7593 Over a year ago

My answer was focused on the SQL statement; getting that string specified as a string literal within PHP (or Java or whatever language), that introduces the need to "escape" characters within the string. You figured out you needed the backslash before the double quote...

Collectives™ on Stack Overflow

load data infile syntax in mysql

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related