Importing a CSV into Numbers does not work

It is because of extraneous spaces. Instead of a comma as a delimiter, your file has a comma followed by a space. That space is interpreted as the first character of the next column, followed by the quotation mark, the text you want, and then the closing quatation mark. In TextEdit Find/Replace All "Comma Space" with "Comma" then inport the file.

 CSV is a standard format, and the C in CSV stands for comma

Actually CSV is only "sort of" a standard format. It comes in many different flavors. The "C" now stands for "character" in character separated format" and can be , or ; or | or various other characters, including tab. The semicolon ; is often an excellent choice if you have that option at the source.

But as you say, just replacing the , with ; won't work here as you have commas embedded within the Numbers.

The problem here, that confuses both Numbers and Excel, is that you have an extra space after each comma separator, ie. instead of "A","B","C","D","E","F","G" you have "A", "B", "C", "D", "E", "F" ,"G"

Simply remove that extra spaces (don't worry about removing commas or currency symbols and all that; just remove the extra spaces) and the data should import as expected. I removed the spaces by pasting into TextEdit and doing this search-replace:

I then pasted the result directly into a Numbers table.

SG

Full post got cut off...

I copied the data you posted in this thread and pasted it into a new text file (shown here in VIM):

then I opened that file with Numbers and got this:

If you can, I suggest omitting the thousands separator (the comma in the numeric values). the double quotes do not properly assist Numbers in identifying fields.

If you can, use tabs as the field delimiter and omit the double quotes and commas.

Can you post your source and the result in Numbers?

Here is what I did to be able to import the file correctly:

1) Remove the double quotes by replacing '"' with nothing:

2) remove the currency symbol by replacing '$' with nothing:

3) replace the field delimiter (which is a comma followed by a space) with a tab:

4) remove all remaining commas (which should be the thousands separators):

5) Save text file, then open file with Numbers:

I just noticed I need to fix "Acme Company" and put the comma back i the text file:

"ACME Company Inc." should be "ACME Company, Inc."

save text file and open in Numbers

Have you tried the options below

https://help.apple.com/numbers/mac/6.1/#/tan9f3c54bdc

look under «Change settings for a delimited text file»

I copied the data you posted in this thread and pasted it into a new text file (shown here in VIM):

then I opened that file with Numbers and got this:

Can you post your source and the result in Numbers?

It seems to treat , as a separator despite the "$3,500". This is what happens when I exchange , for ; in the appropriate places...

I’ve noticed that numbers exports csv with « ; » as the seperator. Use find and replace , with ; and import afterwards

I agree with @SGIII. Generally, if you are getting double-quotes alone in cells, it's either because the app importing (in this case Numbers) misinterprets CSV based on differing pattern exports, or because you are missing an errant comma or quote. @Wayne Cotello's extensive data-munging in most cases isn't necessary.

I don't like to use TextEdit for these tasks, though it will work in most circumstances. SubEthEdit and BBEdit are unlikely to try to "interpret" a text file; both have "show invisibles" which might lead you to a problem, and BBEdit has the command "Zap Gremlins" which eliminates non-alpha numeric characters which might also cause you problems.

But @SGIII has found the specific thing that is messing with your file. 'Nuff said

It is a messa for me, too. Not sure why yet.

"This cannot be right. CSV is a standard format, and the C in CSV stands for comma."

Well, sort of…

CSV, more accurately expanded as Character Separated Values, is a fairly 'loose standard. In North America, where the decimal separator is a period / full stop, the original "C" was, and for the most part, still is, a comma. Elsewhere in the world, where the decimal separator is a comma, the more common "C" is a semi colon, which doesn't conflict with either the decimal separator or the thousands separator.

Perhaps the simplest solution to this would be to open it with a more versatile spreadsheet application, such as LibreOffice.

Here is the result of copying the text from your post, pasting it into TextEdit, then with no editing of the pasted text, saving as a text file with the.csv extension, and opening that csv file directly into LibreOffice v5.1.4.2:

If you need the file to be in Numbers, that requires only these actions:

Select all the cells containing data in the LibreOffice table.

Copy,

In Numbers:

In an empty table, select (one click) the cell you want to contain Client.Matter/Inv #

Paste.

Regards,

Barry

Recent versions of Numbers (unlike perhaps the older versions you may still be using) are excellent at importing character-separated values. Better even, in some cases, than Excel, which is also very capable at the task (with its Text to Columns function).

I don't have much experience with LibreOffice because, also, some of its modules are buggy and I also don't like the idea of having to install a compatible JRE (Java Runtime Engine) on my Mac.

For me it is easier (and perhaps more secure?) to simply use an included app (TextEdit is simple and powerful) and use that to perform a one-second find-replace operation if needed.

In general it's better (if possible) to address data format problems at the source. Often it's possible to tell the source to omit those extra spaces or to use another separator. Given a choice a ; is often an excellent option not just outside North America but also within North America as well. A tab is often an even better choice, as a tab is less likely to be embedded the text and also spreadsheets like Numbers and Excel directly accept pasted tab-delimited data.

In any case Numbers, in its current version, has become a truly excellent tool for this job. I find little need to download extraneous software or fire up Excel (as I used to do).

SG