22

I try to import a CSV file in Excel, using ; as delimiters, but some columns contains ; and/or quotes.

My problem is : I can use double quotes to ignore the delimiters for a specific string, but if there is a double quote inside the string, it ignores delimiters until the first double quote, but not after. I don't know if it's clear, it's not that easy to explain.

I will try to explain with a example :

Suppose I have this string this is a;test : I use double quotes around the string, to ignore the delimiter => It works.

Now if this string contains delimiters AND double quotes : my trick doesn't work anymore. For example if I have the string this; is" a;test : My added double quotes around the string ignore delimiters for the first part (the delimiter in the part this; is is correctly ignored, but since there is a double quote after, Excel doesn't ignore the next delimiter in the a;test part.

I tried my best to be as clear as possible, I hope you'll understand what is the problem.

Ken
  • 4,367
  • 4
  • 28
  • 41
Whin3
  • 675
  • 2
  • 9
  • 27
  • You might replace all double quotes with a character like | = Chr(124) and reverse the replacement after successful import. – Variatus Apr 07 '17 at 09:07
  • If I understand correctly, I should replace all `"` with `CHAR(124)` ? So Excel replaces all quotes with `|`, but how can I reverse it after in Excel ? – Whin3 Apr 07 '17 at 09:18

2 Answers2

46

When reading in a quoted string in a csv file, Excel will interpret all pairs of double-quotes ("") with single double-quotes(").

so "this; is"" a;test" will be converted to one cell containing this; is" a;test

So replace all double-quotes in your strings with pairs of double quotes.

Excel will reverse this process when exporting as CSV.

Here is some CSV

a,b,c,d,e
"""test1""",""",te"st2,"test,3",test"4,test5

And this is how it looks after importing into Excel:

enter image description here

Ken
  • 4,367
  • 4
  • 28
  • 41
-1

Import your Excel file in openOffice and export as CSV (column escaped with " unlike Excel csv, utf8, comma against ";").

Phil D.
  • 21