Hi I am trying to use to MySql Workbench Data Import facility to import a .csv file (thousands of records). However it stops importing the first 5 records.
After studying the .csv file I could see that on line 5 there was a double quote – see below;
1,90945601,123.123.123.123,Fujitsu,Esprimo Q900,YLBX097807192,,01/07/2011,408000085A,G.039,Weekend PT (BS),,,,
2,90456978905,123.123.123.123,Fujitsu,Esprimo Q900,YLBX009787188,,01/07/2011,408089005A,"G,.039",Joe (ert),,,,
3,90945610,123.123.123.123,Fujitsu,Esprimo Q900,YLBX007897202,,01/07/2011,408097805A,2.012,Jo,,,,
4,90945978616,,Apple ,Mac ZOPF00PJ ,DGKLM05BF8J9,,01/10/2013,408005A,1.029,Jim and ICRH,,,wif,
5,90456789917,,D4M ,FlexiBasic Kiosk ,,,01/08/2013,408098705A,1.029,,,,"19"" Saw Screen with Artwork",
6,94560918,143.117.198.197,3M,9100BC,91201338,,01/07/2013,408005A+J101,G.036,SD RF,,,Dual (91201567338) ,
I have tried to configure the settings during the import however it still doesn't work – see image;
It's not possible to change the 'Field Seperator' tp a comma, only three options are provided.
I manually removed line 5 from the csv and this time over 100 records were imported, but again, it stropped at the next double quotes ". The problem is the csv file contains thousands of items that have descriptions that include size in inches.
Are there any other changes I can make without having to manually remove all the double quotes from my .csv file?
I've also tried PhpMyAdmin, doesn't work there either.
Any suggestions appreciated.
Best Answer
You could try using sed and substitute | (the pipe character - not normally found in data dumps) for the " (double quote) character
sed -i does an inline replace - take a backup before messing with your original file. If you're on Windows - as you appear to be, you can get sed functionality from the answer here or use the editor of your choice - VIM for Windows for example or whatever you prefer.
Then when you've loaded the data, do a REPLACE() on your tables