Numbers does not import CSV files properly

allurenl wrote:

Appreciate the script and I wonder: what other scripts you may have that are helpful ?

Glad you found the script useful. Over the years I've ended up with a number of mostly short scripts that extend the capability of Numbers.

Some of these I rarely use; they were developed in response to questions here. Others, like the one above in this thread and 'Text to Columns' and 'Copy Distinct' and 'Flag Duplicates' have withstood the test of time.

If you have questions on one of these topics where a script may help in your work then perhaps you could start a new thread.

SG

might be worth providing an example of the file, or, perhaps share the data... No-one here can change Numbers, we are all users like you. So the only options we have are to provide solutions using what already exists, or to work around issues by other means.

You can always provide feedback to pole using the menu item "Numbers > Provide Numbers Feedback"

Tony Bris wrote:

Unfortunately I can't share the data because it is a download from my bank.

Hi Tony,

Just open a copy of the download file from your bank, open in Textedit (in your applications folder), delete all but the first few lines, change a few names and numbers if necessary to disguise them, and paste into a post here. That should take less than a minute, and can save you (and us) a lot of time as it will immediately show the exact format (csv comes in a lot of flavors). Almost certainly there are easier ways to solve your problem than fooling around with a 7-year old version of Numbers.

SG

I still use Number '09 to open quicken exchange format file (QXF). This was dropped in going from Numbers 2.x to 3.x.

This is the primary reason I keep Numbers 2.x around.

That format looks a little strange. For example some of the quotation marks are "straight" and some are "curly". That could have happened in the copy-pasting process. CSV is not a "strict" standard and there are endless little variations that can cause problems.

Assuming there are no commas within a column in your download (e.g. descriptions that have commas in them) then the simple script below should fix the file so you can paste the values directly into Numbers. No AppleScript knowledge needed to use the script, just copy-paste.

1. Copy-paste the script below into Script Editor (in Applications > Utilities).
2. If you have not used scripts on your machine make sure the box by Script Editor.app is checked at System Preferences > Security & Privacy > Privacy > Accessibility. You only need to do this once.
3. Click the triangle 'run' button and navigate to the downloaded csv file, which is assumed to have a csv extension.
4. Click once in a Numbers cell and command-v to paste in the values.

If this doesn't quite do the right thing then just post results and the script can be adjusted. Once it's working you can just use the script every time you do a download. You can turn it into a menu pick. Because it places the results on the clipboard for pasting wherever you want it in an existing Numbers document it may be a little easier than having to open the csv file in a new Numbers document each time.

SG

set inText to read (choose file with prompt "Choose csv file" of type "csv") as «class utf8»

set outText to substitute({"“", "”", "‘", "’", ",", "&"}, {"", "", "", "", tab, "&"}, inText)

set the clipboard tooutText

display notification "Click once in a Numbers cell and command-v to paste"

on substitute(s, r, t)

repeat with i from 1 to count s

set text item delimiters to s'sitemi

set t to t's text items

set text item delimiters to r'sitemi

set t to t as text

end repeat

return t

end substitute

Trying the script will take you less than a minute. Copy-paste-click and you're done. Why not give it a try? It's really easy to try and it might let you get work done in the meantime. It works on the sample you posted.

Note that the sample you provided here also confuses Excel, which is really good at parsing CSV files. That is a pretty good indication that something went wrong with the file.

CSV (character-separated-values) is a loose standard. If the delimiter character is a comma, straight double quotes are needed around values that include a comma within them. If the delimiter character is a semicolon ; quotes are needed around values that include a ; within them. Including quotes around a value even when not needed typically doesn't confuse parsers.

The quotes do typically have to be straight, not curly. How you ended up with curly quotes in your file isn't clear. It could be you opened it in an app, Numbers or TextEdit or many others on the Mac, where you Smart Links turned on in the Edit > Substitutions menu. Best to turn that off when working with CSV files.

Anyway, give the script a quick try. It might do the trick.

SG

Hi Tony,

Fantastic to hear it worked. Now at least you can carry on while awaiting a more permanent solution. And also check out the Smart Links setting in any apps you use to open the downloaded file. That could be a source of some of the problems. Thanks for the green check!

SG

Tony,

You probably already figured it out, but a couple of posts up I meant to write turn off 'Smart Quotes' in the Edit > Substitutions menu. I wrote 'Smart Links' by mistake. It's 'Smart Quotes' that can introduce "curly" quotes that can be a problem in CSV files.

SG

Thanks SGIII, works fine, even for someone like me who never uses scripts! Great!

Glad it worked for you. Scripts can make life easier. Writing them takes time, but often using them is fast and easy and doesn't require previous experience.

SG

Great help ! I have been figuring this out too. Appreciate the script and I wonder: what other scripts you may have that are helpful ?