problem importing complex text file into mySQL

sneakyimp

ok...i realize this is not a mysql forum but i was hoping for some guidance.

i havea large text file full of articles. my friend has exported it from M$ Access (which is probably my problem) as a comma-separated file.

Some of the fields are large text fields and contain not only line breaks (\r\n) but also curly double quotes (ASCII 147 and 148). To further confuse things, some of the lines within a data field end with a quote mark.

like this:

'Occupational therapist assistants are regulated in most States and must pass a national certification examination after they graduate. Those who pass the test are awarded the title “Certified Occupational Therapist Assistant.”

Occupational therapist aides usually receive most of their training on the job. Qualified applicants must have a high school diploma, strong interpersonal skills, and a desire to help people in need. Applicants may increase their chances of getting a job by volunteering their services, thus displaying initiative and aptitude to the employer.

Assistants and aides must be responsible, patient, and willing to take directions and work as part of a team. Furthermore, they should be caring and want to help people who are not able to help themselves.

At any rate, i have recieved this file both in XLS format and in CSV format and neither one will successfully import by using this sql:

LOAD DATA INFILE '/home/virtual/site1/fst/var/www/html/test/data_import/data_files/careers/OOH_SRC.csv' REPLACE INTO TABLE `_ooh_src` 
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'

the result says it has imported 556 rows and there are only 278 rows in the excel spreadsheet (one header row and 277 records). Additionally, fields like the one above are truncated after

..."Certified Occupational Therapist Assistant.

it's worth noting that the quotes around 'Certified...Assistant' are the aforementioned curly quotes (ascii 147 and 148).

Anyways, I'm wondering if there's some bug stuff going on with the LOAD DATA INFILE command or what. any advice would be much appreciated.

liquorvicar

if have double quotes within some of the text fields and you have an XLS version, why don't you try doing a search and replace in Excel (" -> \") before you export it to csv and see if that works.

sneakyimp

the problem appears to be more complicated than that. if i do the replace you suggest, i also need to open the resulting CSV file and replace \"" with \".

Additionally, I have noticed that the find and replace in excel totally ignores the curly quotes when i do the find and replace.

What's more, apparently the LOAD DATA INFILE function does recognize the curly quotes and somehow confuses them with an 'end record' indicator under certain circumstances-- such as the situation i described in my post.

annoying...can't really pin down where the problem lies.

my solution is to write my own import script. the php function fgetcsv seems to handle each line properly. i'm having better luck with that. AND i can replace all those curly quotes and line returns with their html equivalents as i import. since htmlspecialchars() doesn't deal with curly quotes, i have had to write my own function to do so which is, unfortunately, computationally intensive (scan every character of the article several times over for each char to be replaced).

ANYWAYS...it would be nice if the LOAD INFILE routine were a bit smarter.