4

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.

Improve this question
3
  • 1
    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.
    – Mike Lischke
    Jan 26, 2016 at 9:25
  • 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.
    – ibennetch
    Mar 18, 2016 at 1:23
  • @ibennetch The csv is fine. Quotes are escaped with another double quote (which is somewhat default, unix style is \" indeed).
    – Danny_ds
    Mar 8, 2018 at 18:23

1 Answer 1

Reset to default
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.