You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Numbers exports csv with semicolons instead of commas!

I've read a few posts with the same problem, but now in 2019 I still experience the same problem. Is using EXCEL instead of Numbers the only way?

Posted on Nov 26, 2019 3:49 PM

Reply
Question marked as Top-ranking reply

Posted on Nov 27, 2019 3:56 AM

The easiest way I've found to get comma separated instead of ; separated is to go to System Preferences > Language & Region > Advanced ... and temporarily change the Decimal separator to a period (.)



After the export to CSV change that back to , if that is what you normally use.


SG


9 replies
Question marked as Top-ranking reply

Nov 27, 2019 3:56 AM in response to redstarsurf

The easiest way I've found to get comma separated instead of ; separated is to go to System Preferences > Language & Region > Advanced ... and temporarily change the Decimal separator to a period (.)



After the export to CSV change that back to , if that is what you normally use.


SG


Nov 27, 2019 9:02 AM in response to redstarsurf

Don’t believe everything you read on the internet, and definitely consult sources in countries other than your own. I’m sorry, but the statement that it means “comma separated values” is very US centric and is by no means universally recognized. Many regions use a comma in place of a decimal in numbers. One and a half would read “1,5”. Image the chaos if these regions used a comma as the delimiter.

Nov 27, 2019 5:49 PM in response to redstarsurf

CSV used to mean comma-separated-values. But then other delimiters were pressed into service (to accommodate the diversity of number formatting around the word) and the acronym has now come to mean character-separated-values. The people who write Wikipedia articles and the like are behind the times as to how the term is actually used these days "in the wild" in many, many software applications.


Purists might say that CSV is for commas only and DSV (delimiter-separated-values) should be used for other delimiters. Wikipedia actually has an entry on DSV. But who has even heard of DSV and isn't "delimiter" a scary-sounding math-like term? That's more confusing than using the good old, familiar CSV. BTW, I think it's a stretch to say CSV is somehow "US-centric." It's just a very old "standard" (and a loosely defined standard at that) that has been around for decades to facilitate the transfer of data between different applications.


In any case, temporarily changing the delimiter in System Preferences does the trick. Hope you found that suggestion useful.


SG

Nov 27, 2019 1:29 AM in response to Bismarck2387

Thanks a lot for trying to help me. Unfortunately, this solution doesn't make me happy, at all.

  1. CSV is supposed to be a comma separated value, why numbers exports the values separated by a semicolon? For me it looks like a massive bug. Has nothing to do with regions. CSV should be comma separated even in on a different planet.
  2. I tried to export the files in my office from Excel on Win10, and it worked fine from the first time! Excel separated the values in the CSV with commas. The region is the same I have at home on my MacBook.
  3. I need to migrate data from one inventory to another and I need to upload it in a csv file.


Do I need to install Excel or OpenOffice, because it looks like Numbers is full of bugs?

Nov 27, 2019 3:10 AM in response to redstarsurf

Hi redstarsurf,


CSV stands for Character Separated Values. The character is not necessarily a comma. In regions that use a comma as the decimal separator, the CSV separator needs to be something other than a comma. No, it is not a bug. It is expected behaviour.


To help us help you, please reply with:

  • the version of Numbers: Menu > Numbers > About Numbers;
  • your operating system: Menu > Apple > About This Mac (or are you using an iOS machine?);
  • your Language & Region: System Preferences > Language & Region;
  • What does the CSV look like when you open it in TextEdit?
  • Are you exporting or importing the CSV?


Regards,

Ian.



Nov 26, 2019 4:11 PM in response to redstarsurf

I just exported this


As a CSV and opened it with a text editor on my iPad, and I get this:



Looks like a comma delimiter to me, so you may try adjusting your regional settings if your region uses a semicolon delimiter. My region is Panamá.


If that fails (which it shouldn’t), when I import in Numbers on my iPad I can choose any character as a delimiter, and if I can do that in Numbers with an iPad, surely it can be done with Excel on a PC, so I’m not sure I really see the issue.



I refuse to believe there is anything I can do with my setup that a desktop running Excel, which presumably is what it will be imported with, cannot do, so I’m not convinced that having a comma as the delimiter is critical. But if it is, as I said, adjust your region.

Nov 26, 2019 5:15 PM in response to Bismarck2387

I did a little digging and found that indeed, the delimiter cannot be manually adjusted from within Excel on a Windows machine. Mind=blown. There is actually something dealing with spreadsheets that I can do on an iPad that I can’t do with Windows. For shame, Microsoft. And they actually charge money for Excel?


I retract my comments about the specific delimiter being irrelevant. My solution though, to change your regional settings, stands.


An alternative would be to export to a temporary location, open it with any text application, use Find and Replace to change the semicolons to commas yourself, then send it to the recipient. But changing region is easier.

Nov 27, 2019 6:10 AM in response to redstarsurf

I’m sorry my answer didn’t make you happy. It was the correct answer though. As Yellowbox has already pointed out, CSV in contemporary usage means Character Separated Values. Perhaps at one time it meant Comma, but this is no longer the case.


Different regions use different settings. The approach Apple (which none of us here are employed by - this is a community based discussion board) has always taken is to keep it simple, rather than giving us a list of things we can change, but rarely need to do so, and doing so without knowing what we are doing can land us in trouble. There is something to be said for this approach, particularly for personal usage. For professional applications, there is something to be said for full customization options. Most people who prefer that approach are using Windows based PCs and Android phones, not Apple products, but as I pointed out, in this instance Apple actually gives you more customization options than Windows does - on the importing side, you can choose which character to recognize as a delimiter. In both Excel and Numbers, the default is set by machine, and is based on your region. Apple makes it easy to change this region.


Believe it or not, there are places in this world where temperature is still measured in Fahrenheit and distance in miles. There is no universal standard for many things. How annoying would it be if you lived in the UK, and you had to manually change every distance calculation because the default was miles? Having the default be set by region is a logical solution, not a bug.

Nov 27, 2019 8:08 AM in response to Bismarck2387

Thanks a lot everyone for your intention to help. At the end the solution proposed by SGIII did the job. Thanks for that. And regarding CSV - according to most trustful sources on internet it is still supposed to be a Comma Separated Value, as it's always been. The problem here is that Numbers is just not as good as Excel. Why is it so hard to agree, that even a generally better working system can have some bugs? Well, anyway, shabbat shalom every one!

Numbers exports csv with semicolons instead of commas!

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.