Forum Discussion

krish86's avatar
krish86
Copper Contributor
Apr 09, 2019
Solved

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

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 sna...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 09, 2019

    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

Simone3110's avatar
Simone3110
Copper Contributor
Apr 03, 2023
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 ...
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 03, 2023

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

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 04, 2023

    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.

  • Simone3110's avatar
    Simone3110
    Copper Contributor
    Apr 04, 2023
    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".

Resources