Results 1 to 6 of 6
  1. #1
    mommyof4kids is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    20

    Importing data from Excel, does not import all data

    I have an excel spreadsheet that I'm importing into Access. Some of the fields do not import all of the data. For instance, each record has a date field, however on the imported data it only imports about 1/2 of the dates. Importing from Excel 2010 to Access 2007

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    which method are you using to import data?

    Access has very strong datatyping whereas Excel is very weak (or more flexible if your prefer!) So check your date cells in Excel, they may not be proper dates so will be ignored on import

  3. #3
    mommyof4kids is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    20
    I have tried importing the data to a table and I've also tried creating the table and copying the data. In Excel the format is set to short date and in Access it is set to short date...perhaps my problem is where the data in Excel is copied from. I will double check those that are not importing and see if that is the case.

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I have experienced this Excel to Access import frustration many times.
    Access tries to "guess" the format of the data by looking at the first 8-10 rows of data. Sometimes it guesses wrong (like if you had a column where the first 10 rows look like dates, but then row 11 has some text in that column).

    What other fields are you importing? It is possible that the issue is with another field (column).

    What I end up doing many times is exporting my Excel file to a text file, and then import the text file into Access where I can control the format and data type of each field (set through the Import Wizard). I know exactly what the structure of each field (column) should be, and I can control it that way.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Along with what others said, change the Date field in your Access table to Text and see if they all import. That would tell you if the data is a problem in that excel field.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I have tried importing the data to a table
    by using the import manager? vba and transferspreadsheet? linking and appending?

    JoeM makes some good points, I also convert to .csv before importing to resolve a lot of issues.

    Excel the format is set to short date and in Access it is set to short date...
    formats are properties of a cell in excel or a field in Access (dates are actually doubles). Properties are not imported. What matters is the 'perceived' datatype. So also look at the specification in the import manager to ensure you are specifying there what the datatype is.

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

Similar Threads

  1. Replies: 2
    Last Post: 06-25-2015, 03:56 PM
  2. Replies: 3
    Last Post: 01-05-2015, 02:21 PM
  3. Formstack & Importing Data via excel, best way to collect data?
    By Yogibear in forum Import/Export Data
    Replies: 2
    Last Post: 02-10-2014, 07:05 PM
  4. Replies: 7
    Last Post: 04-22-2013, 08:01 AM
  5. Importing data from Excel
    By dsaxena15 in forum Access
    Replies: 1
    Last Post: 10-03-2012, 10:56 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