Save as CSV with semicolon separator

Question

I'm currently use this function for saving, but I have a problem with it:

Private Sub spara()
    ActiveWorkbook.SaveAs Filename:="T:\filepath+ ActiveWorkbook.Name", _
    FileFormat:=xlCSV, CreateBackup:=False
End Sub

It automatically saves with , but I need it to save with ; in its file. Is it possible to change how it saves somehow?

I've tried googling around for this issue, but all macros regarding .csv file saving are just how to save in .csv and how to split multiple sheets to .csv .

Not easy I'm afraid. A csv is defined to have commas. And csv parsing in full generality is difficult as you have to handle quoted strings containing commas correctly. What I would do if I were you is post-process the csv yourself. In the simplest case you might get away with converting every comma in your output file with a semicolon. In more complex cases (as I've alluded to) you could use a regular expression. Or build your own saver in vba. — Bathsheba, Oct 09 '13 at 07:31
As it name stands CVS is Comma Seperatd Values. You could use a script to open the .csv file then replace ',' with ';' and save. — Thanushan, Oct 09 '13 at 07:35
@Bathsheba Unfortunately, this is not true. Localized Versions of Excel use the ";" as Seperator. The german Version of Excel does this, I doesn't know which others do this. Also, csv can also stand for "character seperated values" - which makes csv a horribly broken format. — Christian Sauer, Oct 09 '13 at 07:38
@Christian Sauer It's helpful to know that. Do you think saknar namn could somehow trick his Excel into thinking it's locale is, for example, German? — Bathsheba, Oct 09 '13 at 07:42
@Bathsheba: No, I think not. I searched for a easy way to do just that, because the localization of Functions is annoying, but I found no easy switch to do it. — Christian Sauer, Oct 09 '13 at 07:47
@pnuts Yes, but that was not my primary intention - in my version, every function is localized, e.g. "wenn" statt "if" - that is annoying as helll and almost impossible to change. — Christian Sauer, Oct 11 '13 at 06:52

score 17 · Accepted Answer · answered Oct 09 '13 at 07:40

17

Which language does your Excel use? If your native language uses ";" as default, you can pass the parameter "local:=True"

ActiveWorkbook.SaveAs Filename:="C:\Temp\Fredi.csv", FileFormat:=xlCSV, CreateBackup:=False, local:=True

If not, your only choice is searching and replacing afterwards...

answered Oct 09 '13 at 07:40

Christian Sauer

10,351
10
53
85

Adding `local:=True` seems to not working on my machine (still uses `,` as separator). – Petr Abdulin Oct 09 '13 at 08:33
it actually works here in sweden, so i thank you dearly for that. local:=True was the way to go – saknar namn Oct 09 '13 at 14:04
`Save` doesn't work with `Local`, but `SaveAs` does, even without specifying a `Filename` (after saving via a save dialog or so). – root Jul 15 '23 at 18:07

score 3 · Answer 2 · answered Mar 16 '15 at 13:44

I had been looking this up to help resolve a similar issue I was having, I had an excel sheet that I export to a csv file, this is then uloaded elsewhere but requires the use of semicolons rather than commas for the character seperation, this worked fine when i was manually exporting the file as i had already changed the following Control Panel >> Region and Language >> additonal settings >> List separator from comma to semicolon. But when i tried to automate via VBA it defaulted back to comma, to fix I added the local paprameter as suggested by Christian Sauer above which then picks up the fact that i have changed my regional settings.

ActiveWorkbook.SaveAs Filename:="Filename.txt", FileFormat:=xlCSV, CreateBackup:=False, Local:=True

Thanks to Christian for the pointer.

score 2 · Answer 3 · answered Aug 14 '15 at 18:41

2

I solved it adding "SaveChanges:=False" when closing workbook

With ActiveWorkbook
     .SaveAs Filename:="T:\filepath+ ActiveWorkbook.Name", FileFormat:=xlCSV, Local:=True
     .Close SaveChanges:=False
End With

answered Aug 14 '15 at 18:41

Lionel T.

1,194
1
13
17

score 1 · Answer 4 · answered Oct 09 '13 at 08:44

It's quite possible this problem is not solvable using Excel VBA only. The problem is, while Excel Save As... uses machine locale define list separator value, Excel VBA always uses en-US locale, thus, it always uses , as a list separator.

I would recommend saving a CSV and then use custom console app/script for postprocessing. There is plenty of CSV parsers available which can read a ,-csv and then save it as ;-csv.

score 1 · Answer 5 · answered Oct 09 '13 at 10:16

1

Change Excel Options (Advanced, Editing options) to set Decimal separator to , (obviously (!))

answered Oct 09 '13 at 10:16

pnuts

58,317
11
87
139

1

I did not know that. Now I do. – Jan 30 '15 at 04:02

score 1 · Answer 6 · answered Sep 28 '16 at 18:40

For people having issues with this code

dim wa as Workbook

Workbooks.OpenText FileName:=path2file, _
DataType:=xlDelimited, Semicolon:=True, Local:=True
set wa = ActiveWorkbook

    wa.SaveAs FileName:=path2file, FileFormat:=xlCSV, _ ConflictResolution:=xlLocalSessionChanges, Local:=True

    wa.Close False

The second line is really important, if wa.Close False is not there it will ask for approval to save, or if wa.Close True it will replace the ";" for ",".

After going into local settings and making ";" the list delimiter, VBA was still separating with a ",". Modified the code to the above and it was done.

Hope this throw some light for some

score 0 · Answer 7 · answered May 26 '21 at 15:56

Great answers here, but they didn't work for me, because I'm trying to create a tool that will work for any user on their own computer, and I don't want to have to set up this locale stuff on everybody's PC. I even tried rolling my own CSV exporter using FileSystemObject, but the destination path name is on SharePoint, so that failed too.

Then I stumbled upon a very simple workaround: create a new worksheet that concatenates all your information into one column, separated by semicolons, e.g.:

CONCAT('Old WS'!A1,";",'Old WS'!B1,";",'Old WS'!C1,";",'Old WS'!D1,";",'Old WS'!E1)

Then add some code like this to export it:

Call Worksheets("Export WS").Copy
ActiveWorkbook.SaveAs Filename:=CSVname, FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close

Because it's all in one column, Excel won't add its own delimiters!

score 0 · Answer 8 · answered Feb 17 '22 at 23:03

Expanding on James Fingas's answer.

SOLUTION:

Concatenate all your information from multiple columns into one column and separate the data in the concatenated string by your separator of choice - i.e. the semicolon. Then copy the one column to a new file and save it.

LIMITATION:

However, beware that this solution is flawed when comma is present somewhere in the concatenated data as the concatenated string becomes wrapped by double quotes - see explanation below.

(1) When you save the file, VBA assumes that comma is the delimeter for the file. VBA also sees that comma is present in your string. Thus, in order to protect against future splitting of the string, VBA wraps the concatenated string in double quotes.

(2) You can try to bypass this problem by setting the system separator to semicolon and saving the file with the "Local:=True" parameter. However, now VBA knows that semicolon is the delimeter for the file. VBA also sees that semicolons are present in your string. Thus, in order to protect against future splitting of the string (VBA doesn't now that you actually want to split using the semicolons), VBA wraps the concatenated string in double quotes.

Save as CSV with semicolon separator

8 Answers8

Linked