24

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 separator 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?

bad_coder
  • 649
Sandra
  • 731

4 Answers4

39

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
  • 731
5

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

Community
  • 1
Raystafarian
  • 21,963
  • 12
  • 64
  • 91
0

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)
MarianD
  • 2,726
Nicole Kappelhof
  • 1
0

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
  • 103