Mysqlimport and double quotes
We have a large delimited text file (about 120,000 records, 50MB) that we are trying to drag and drop into MySQL using mysqlimport. Some fields are enclosed in double quotes, some are not. We are using a switch fields-optionally-enclosed-by='\"'
, but the problem is that some of the field values contain double quotes (indicating inches), so the delimited field value might be something like "ABCDEF19". Has the meaning?
We have no control over the source of the file, so we cannot change the formatting. I tried removing the radio button fields-optionally-enclosed-by
, but then the double quotes that surround the values are imported.
it writes with quotes in values that get seriously messed up. Is there a way that we can tell mysqlimport that some fields are not necessarily quoted, but can contain quotes? We were thinking maybe global search and replace to avoid double quotes in field values? Or any other suggestions?
a source to share
You can import it with quotes (removed fields - optional - nested - off) and then run validation where if the value has double quotes at the beginning and end (if none of the values have inches at the beginning) then truncate by 1 character at the beginning and end to remove the extra quotes you got from the import.
EDIT: After reading kekoav's answer, I have to agree that if you can manipulate the file before importing it would be a much smarter option, but if you are forced to remove the quotes afterwards, you can 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 each column in the table
If your data contains quotes inside the body of the quote field without any restrictions, you have a problem. You cannot guarantee that mysqlimport will do this correctly.
Massage the data first before trying to insert it this way.
Fortunately, it is a tab delimiter, so you can run a regex to replace the quotes with a delimited version and then tell mysqlimport the delimiter.
a source to share