MySql Workbench Data Import .csv

Question

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.

enter image description here

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.

• Mike Lischke: We'd like to fix this problem in a future version of MySQL Workbench, so any additional info is welcome. Can you also include your target table definition here? And btw. upgrade to the latest Workbench version. You have an older one. That comma selection is already fixed.
• ibennetch: How was your CSV generated? It seems to me this is an incorrect file in that the literal double-quote should have been escaped (there are a couple of ways of doing that, \" is what I would expect (making that entry ,"19\"",). This suggests to me that your CSV creation/export program is flawed.
• Danny_ds: @ibennetch The csv is fine. Quotes are escaped with another double quote (which is somewhat default, unix style is \" indeed).

Answer 1

You could try using sed and substitute | (the pipe character - not normally found in data dumps) for the " (double quote) character

sed -i 's/"/|/g' My_File.csv

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