Importing Large Numbers in Access Table

manicmighty

New Member
Joined
Apr 18, 2018
Messages
15
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?
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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).
 
Upvote 0
In your Access table, make the large number field a Number datatype, then Double in the FieldSize.
largeNumberInTable.PNG
 
Upvote 0
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
 
Upvote 0
You didn't answer the question I asked in the first reply.
 
Upvote 0
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.
 
Upvote 0
Must be with the importing set up as Joe4 suggests. Here is an example Access table

tblDemoNumber

idlongnumber

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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top