Why won't Numbers 09 open when a tab delimited file is placed over the Numbers icon in Applications

If you have tab-delimited data (as opposed to comma-separated data) the best way to get it into Numbers 3 is to open the file in a text editor, command-a to select all, command-c to copy and then, in Numbers, click once in the top-left cell of a range where you want the data to appear, and command-v to paste. The table will expand automatically to accommodate your data.

If you are doing a lot of this, there are ways to automate it further, but in general copy-paste is fast, easy, and efficient.

SG

Edit:

I thought I recalled being able to paste directly to a Sheet, but after posting that option, couldn't make it work. This leaves SG's option as the best I know of.

Jerry

I found I can open the tab delimited file (named TabTest.csv) in Numbers 3.x by dropping the file on the Numbers icon in the dock. A ned document will open with a table that contains the data but it is all crammed into column A.

I can get the data into a table properly by:

select Column A and copy

then create a text box in the sheet and paste

then paste, then select All, copy,

then select cell A1 of the original table (with the data crammed into a single column), paste

then I drop a file named CommaTest.csv, where the delimiter is a comma, onto the Numbers icon in the dock a new documnet appears and a table contains the data correctly.

The Tab separated data looks like and imports like:

Item Description Amount

1 dsfa 2

2 ghdhg 4

3 hgfcvbnrty 5

4 gerbg ytn 8

5 ikmhjdrtsg 12

6 vqzzedw 18

7 qwerewqtr 22

8 pouipoiup 30

The Comma separate data looks like and imports like:

Item,Description,Amount

1,qwere,89

2,dsfdasf,90

3,xcvzv,678

4,vbnbvcuyt4n,456

5,ghjkyu,654

6,yuioiuyo,432

7,wertwert,234

8,mnbvmbnv,123

you can keep the Numebrs icon in the dock by clicking on the icon and holding until the contextual menu appears then select "Options > Keep in Dock":

Table on left contains imported data from a tab delimted file. Text box on right ready to received pasted data:

Selected data in column A, copy

Data pasted into text box, on right, then aelect all, copy, select cell A1 in table paste

Nathan,

Dragging to the Dock when the Numbers icon isn't there would be pointless, I think. It's possible to have the Numbers icon stay on the Dock even when Numbers is Quit. click an hold the Numbers icon on the Dock while Numbers is running and select Options > Keep in Dock.

Jerry

Or, if you've already ended up with data all crammed into column A, just select the cells in column A, run this script, and paste the "columnized" data where wanted.

