Escaping quotes and delimiters in CSV files with Excel

excel csv delimiter double-quotes
Whin3 picture Whin3 · Apr 7, 2017 · Viewed 37.6k times · Source

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.

Answer

Ken picture Ken · Apr 7, 2017

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

Related questions

CSV End of line

Hi i am trying to put end of line code in CSV format to import it to excel I tried putting \n, "\n", \r, "\r", \r\n, "\r\n" but nothing worked for me I am trying to import it …

Read More
Excel to CSV with UTF8 encoding

I have an Excel file that has some Spanish characters (tildes, etc.) that I need to convert to a CSV file to use as an import file. However, when I do Save As CSV it mangles the "special" Spanish characters …

Read More
Writing to an Excel spreadsheet

I am new to Python. I need to write some data from my program to a spreadsheet. I've searched online and there seem to be many packages available (xlwt, XlsXcessive, openpyxl). Others suggest to write to a .csv file (never …

Read More