27

I have Excel 2011 on my Mac, and when I try to export a .xls file into .csv I end up having a semicolon separated file instead of .csv.

p.s. The System preferences for my keyboard input is Italian

What is the matter?

Improve this question

3 Answers 3

Reset to default
41

Excel for Mac currently has no setting to change the CSV separator from the app itself.

The chosen value separator depends on your Region and your region's default number separators.  To change them, go to “System Preferences” > “Language & Region”:

System Preferences > Language & Region

Then you need to verify the number separators in “Advanced …” > “General”.

Advanced > General

Here are the rules Excel uses to determine how it saves CSV files:

  • If your language / region / locale uses comma (,) for decimal separation (i.e., π (pi) is displayed as 3,1416), then Excel will save using semi-colons (;)
  • If it uses a dot (., a.k.a. full stop or period), then it will delimit with commas (,).

Note:

  • You need to change your general region for it to work. For example, if your region is Germany (which uses , for decimal separation), Excel will always use ; for CSVs, even when changing the decimal point under “Advanced” settings. You could, for example, if you want , as a CSV separator, choose the United States as a region.
  • You need to relaunch Excel for it to notice. Quit the app, change the region, then start it again.
Improve this answer
9
  • 1
    Thank you but it doesn't work. I've tried both of your solutions....
    – Cris
    Oct 19, 2015 at 14:42
  • 1
    Works repeatably here, switch both number & currency comma separators to 'dot' separators & vice versa. Relaunch Excel, open xlsx file, save as csv or windows csv, it saves with the alternate comma/semi-colon
    – Tetsujin
    Oct 19, 2015 at 14:57
  • I tried again, it doesn't work.... both i59.tinypic.com/2z4lt3m.png AND i57.tinypic.com/13yfbci.jpg
    – Cris
    Oct 19, 2015 at 15:34
  • 4
    changing the decimal separator didnt work for me. I add to change the whole "region" from "France" to "United States".
    – cyrilchampier
    Mar 9, 2016 at 15:51
  • 1
    I tried to clarify your post – I noticed that the actual decimal point settings don't matter to Excel. It's the region that needs to change. So for me, living in Germany, I had to change it to US to correctly interpret CSV with commas. I consider this a bug or at least a lack of features in Excel, which should have an option to override the system's settings.
    – slhck
    Aug 1, 2017 at 9:50
6

I've tried the accepted answer with no success. (High Sierra + Excel 16.10) My only option has been to use open office, far away from Excel in this feature.

Improve this answer
6
  • +1 Installed OpenOffice and got what I need. Thanks!
    – geff_chang
    Mar 24, 2018 at 13:19
  • 3
    Another option is to add "sep=," as the first line of the csv file.
    – v.shashenko
    Dec 18, 2018 at 16:55
  • 1
    +1 for sep= :DD
    – user1721019
    Oct 14, 2019 at 8:37
  • 1
    +1 this should be THE answer. Gold for sep=,. I needed tabs: sep=\t
    – Tom Siwik
    Sep 1, 2020 at 15:20
  • How does one 'add something to the first line of the csv file'? Like, what does it mean and how do I do that if I'm in Excel?
    – Jem
    Dec 2, 2020 at 21:38
-1

One more solution.

  1. Select the column.
  2. Click on Data, then click on Text to columns.
  3. Follow the wizard as follows, Delimited -> Select comma, Tab as delimiters -> Finish.
Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.