Getting trailing zero in csv file for 15 digit number

Question

I am having a column in csv with 15 digits with value 1234567812345678 but it always coverts the last digit with zero 1234567812345670. I have formatted them as number.

Are you opening the CSV file in Excel directly? The preferred method is to use "Get External Data" — wysiwyg, Mar 02 '17 at 23:51
So try using Get External Data -> From Text instead. It's on the Data tab. Select Text for Column Data Format — wysiwyg, Mar 02 '17 at 23:55
I tried to store as Text wont work. A zero is still trailing — maxspan, Mar 03 '17 at 00:05
It works for me, just tested it. Again, you need to use Get External Data. Are you doing that? — wysiwyg, Mar 03 '17 at 00:07
Yes I went to Get External Data -> From Text and it open a file Dialog box to select a Text file. I dont have a text file. — maxspan, Mar 03 '17 at 00:11
Also when I set the cell format to text it work and when I close and open the file. the leading zeros come back. — maxspan, Mar 03 '17 at 00:15
just put an apostrophe before the value to make it text '012345678901234567890. Don't format as text. How to stop Excel from formating the 16 digit account number in a way to avoid making the last digit zero? — phuclv, Mar 03 '17 at 05:41
@LưuVĩnhPhúc according to RFC4180 the right way to avoid such issues in CSV is to "enclosing them in double quotes (Hex 22)" — Alex, Mar 03 '17 at 07:05

score 3 · Answer 1 · answered Mar 03 '17 at 00:21

As Sir Adelaide answered, Excel supports number precision only up to 15 digits. But you can still save numbers with many more digits if you store it as text.

When you open a CSV file in Excel directly, Excel will automatically convert your numbers to number format, causing the truncation. Use Get External Data to bypass this:

In a new Excel file, go to the Data tab, and click From Text in the Get External Data section. Navigate to your CSV file and hit Open.

Now choose Delimited, check My data has headers if it does, hit Next.

On the next screen, assuming your delimiter is a comma as it is a CSV, make sure only Comma is checked and hit Next

This is the crucial step: Select the column(s) in the Data Preview section with the long numbers and choose Text:

Then just hit Finish, select where you want to put your data, and you're done!

But this will create new file and needs to be saved as excel sheet not csv. I want to save it as csv. If I save it as csv the number are back converted to trailing zeros. — maxspan, Mar 03 '17 at 00:41
I don't know what you mean. I can save the file from Excel as a CSV and it keeps the full numbers just fine. What are you trying to do exactly? Can you provide a screenshot of how the CSV file looks? Just make sure to replace the CC info with dummy data... — wysiwyg, Mar 03 '17 at 01:34

score 2 · Answer 2 · answered Mar 02 '17 at 23:58

2

You've run into the maximum precision that Excel can handle (15 digits). See

https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Excel_2010

for more detail.

answered Mar 02 '17 at 23:58

Sir Adelaide

4,959

1

so whats the solution – maxspan Mar 02 '17 at 23:58
@maxspan honestly, use smalller numbers. – Ramhound Mar 02 '17 at 23:59
I want to add credit card numbers mate – maxspan Mar 03 '17 at 00:00
try http://superuser.com/questions/373997/adding-more-than-15-digits-in-excel – Sir Adelaide Mar 03 '17 at 00:00
If you store it as text as I said in my comment above on OP's question, you can go way beyond 15 digits...and if they're CC #s then I assume no calculation is needed – wysiwyg Mar 03 '17 at 00:00
or, for credit cards, store the 3 parts of the number in different cells – Sir Adelaide Mar 03 '17 at 00:01
@maxspan So drop Excel and use something else if you must store a number with more than 15 digits – Ramhound Mar 03 '17 at 00:04
2

@maxspan Storing CC numbers in an excel spreadsheet is likely to be against the data protection rules in a lot of places ... – DavidPostill Mar 03 '17 at 10:32

score 1 · Answer 3 · answered Mar 03 '17 at 05:11

Excel seeing you number as a number, just enclose this long number in double quotes (like:"1234567812345678") and it would be treated as a text, so you can keep there even longer numbers for credit cards that may be will appear in a future.

Example:

name,card_number,expiration_date
John,"1234567812345678","2017-03-03"

Another way to get Excel tricked is to represent card number separated with dashes as 1234-5678-1234-5678 that will be treated by Excel as text too

Getting trailing zero in csv file for 15 digit number

3 Answers3