I'm using mysqli in a PHP file to load a CSV file into a table. I have the following SQL statement:
$query = 'LOAD DATA LOCAL INFILE "pull-everything2.csv" INTO TABLE temp_listing2 FIELDS TERMINATED BY "," ENCLOSED BY "\"" LINES TERMINATED BY "
" IGNORE 1 LINES';
The issue is that some lines of the CSV file are not being inserted correctly. More specifically, some data fields are being inserted into the wrong columns in the table. This is an excerpt of one of the lines not being inserted correctly:
,863-299-9523,"MFR.MLXCHANGE.COM,",863-,
So, some data fields will have commas inside them. Not all data fields are enclosed with a parentheses and ones that are not enclosed with a parentheses may have special characters like \, @, -.
I have tried the answers in this SO question, load data infile, dealing with fields with comma, but they did not resolve the above issues with my insert query.