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.

Import CSV with decimal comma in stead of point

In my language, the comma is used as a decimal separator. But unforunately, Numbers imports csv with decimal points, not comma's. How can I tell Numbers that I want comma's in stead of points?


Thanks!

J

Mac Pro, macOS 12.6

Posted on Oct 7, 2023 4:49 AM

Reply
Question marked as Top-ranking reply

Posted on Oct 7, 2023 6:05 AM

Numbers just imports what it finds in the CSV file, where the decimal separator is already a decimal point in your case.


So you can act before or after importation.

Before: you open the file in TextEdit or other text editor and do a search(.) and replace (,). However you risk replacing legitimate periods (in texts) by commas. Evaluate the risk. Importation might not work correctly if the field separator is also a comma.


After: Basically the same thing with the advantage that you can hide columns before the search and replace. Content in hidden columns will not not be searched and replaced. Converted values may still be left-aligned, so format the column as Numbers and then back to Automatic.


The other option is to see if the source of the CSV file has the option of creating it with different decimal separators.

5 replies
Question marked as Top-ranking reply

Oct 7, 2023 6:05 AM in response to Jaydude

Numbers just imports what it finds in the CSV file, where the decimal separator is already a decimal point in your case.


So you can act before or after importation.

Before: you open the file in TextEdit or other text editor and do a search(.) and replace (,). However you risk replacing legitimate periods (in texts) by commas. Evaluate the risk. Importation might not work correctly if the field separator is also a comma.


After: Basically the same thing with the advantage that you can hide columns before the search and replace. Content in hidden columns will not not be searched and replaced. Converted values may still be left-aligned, so format the column as Numbers and then back to Automatic.


The other option is to see if the source of the CSV file has the option of creating it with different decimal separators.

Oct 7, 2023 8:21 AM in response to Jaydude

If you copy-paste into Numbers, rather than File > Open a CSV file, then you have more flexibility.


You can go to File > Advanced > Language and Region ... and temporarily switch to a region that uses a decimal separator compatible with importing the CSV. Then paste the CSV data into a table, adjusting the delimiter if needed so the data is spread across columns as expected. Then go to File > Advanced and switch back to your original region.


SG



Oct 16, 2023 2:08 PM in response to SGIII

I just noticed something in Numbers 13.2, I wonder if it was there before.


If you open a CSV file which creates its own table, or create a table by pasting data from another source like Google Sheet, Numbers remembers what the data was like originally. So just after importing it shows that for a few seconds in the top-right corner of the sheet:


If the message has disappeared you can still find that at the bottom of the Format / Table side bar:



Clicking on either you're presented with this dialog window:


So if the source data has a decimal point but your language settings requires a comma instead just enter a point in the Decimal Separator field and the conversion is done immediately in the preview, and permanently in the table with Update Table. Quite handy. The other options can also make the importation a better success.


Dec 30, 2023 9:28 AM in response to Jaydude

The Adjust Import Settings box has been there for a few years now.


There seem to be two issues.


If you are in a region that uses , for the decimal separator and you need to import csv with . as the decimal separator as posted above, you can go to File > Advanced > Language and Region ... and temporarily switch to a region that uses a decimal separator compatible with importing the CSV. Then paste the CSV data into a table, adjusting the delimiter if needed (using the Adjust Import Settings if necessary) so the data is spread across columns as expected. Then go to File > Advanced and switch back to your original region.


Recently people have reported trouble copy-pasting from Google Sheets in particular. That seems to be a different problem. First pasting into an another app and from there copy-pasting into Numbers has been reported to work.


SG


Import CSV with decimal comma in stead of point

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