Prevent Numbers from resolving "equations" on importing

Question

Level 1

10 points

Prevent Numbers from resolving "equations" on importing

We have a database of items that uses the following system of categorization: category/sub-category, where both "category" and "sub-category" are numbers. So a unique identifier for an item might be "199/23"

Even though the field that holds this identifier in the database is a text field, when I export records from the database that includes this field and open the exported file in Numbers, Numbers will resolve the implied equation and "199/23" comes through as "8.6521739." Even if I then explicitly format that cell as text, since the conversion has already taken place, it does not revert to the way I need it to be.

I do not have the option of changing the database. How can I force Numbers to import "199/23" as a text string?

Thanks in advance.

iMac, OS X Mavericks (10.9.1)

Posted on Feb 4, 2014 11:36 AM

Reply

Answer 1

Feb 4, 2014 11:49 AM in response to Mark Knoll1

Hi Mark,

Until you find a better answer, you could use a word processor or text editor to replace the "/" character with a comma delimiter. This would expand your table by one column and eliminate the conversion. The delimiter would probably require the closing and opening quotes too, or whatever it would take to fully divide the field into two fields.

Would you mind sending me a small sample of your file to experiment with? Just a few rows.

Jerry

Reply

Answer 2

SGIII

Level 8

35,288 points

Feb 4, 2014 11:58 AM in response to Mark Knoll1

Hi Mark,

I can reproduce that problem here when opening a csv file with what Numbers interprets as fractions.

Can your database export as tab-separated as opposed to comma-separated? (Or alternatively do you have a way to convert comma-separated to tab-separated?).

If so, you can open the tab-separated file, select all, copy, click once in an existing cell in Numbers and paste. The table will expand automatically as needed.

My tests here show that the original values 199/23, etc. are preserved when you import that way.

(File> Open a tab-separated file the way you would a comma-separated once doesn't work well, hence the copy and paste suggestion).

SG

Reply

Answer 3

SGIII

Level 8

35,288 points

Feb 4, 2014 12:13 PM in response to Mark Knoll1

If your comma-separated fields don't have commas within them, then you could use a word processor or text editor along the lines Jerry suggests to do a "replace all" from comma to tab (i.e. instead of / to comma). Then select all, copy, click once on an existing table in Numbers, and paste.

If your database has commas within a field, then life gets more complicated. Give a shout if you're in that situation.

And, meanwhile, perhaps go to Numbers > Provide Numbers Feedback in your menu and ask Apple to improve the csv/tsv import.

SG

Reply

Answer 4

Mark Knoll1 Author

Level 1

10 points

Feb 4, 2014 12:36 PM in response to SGIII

Thanks Jerrold and SGIII,

I had already tried both csv and tsv and neither had worked. I couldn't get cut and paste to work either.

What I finally did was to open the tab-separated file directly in Pages and then turn the text into a table, which I could then sort by column.

This worked out best in the long run anyway, since the ultimate destination for the exported data was a Pages file. I thought I had to go through Numbers in order to be able to sort by column, forgetting that I could do the same thing in a Pages table.

That resolves the immediate issue, but it still would be nice to have a little more control over importing into Numbers. I'll let Apple know.

Reply

Answer 5

SGIII

Level 8

35,288 points

Feb 4, 2014 12:40 PM in response to Mark Knoll1

Hi Mark,

open the tab-separated file directly in Pages and then turn the text into a table, which I could then sort by column.

Interesting. So you're saying Pages can File > Open a tab-separated file whereas Numbers cannot? And Pages has some sort of "text to table" conversion that Numbers doesn't have?

SG

Reply

Answer 6

Mark Knoll1 Author

Level 1

10 points

Feb 4, 2014 1:19 PM in response to SGIII

Hi SGIII,

Numbers will open the tab-separated file, but it will resolve the "equation" while doing so. Opening the same file in Pages results in a file where each record is on its own line and the "fields" are separated by tabs. You can then do a Select All and under "Format->Table" choose "Convert Text to Table" and you get a nicely formated table, without the text being altered by the import process.

Reply

Answer 7

SGIII

Level 8

35,288 points

Feb 4, 2014 1:36 PM in response to Mark Knoll1

Numbers will open the tab-separated file, but it will resolve the "equation" while doing so.

Yes, that is what I get here when you try to File > Open a tab-separated file. The file will open, but you end up with a mess in Numbers. That is why I was saying instead of File > Open (as you would do with a comma-separated file) try opening the tab-separated file in a text editor, select all, copy, click once in a cell in an existing table in Numbers, and paste. That should work, and on my machine does not alter the text.

Nice to know Pages has that text-to-table conversion capability (I don't have Pages on the Mac yet.)

SG

Reply

Answer 8

Feb 6, 2014 8:27 PM in response to SGIII

I don't get it. I can't get Numbers to open a .tsv or .tab file. File > Open just shows the files greyed out. Do I need to tell Numbers or the OS that it is okay to open? How? Thanks.

Reply

Answer 9

SGIII

Level 8

35,288 points

Feb 6, 2014 8:52 PM in response to ronniefromcalifornia

Hi Ronnie,

For tsv (tab-separated) open the file in a text editor, select all, copy, and then in Numbers click ONCE on a cell in an existing table, and command-v to paste.

Trying to open a tsv file (which you can do if you change the extension) doesn't work well. But pasting tsv does, often better than File > Opening a comma-separated file.

SG

Reply