How can we open CSV files that are using single quotation as string delimiter in Excel?

Question

We are working with a set of data that contains all kind of characters (comma, semi-colon, double quotation, tab, etc.). To handle this situation, we are using single quotation to surround the strings and we are able work with our data using data science libraries like pandas or to open it using tools like LibreOffice without any problem.

Here is some sample data (separated using tabs, so it's easier to see what the data looks like):

fname   lname   address             age  data           code    date
----------------------------------------------------------------------------
john    doe    'Province1, City1'   22   '11,"dx",""'   380    '2022-Jan-24'
jane    doe    'Province2, City2'   28   '"","dy","98"' 33     '2022-Feb-11'

And here is the csv file:

fname,lname,address,age,data,code,date
john,doe,'Province1, City1',22,'11,"dx",""',380,'2022-Jan-24'
jane,doe,'Province2, City2',28,'"","dy","98"',33,'2022-Feb-11'

However, if we open the file using Excel, this is how it looks like:

It seems that Excel is using double quotation as its default string delimiter. Is there a way to change it to single quotation? Similar to what we can do in LibreOffice Calc?

Answer 1

You can do this in Excel, too, but not by directly opening the CSV file in Excel. Instead, start with an empty Excel sheet, and use Ribbon Data -> Get external Data -> From Text. In the following window, select your CSV file and hit Import.

Now, Excel's Text Import Wizard should appear. In the second step, you're able to choose your field delimiter and string delimiter. With ' as string delimiter, your import should work as expected.

See also:

• MS Support: Import or export text (.txt or .csv) files
• Engineerexcel: How to Use the Excel Text Import Wizard

Answer 2

Yes, you can do this in Excel. Change the filename from .csv to .txt. Open the file from Excel.

The text import wizard will now open, and on the second step it will allow you to change the text delimiter to ' (and also the field delimiter to a comma, if you happen to be in a locale where this is not the default).