Results 1 to 5 of 5
  1. #1
    OldenMcdonald is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2017
    Posts
    6

    Exclamation Import Excel to access creates Scientific notation numbers

    Import Excel 2010 to access 2010 creates Scientific notation numbers


    I had imported the data before and did not have this problem.


    I have 2 excel files I am importing to Access to become tables.

    In Excel, cell format for the column set to custom #################### (19 # symbols)
    In Excel all the long numbers show as normal.
    If I change format to text, the numbers change to scientific notation UNTIL I click on EVERY cell in the column displaying incorrectly, So I leave it at custom (####...) so they show correctly

    I have even added a 2nd of of text at the top row( I copied the top row and pasted it under itself. )

    In Access, when importing , the default classification is Text so I leave it there. I did try long integer, but it did not work for my data which is a mix of numbers and text.

    after importing, all fields in access show as text
    But it changes all the long numbers to scientific notation.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I tested import with workbook downloaded from your other thread and cannot replicate the issue. Used import wizard. All SKUs are text, no scientific notation
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    importing only imports the underlying data, it does not import any formatting properties that have been set.

    If I change format to text, the numbers change to scientific notation
    In this case your underlying value is still a number - try putting a single quote before the value to ensue it is text.

    Or if it needs to stay a number in Access, ensure field is set to double (long's only handle up to 12 chars, doubles 19) and set the format property to # (1 # char)

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Access field has to be text because some of the SKUs have alpha characters.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    OldenMcdonald is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2017
    Posts
    6
    Turns out I was doing it properly, since I had already done it properly before.
    But office must have had memory leak or one small subroutine in it got temporally messed up

    I decided to export the data to csv file. It imported properly.
    So I went back to import the excel file. that failed stating resource issue. System memory and disk space not even close to cap. like at 1/4

    So...
    I shut everything down and rebooted,
    Now it worked doing the exact same thing I did prior that kept malfunctioning.


    Thanks June7 and Ajax for your responces

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-28-2017, 01:34 AM
  2. Replies: 1
    Last Post: 09-15-2015, 02:16 PM
  3. Replies: 1
    Last Post: 09-29-2014, 03:35 PM
  4. Converting Scientific notation in a Query
    By wuhsean22 in forum Queries
    Replies: 2
    Last Post: 02-06-2013, 05:30 PM
  5. Replies: 1
    Last Post: 11-29-2011, 08:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums