Query more than 15 digits

[Jorgeoshoa8]

I want to web query an online database. One of the columns contains numbers 16 digits long. When Excel imports the query I get the number in exponential format and it replaces the last number with a zero.

Example: The original is 1002002660100027, after query ends up as 1.002E+15 and reformated as text I get 1002002660100020 (Last # changed to a "0")

Is there a way to prevent the query from replacing the last number with a zero when importing from a query? Or.. can I get the query to paste as text?

 

[Smitty]

Welcome to the Board!

You can try pre-formatting that column as text.

How are you running the query though, as that might also have some options?

 

[Jorgeoshoa8]

Already tried pre-formatting as text and when imported it appears in exponential form, everything I do after that results in the las # replaced by zero.

Also tried query options and pasted as rich text format and html, checked preserve cell formatting and got the same results.

 

[Roli001]

The problem is that Excel is treating the the column with the 16 digits as a number and Excel is limited to 15 digits. Any digits above 15 are cut down to 0's by Excel and that's final. You can check this by simply typing in a non text formated cell 12345678901234567890. When you look at that cell again Excel has changed the number to 12345678901234500000.

Tip: If a number has 15 digits or less you can convert it from scientific notion to full notion by using the UPPER function i.e. =UPPER(A1)

So having said so there is little that can be done once Excel has gotten it's hands on a +16 digit number.

There are however several solutions:

1. When writing the query i.e. in biQuery or Access make sure that you format the 16 digit column as text and import the file using Text Import Wizard.

or

2. If you already have a csv file rename it to txt i.e. myfile.csv to myfile.txt and then import it using the Text Import Wizard formatting the column with the 16 digits to text.

or

3. Use OPEN filename FOR INPUT AS ## in VBA.

Hope this helps.