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?
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,295
Office Version
  1. 365
In your Access table, make the large number field a Number datatype, then Double in the FieldSize.
largeNumberInTable.PNG
 

manicmighty

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

manicmighty

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

Well-known Member
Joined
Oct 19, 2006
Messages
1,295
Office Version
  1. 365

ADVERTISEMENT

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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 19, 2006
Messages
1,295
Office Version
  1. 365
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,794
Messages
5,574,329
Members
412,587
Latest member
Krucial155
Top