SEMI COLON (;) COLUMN SEPARATOR INSTEAD OF COMMA (,) WHEN WE EXPORT AS CSV UTF-8 FILE FORMAT

[krish86]

HI,

I have an excel sheet with multiple columns and When I export that as CSV UTF-8 file format, column separator comes as comma. Is there anyway I can change comma to semicolon?

Attached snapshot.

Thanks,

Krish

[#2 · Accepted solution]

@krish86 , that's only if change system separator

- open your file(s) you'd like to save as csv

- Win+R and type "control international", click Additional settings

- Change List separator on semicolons and click Apply

- save your Excel files as csv

- Change back List separator on comma and Apply (or Ok)

Sure you may keep your list separator as semicolon, but that could be side effect in different places, better to keep default one for your locale

[#3]

Thanks Sergei, been googling 1h now and this was the solution

[#4]

@Jon_The_IT_Man , glad to help

[#5]

@Sergei Baklan Dear Sergei, I have the problem other way round. I need a comma separated csv. I followed all the steps - but still if I open the csv in text editor - it is still semicolon separated.

[#6]

@Simone3110 

To be sure, on Windows you have list separator as comma, correct?

[#7]

Dear @Sergei Baklan, I followed the steps you described above and changed it in Excel from Semikolon to Comma - but it does not work. I there a general setting in Windows as well? Cannot find it... My language Settings are switched to German..

[#8]

@Simone3110 

Yes, that's Windows setting, not Excel one. It's here

[#9]

Dear Sergei, sorry my fault. I used the Windows+R and thought it leads to an excel setting. Your above screenshots show what I already did. But still. I convert the xls to an csv - open it in editor and it is still semikolon separated ...

[#10]

@Simone3110 

Sorry, I'm not sure what's wrong. Tried to repeat

- my default Window setting for list separator is comma

- closed all Excel and change setting to semicolon

- start new Excel save as csv, open in Notepad:

- closed Excel

- returned back list separator to comma

- save same file as csv

now it is comma separated

[#11]

Short question. Maybe I am too stupid. But it stays as comma here. You save the Excel as csv. Then you close it and open it in notepad and it is comma separated. You close Excel and "leave notepad open while you do the windows changes or doesnt that matter". If you save the same file as csv. where do you do that. Do you open the file with Excel again after changing the windows settings? Or do you save the file in Notepad as CSV? Whatever I do - it stays as "semikolon".

[#12]

@Simone3110 

Let do step by step.

- create Excel file (test csv.xlsx in attached and save it);

- save copy as csv (my default list separator is comma) - "test csv default.csv"

- close Excel, change list separator in Windows settings on semicolon

- open Excel file, save as csv ("test csv semicolon.csv")

- close Excel, return regional setting back to default (comma in my case)

Another way

- open initial Excel file

- got to File->Options->Advanced

- Find Use System Separator and uncheck it

- I want to change on semicolon, thus set Thousand separator as semicolon, Decimal separator something different

- save Excel file as csv ("text csv advance setting.csv")

- close Excel, open again and tick Use system separators to return settings back.

In general it's not necessary to close/open Excel on each step, just to be sure. In above I changed comma on semicolons, in your case that's opposite situation but all steps are the same.

If "Use system separators" is initially unchecked then this option works.

All generated files are attached.