mysqlimport and double-quotes

Question

We have a large tab-delimited text file (approximately 120,000 records, 50MB) that we're trying to shove into MySQL using mysqlimport. Some fields are enclosed in double-quotes, some not. We're using the fields-optionally-enclosed-by='\"' switch, but the problem is some of the field values themselves contain double-quotes (indicating inches) so the delimited field value might be something "ABCDEF19"". Make sense?

We have no control over the source of the file, so we can't change the formatting there. I tried removing the fields-optionally-enclosed-by switch, but then the double-quotes that surround the values are imported.

he records with quotes in the values are getting seriously messed up. Is there a way we can tell mysqlimport that some fields are optionally enclosed by quotes, but may still contain quotes? We've thought maybe a global search and replace to escape the double-quotes in field values? Or any other suggestions?

Answer 1

You could import it with the quotes (fields-optionally-enclosed-by switch removed) and then run a check where if the value has double quotes at the beginning and end (assuming none of the values have inches at the beginning) then truncate by 1 character from the beginning and end to remove the extra quotes you got from importing.

EDIT: after reading kekoav's response I have to agree that if you are able to manipulate the file before importing that would be a much wiser option, but if you are forced to remove quotes afterwards, you could use something like this:

UPDATE table 
SET column = 
IF(
STRCMP(LEFT(table.column,1),'"'),
MID(table.column,2,(LENGTH(table.column)-2)),
table.column
)

for every 'column' in 'table'

Answer 2

If your data is including quotes inside of the body of the field quote without delimiting that somehow, you have a problem. You can't guarantee that mysqlimport will do this properly.

Massage the data first before trying to insert it in this way.

Luckily, it is tab-delimited, so you can run a regex to replace the quotes with a delimited version and then tell mysqlimport the delimiter.