Excel does not honor the delimiter setting for reading or writing CSV files

When saving/opening CSV files in Excel, the default separator is a semicolon (;), as I need it to be a comma (,) I tried to change it by changing the Language and Regional settings following several other posts about this issue (i.e. How to get Excel to interpret the comma as a default delimiter in CSV files?).

However, after changing the List separtor in those settings, my Excel keeps saving CSV files with a semicolon. Is it because Excel was installed while my list separator was set as a semicolon? or is there another setting I am missing here?

Thank you in advance.

Sandra

Posted 2014-10-28T14:37:59.380

Reputation: 671

Is the problem in reading existing CSV files or creating one using comma as the delimiter? – fixer1234 – 2014-10-28T15:33:55.337

Both, I have already done what @Raystafarian mentioned before saving a new file. My regional setting is correct. Then I open Excel and save as CSV and when I open the file in notepad I can see that it used a semicolon. If I open in Excel it does load correctly because Excel still uses a ; for separating the columns. – Sandra – 2014-10-28T17:27:07.147

Answers

I found the problem. My decimal symbol in the Regional settings was also a comma (European) so, even when my List separator was a comma, the CSV was saved with semicolons. Now I changed my decimal symbol to a point and now the CSV file is created correctly with commas as separators. I tested this twice and now know that there must be an exception: if the decimal symbol is a comma, then the list separator will be a semicolon even is set otherwise.

Sandra

Posted 2014-10-28T14:37:59.380

Reputation: 671

13Thank you for taking the time to update the post with an answer. – Gary's Student – 2014-10-28T18:14:03.840

For saving

You need to adjust the list separator in language and regional settings prior to saving the file -

enter image description here

For opening

Go to Data - Get External Data - From Text

enter image description here

select the file

Select delimited, press next enter image description here

and select the delimiter comma

enter image description here

Click finish

enter image description here

Or, pull it in and then use text to columns see here

Raystafarian

Posted 2014-10-28T14:37:59.380

Reputation: 20 384

If I read the question right, it looks like the problem is creating (saving), a delimited file using commas for the delimiter (2nd paragraph). – fixer1234 – 2014-10-28T15:29:40.390

@fixer1234 yeah that's how I read it at first. But then I thought the first paragraph was the important one. I've no idea which is correct. Either way, I have no solution for the creation other than what the OP mentioned they've already tried. – Raystafarian – 2014-10-28T15:30:37.083

Yes, I did change the regional settings before saving the file. It didn't help. When I receive CSV files from other people which are delimited with a comma, then I use your method for importing the data correctly, then I save and it goes to a semicolon. It's annoying because every time I need to go to notepad and replace all semicolons with commas before using my CSV as an import file. – Sandra – 2014-10-28T17:27:28.370

This is a bit old, but I ran into the same problem:
Had a CSV delimited with semicolon (;). System separator settings (Windows) also set to semicolon.

If I opened the CSV manually, the file was separated correctly.
If I let VBA open it, it separated it incorrectly with a comma.

Using Delimiter:=";" apparently only works with .txt.

HOWEVER, using Local :=True solved the problem for me:

Workbooks.Open(Filename:=fullpath, Local:=True)

Nicole Kappelhof

Posted 2014-10-28T14:37:59.380

Reputation: 1

Here is a very nice macro that works quite well:

Sub saveCSV()
File = Application.GetSaveAsFilename(InitialFileName:="file.csv", FileFilter:="CSV (*.csv), *.csv")
ActiveWorkbook.SaveAs Filename:=File, FileFormat:=xlCSV, CreateBackup:=False, local:=False
End Sub

yo_haha

Posted 2014-10-28T14:37:59.380

Reputation: 103