Importing Large Numbers in Access Table

[manicmighty]

I have a file which has 15 digit numbers in one of the columns.

When I have imported into my database with the field set as 'Short Text' the numbers for any data imported all show as 4E+14 and then all subsequently become '400000000000000'.

I have changed the field to be Large Number and when importing the number is then displayed as '400000000000000'.

The problem being that all the numbers should not be zero as the last 6 numbers are not zero and it should be showing like '400000000411577'

Is there a way to format the field in the table so that numbers at the end are not changed to zero when importing?

 

[Joe4]

How exactly are you importing these values?
If you import it as a Text field instead of a numeric one, all characters should be kept (scientific notation denotes that it is importing as a number, not as text).

 

[jackd]

In your Access table, make the large number field a Number datatype, then Double in the FieldSize.

 

[manicmighty]

Unfortunately the setting the field to 'Number' and Field Size to 'Souble' encounters the same issue where the last six digits have all been changed to zero

 

[Joe4]

You didn't answer the question I asked in the first reply.

 

[manicmighty]

You didn't answer the question I asked in the first reply.

Apologies Joe4 - The data is exported as a CSV file so I would assume in the first instance that the field is general (so number by default) I am using the import tool on the database; when I manually copy and paste from the CSV it pastes in correctly only when I use the saved import it updates the last 6 digits.

 

[jackd]

Must be with the importing set up as Joe4 suggests. Here is an example Access table

tblDemoNumber

id	longnumber

1​	123456789012345​
2​	140000000012347​
3​	123000000000000​

I just added the record with ID 4 by entering the value in Notepad++ then copy and paste into my Access table.

id longnumber
1 123456789012345
2 140000000012347
3 123000000000000
4 101010101010101

 

[Joe4]

Apologies Joe4 - The data is exported as a CSV file so I would assume in the first instance that the field is general (so number by default) I am using the import tool on the database; when I manually copy and paste from the CSV it pastes in correctly only when I use the saved import it updates the last 6 digits.

When you import the CSV (choosing the Text File option), it should invoke the Import Wizard, right?
When you get to the third step, it asks for the Data Type of each field. So you want to click on that field, and change the Data Type from "Long Integer" to "Text".
Then it will import and keep all the characters.

 

[jackd]

I added 2 more records to the table. I used the Import wizard and imported these from Long.txt, but I had to drop the autonumber field. The import was trying to put the number into the ID field?? I imported as fixed width -didn't try anything else.
longnumber
123456789012345
140000000012347
123000000000000
101010101010101
101010101010333
232323232323237