I'm trying to import this CSV file into MySQL that appears to be optionally enclosed by more than one character. Unfortunately, MySQL only supports one character as a field separator.
I am stuck doing this entire process in SQL as it is part of a larger program- so scripting is out of the question.
Example of the data:
reportdata, commission, total, chargeback, company
",123,""$116.00 "",""$604.00 "",""($88.00)"", foo
I'm stumped apart from attempting to do a bunch of string operations on a temp table.
Any ideas?
Best Answer
Since you are not allowed to script but SQL try the following:
There you go, another text file that has the doubled double quotes stripped.
If you want all doubled double quotes replaced with single quotes, do this UPDATE:
Now, go import that file (improved_importfile.txt) and have fun with it.
Give it a Try !!!