CODEWITHSUNDEEP
mysqlsqlcsv
S. Patel
S. Patel

Reputation: 27

Load the csv file to MySQL with coma delimited and comma within field

I am able to upload a csv file to MySQL however not able differentiate comma delimited words when used internal to one term.

There are 5 fields separated by "," however in some rows, one field has internal "," which separates the two words falsely that supposed to be intact term "abc, def". Below is the example.

Good rows: field1,field2,field3,field4,field5

Rows with non-distinguish field3: field1,field2,"field3a, field3b",field4,field5

Code:

$upload = mysqli_query($cons, 
    'LOAD DATA LOCAL INFILE "file_name.csv" 
         INTO TABLE table_name 
         FIELDS TERMINATED by \'\,\' 
         LINES TERMINATED BY \'\n\' 
) or die(mysqli_error($cons));

Please note that it works fine for "Good rows" but for other type of rows, "field3b" is inserted as "field4" in MySQL table.

Upvotes: 1

Views: 1083

Answers (1)

GMB
GMB

Reputation: 222402

You are looking for the FIELDS OPTIONALLY ENCLOSED option.

$upload = mysqli_query($cons, 
    "LOAD DATA 
        LOCAL INFILE 'file_name.csv'
        INTO TABLE table_name 
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\n'"           
);

NB : I switched the php single quotes to double quotes, so less escaping is needed in the query.

Upvotes: 3

Related Questions