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

spencer7593 spencer7593 · Accepted Answer · 2014-06-23T15:47:37

1

votes

Try adding this:

FIELDS OPTIONALLY ENCLOSED BY '"'

before:

IGNORE 1 LINES

load data infile syntax in mysql

1 Answers