soooo close....

kdalts

GENTS... any help would be greatly appreciated... I've been seeking a solution for ages and I am so close... but there is 1 problem.

This is my code...

mysql_query('LOAD DATA LOCAL INFILE \'' . $CSVFile_calls . '\' INTO TABLE mkp_calls FIELDS TERMINATED BY "," LINES TERMINATED BY "\r\n";') or die($failed . my_error());

basically, it puts a csv file into mysql... all records are successfully imported and I thought I was done. Now, I discover that any record with a comma in the csv file gets screwed up... I'm guessing because the import thinks it is the end of a record.

So, does anyone know how I can strip out unwanted comma's before I do my import please...

Many thanks

NogDog

Assuming it is a valid CSV file where fields that contain commas are quoted, after the "fields terminated by" clause add:

FIELDS ENCLOSED BY '"'

kdalts

@ (great name btw🙂 )

I'm not sure what you mean by

Assuming it is a valid CSV file where fields that contain commas are quoted

This CSV file is a simple 'saveas csv' from excel.

The weird thing is that, if I do the import manually using PhP Admin it's fine - all records accounded for correctly. I know the records are comma delimited.

Cheers

NogDog

Then it should be a valid CSV file (there is more to the CSV format than just the commas), so just insert the statement I suggested above.

mysql_query('LOAD DATA LOCAL INFILE \'' . $CSVFile_calls . '\' INTO TABLE mkp_calls FIELDS TERMINATED BY "," FIELDS ENCLOSED BY \'"\' LINES TERMINATED BY "\\r\\n";') or die($failed . my_error());

kdalts

I used the code you suggested, now it says it can't find the file!!! How finding the file is related to adding the bit of code you suggested is a mystry to me - any ideas? :eek: 😕

BTW: It finds the file without the added code

kdalts

and it works fine manually....

Fields terminated by = ,
Fields enclosed by = "
Fields escaped by = \
Lines terminated by = auto

taken from the myPhP Admin import screen

NogDog

Don't know why that should cause the file problem, other than some silly typo either on my part or yours. Try assigning the entire SQL string to a variable first, then assign that variable as the argument to mysql_query(). If it fails, then output that variable or log it to your error file so you can see exactly what was sent to MySQL.

kdalts

@🙂 🙂 🙂 🙂

Thanks for your help... I worked it out... added code was correct but not the word FIELD

Instead of

FIELDS ENCLOSED BY \'"\'

it was simply

ENCLOSED BY \'"\'

Once again thanks for your help🙂