Skip navigation

Prevent Numbers from resolving "equations" on importing

223 Views 9 Replies Latest reply: Feb 6, 2014 8:52 PM by SGIII RSS
Mark Knoll1 Level 1 Level 1 (5 points)
Currently Being Moderated
Feb 4, 2014 11:36 AM

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)
  • Jerrold Green1 Level 7 Level 7 (28,195 points)

    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

  • SGIII Level 4 Level 4 (3,290 points)

    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

  • SGIII Level 4 Level 4 (3,290 points)

    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

  • SGIII Level 4 Level 4 (3,290 points)

    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

  • SGIII Level 4 Level 4 (3,290 points)

     

    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

  • ronniefromcalifornia Calculating status...

    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.

  • SGIII Level 4 Level 4 (3,290 points)

    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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.