.csv files not imported correctly with Numbers

Hi Xav,

"…apparently not solved yet by Apple…"

I would suggest, given that :

• Numbers has been in existence for about eight year now, and its predecessors, AppleWorks and ClarisWorks date back to late 1991, and
• No version of any of these applications has recognize semicolons as CSV separators,

that Apple does not see this as a 'problem,' and that where there's no problem, you shouldn't expect a solution any time soon.

To inform Apple that you do see this as a problem, and to request a solution, launch Numbers, go to the Numbers menu, choose Provide Numbers Feedback, and request a feature enhancement, with reasons why the enhancement is necessary and would benefit Apple.

In the meantime:

Open the CSV file in MS Excel, or in LibreOffice or Apache OpenOffice (both free to download and use, although they do request donations), then either do you work in that application or save the file in Excel format, then import it into Numbers,

OR

Open the CSV file in a Text editor application (you'll find TextEdit in the Applications folder on your Mac)

Use Find - Replace|All to replace the semicolons with commas.

Save or Save As... filename.csv

Control -click (or right click) the file in Finder, and choose Open with > Numbers 3.x.x or Numbers 4

OR

Open the CSV file in a Text editor application (you'll find TextEdit in the Applications folder on your Mac)

Use Find - Replace|All to replace the semicolons with tabs. (click in the Replace box, press option-tab to nsert a tab character)

Select All of the changed text.

Past into a New table in Numbers.

Regards,

Barry

OR just use this CSV to Tabs on Clipboard Automator Service (Dropbox download). It provides functionality similar to Excel, and doesn't require fooling with other applications.

Double-click dowloaded .workflow package to install the Service. You may need to give permission at System Preferences > Security & Privacy and make sure Automator.app is checked at System Preferences > Security & Privacy > Privacy > Accessibility.

Thereafter, all you need to do is navigate to the csv file in Finder, right-click, and choose 'CSV to Tabs on Clipboard' in the Services submenu.

Answer the prompt ( ; is one of the choices), click once in a Numbers cell, and command-v to paste. That's it!

SG

While testing in Numbers 4.0.1 in Sierra I found to my (pleasant) surprise that semicolon-delimited csv files import correctly. The Automator Service remains useful for other delimiters, however.

SG

While testing in Numbers 4.0.1 in Sierra…

Whoo Hoo!!!

That took a while! :-)

Regards,

Barry

Xav-supp-Apple wrote:

it is not a straightforward process (as it is in Excel, but I want to get rid of Excel and stick to Apple software).

I will report it to Apple as you suggest it 8

I'm curious if you read my posts above....🙂

Numbers 4.01 already imports semicolon-delimited CSV. You would be reporting a problem that I believe has already been addressed.

If it isn't convenient for you to upgrade to Numbers 4.01 now then there is a really easy solution; just use the CSV to Tabs on Clipboard Automator Service that I linked to above (and again here). In less than a minute you can have it up and running.

SG

Xav-supp-Apple wrote:

SGII,

I have tried to follow the steps you suggest to install "CSV to Tabs on Clipboard Automator Service", but

Automator.app does not show listed in System Preferences > Security & Privacy > Privacy > Accessibility.

Hi Xav,

The System Preference looks like this. Automator.app may already be checked on your machine, in which case you don't need to do anything. If Automator.app doesn't appear in the list at the right, you can add it to the list by clicking the lock at the lower left, then the + sign, and then navigate to and select Automator from the Applications > Utilities folder. This is much easier to do than to describe; it should take less than a minute.

The fact that Numbers isn't splitting your text at the ; character may mean there is something unusual in the format that is throwing Numbers off. If the Automator Service can't process it correctly either, could you post a small portion of the data (just paste part of it into a post, first disguising anything sensitive) so we can check the format?

SG

Xav-supp-Apple wrote:

apparently I do something wrong as SGII and you could correctly import CSV files with ";" separators with this new Numbers version, but I can't ...

Hi Xav,

Did you see my suggestion to post part of your data so we can check out the format?

And did you try to import the data using the Automator Service, which is good at handling lots of format variations (CSV is not a strict standard, so there can be little differences that one application can handle but another can't).

SG

Hi Xav,

My reply to SGIII was in reference to his statement that Numbers 4 can do this import. I haven't moved to Sierra or to Numbers 4 yet, so haven't tested it myself.

Supplying a sample csv file will allow him to test it with a file similar to what you are using, and may lead to a solution.

Your Feedback to Apple, if a solution is not reached, should include a description of the results of attempting to import a semi colon separated file which includes decimal numbers using , as the decimal separator.

Regards,

Barry

Hi Xav,

I tested with Numbers 4.0.5 using the data below in a file named

csv-semicolon.txt

It also works fine if the file is named

csv-semicolon.csv

What is the name of your file and what does your data look like?

SG

The data in the file:

col1;col2;col3

a;10;20

b;30;40

c;40;50

Removed by me as redundant following Xav's message above, posted while this post (now removed) was being composed

Barry

Barry wrote:

Any change if you edit the two 40s in the CSV file to 4,0 (four point zero, as it would be written in the OP's region—as noted in the OP's first reply to you)?

After changing the two 40s to 4.0 here is the result in Numbers 4 with Region set to 'United States'.

SG