Or, perhaps easier, solve the problem at the source by using this self-installing TSV to Clipboard Automator Service. Select the tab-separated file in Finder (extension doesn't matter as long as the data it contains is tab-separated) and choose 'TSV to Clipboard' from the contextual Services menu:

Then single-click a cell in Numbers, and command-v or option-shift-command-v to paste.

This is just a simple variation on the CSV to Tabs on Clipboard service Badunit and I have posted elsewhere.

SG

nathanschwam wrote:

Oooops, I actually have a comma delimited text file.

Ah, in that case this CSV to Tabs on Clipboard service might simplify things. Same usage as above. No need to fuss with icons on the Dock or text boxes, etc. To use: Select the file with csv data in Finder, right-click, choose 'CSV to Tabs on Clipboard' from Services contextual menu, single-click a cell in Numbers, and paste.

SG

Hello

Any decent spreadsheet programmes should open well-formed csv file correctly. Numbers '09 should work.

Just note that csv file must have name extension as .csv, not .txt, for Numbers '09 to interpret it as csv.

Also note that there're two major variants of csv format – one uses comma as field separator and another uses semicolon as field separator – and the latter is usually used in the regions where decimal separator is comma. If you're using Numbers '09 to open semicolon-csv when your number format is set to use period for decimal separator, you'd need to temporarily change your number format to use comma for decimal separator in order to import the semicolon-csv properly. Conversely, to import comma-csv in decimal-comma number format environment, you'd need to temporarily change to decimal-period number format environment.

Hope this may help,

H

Hi H,

With one simple change where indicated in the script, the service can handle comma-separated, semi-colon separated, or tab-separated. In most cases no need to worry about going back to Numbers 2.3 ('09) or changing environments or changing file extensions (though a change in number format *might* sometimes be needed, I think).

Hope Apple eventually builds more robust import capability into Numbers 3. Meanwhile, the service is not at all inconvenient to use. Choose file in Finder, right-click, make choice from contextual menu, paste. Doesn't get much easier than that.

SG

TSV has its own flaw. It has no quoting mechanism and thus it cannot contain tab or return in field data. In this regard, TSV is far worse than CSV because multi-line data in one cell is completely valid and nothing special.

As far as I can tell after browsing through the script in the linked workflow, the csvToList() handler seems to process quoting in CSV properly but the code to generate TSV simply ignores quoting, which will result in data corruption if any field contains tab or return.

Again, any decent spreadsheet programme should open well-formed CSV without problem. Since there're two major variants of CSV format, spreadsheet programme should provide a menu to specify the field separator (and number format as well) per CSV file when importing it. Numbers.app lacks this simple feature and makes the user fiddle with the system's number format accordingly just to import a CSV file. Considering the fact that CSV is commonly used for data exchange around the world and it's not uncommon to process a variant of CSV other than the native one in the user's region, the lack of this simple feature is serious design flaw.

Regards,

H

PS. I wrote about Numbers'09 in my previous post just because I thought the OP is using it under 10.8 as shown in the profile.

Hi H,

I am sure Numbers 3 is not perfect. Certainly Excel imports far better. And of course TSV/CSV are flawed formats. This script can't handle every possible case perfectly. Nor can Numbers 2. Nor can Excel.

But why let the perfect become the enemy of the good?🙂 The tool works well enough to be very useful. I think Nigel Garvey posting at macscripter.com did a bang-up job with the csv conversion handler (here using the term "csv" broadly to mean character-separated-value, which includes not just comma, but tab and semicolon).

Have you tried the script wth Numbers 3? Would love pointers on how to improve it if you discover flaws. In particular it would be nice to have it detect the separator automatically so the user doesn't have to go into the script to change that. I know that's probably quite easy (since the manual change is so simple). But automating that (alas) is above my level.

SG

Hello

Well, I'm not talking about sophisticated perfection but really common requirement – a quoting mechanism which any text based data representation model should implement.

TSV lacks quoting mechanism that CSV has, that is the proper CSV exporter correctly quotes user data which contain resevred token such as field separator and record separator as well as quote character itself and CSV importer correctly unquote them whilst TSV exporter and importer (implemented and invoked in Mac's copy and paste operation on table data) do not. Because of this, converting CSV to TSV can result in user data corruption or user data modification. By user data modification, I mean we can arbitrarily replace, for instance, tab with 4 spaces, return or linefeed with U+2028 LINE SEPARATOR in user data in order to keep them from being interpreted as field and record separator and if we do these replacements in TSV exporting process (which includes CSV to TSV conversion process) and do NOT restore them back to the original text in TSV importing process, which is the case in pasting TSV data into table in Mac, the resulting user data are not the same as the original in case the original user data contains tab or return or linefeed.

CSV, on the other hand, does not have such problem because it has decent quoting mechanism and CSV importer will correctly unquote the quoted data.

After all, what I wanted to say as an answer to the original question of this thread is that there should be no need to use potentially problematic script in order to import CSV data and Numbers'09's built-in CSV importer should suffice. And that if the importer is not working as expected, the primary suspect is the file name extension which must be .csv and the secondary suspect is the number format in system preferences which should be set to use period for decimal separator in order to import CSV using comma for field separator.

And last but not least, please take no offence in my posts, for it's not at all my intention to criticise your dedicated works on this forum. I'm only concerned about integrity of user data – it would be easy to identify data corruption in imported CSV data with 200 records but not with 20K records and so user should be warned of that possibility and advised to use preferred and designed method if any.

All the best,

Hiroto

PS.

As for Numbers v3, fortunately or not, I don't use it.

As for auto-detection of the field separator in CSV file, generally speaking, it is impossible.

E.g, if the field separator is not given in advance, a CSV record such as

1,2;3,4

can be interpreted as two different ways:

field 1 = 1,2
field 2 = 3,4

or

field 1 = 1
field 2 = 2;3
field 3 = 4

and there's no way to determine which the original data structure is, without assuming a priori knowledege of the original data.

H

Hi H,

No offense taken here, of course. Just trying to learn as much as possible here from a true scripting export, and have something that is "good enough for government work" until such time as Apple devotes resources to improving the import filter. I am a veteran of the csv "mangled data" wars, and Nigel Garvey's handler has turned out to be much more robust than I originally thought it could ever be.

On the auto-detection of delimiter, I see your point that it is impossible when neither the delimiter nor the number of fields is known (though your example 1 seems "more likely" than 2). But what if you don't know the delimiter but you do know the number of fields in advance? Can it easily be done then?

And if auto-detection isn't practical, how can it be made easy for the user to specify the delimiter without opening the script and changing:

set csvList to csvToList(csvString, {})

to:

set csvList to csvToList(csvString,{separator:";"})

or

set csvList to csvToList(csvString,{separator:"|"})

etc.

?

It seems as if that should be easy. I know how to do a dialog where the user could specify a delimiter. But I don't know how to work the result into the script. I don't dare mess with Nigel Garvey's handler, which does its magic better than the CSV library routines in Python, etc. Any suggestions?

Thanks for your really sophisticated contributions here!

SG