How can I get Numbers to keep the leading zeros in a .csv file cell?..

Question

Level 1

9 points

How can I get Numbers to keep the leading zeros in a .csv file cell?..

I have a csv file exported from a website. There are id numbers in the first column. When I open the file with Numbers all leading zeros are trimmed from the id numbers.

This issue also comes up when dealing with zip codes (MA has a leading 0 in most zip codes for example).

Is there any way to configure Numbers to preserve those leading zeros when it opens new files of different formats?

I have poked at all the preferences I could find. It may be an OS X issue as well, perhaps there is a setting in system preferences I'm not aware of. Any suggestions would be welcomed.

MacBook Pro, Mac OS X (10.7), 15" 2.53 Core i5

Posted on Feb 11, 2015 7:28 AM

Reply

Answer 1

Necris Rex Author

Level 1

9 points

Feb 11, 2015 8:48 AM in response to t quinn

that's at too late of a point. It truncates all leading zeros from anywhere in the file on open. I didn't hand generate the file or the data. It's a CSV. I can ask for a webpage to export the CSV to me and when I open it in Numbers all the leading zeros are gone. They are significant as they are identifiers, such as a user id. .csv is plain text, there is no formatting option on that file type. it makes it impossible to generate a report for a system I'm working with and then cross file the data as 0001122 is significantly different from 1122 and 001122 or any other combination of leading zeros in the system of record.

I was hoping to a way to open the file (perhaps there was an import I was not finding, but when I double click it just opens and removes the leading zero data).

Thanks for your reply, but it does not help.

Reply

Answer 2

SGIII

Level 8

37,532 points

Feb 11, 2015 10:30 AM in response to Necris Rex

Hi Necris Rex,

If you are having trouble keeping the leading zeros even after first formatting the cells as text and then importing the csv values (perhaps you are importing the csv via File > Open into a new document) then you can restore the leading zeros without much trouble:

Insert a new column next to the column where the leading zeros have not imported as you want. Here I assume the original values are in column B and your new column is C.
Put this formula in column C: =RIGHT("00000000"&A,6) , and fill down the column.
Select column C, command-c to copy, and, with the column still selected, Edit > Paste and Match Styles.
Delete the original column that has the missing leading zeros.

Replace the 6 in the formula with the number of digits you need, so the formula knows how many zeros to "pad."

The above assumes you actually need the leading zeros back. If you just want to display things nicely in your Numbers table, then formatting as Numeral System and setting Places to 6 (or whatever) is quick and easy, no formulas required.

SG

Reply

Answer 3

Badunit

Level 7

30,348 points

Feb 11, 2015 10:39 AM in response to Necris Rex

One idea:

Get the "CSV to Tabs on Clipboard" script/service (from SGIII's post here: how do I open a csv file using numbers so that the data appears in separate columns

Pre-format a table in Numbers with all cells formatted as text

Run the script on your CSV file

Paste into the table

Reply

Answer 4

Necris Rex Author

Level 1

9 points

Feb 11, 2015 10:34 AM in response to SGIII

Thank you so much both restoring the leading zeros via your first suggestion was perfect for my export/restore needs. The formatting in the second was also a great suggestion and will work for most of my use cases as well.

Reply

Answer 5

t quinn

Level 5

7,063 points

Feb 11, 2015 8:31 AM in response to Necris Rex

Hi Necris Rex,

If you format the cell as text it will preserve the 0's. You can also make a custom format for a number but you will have to have consistent lengths of your ids.

quinn

Reply

Answer 6

Hiroto

Level 5

7,467 points

Feb 11, 2015 1:21 PM in response to Necris Rex

Also you may put apostrophe (') in front of data in csv.

E.g.

'0001,'0002

or if quoted,

"'0001","'0002"

Regards,

H

Reply