Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2018
    Posts
    4

    Data Type Error when Importing .xlsx or .csv (Dates)

    Hello,



    I am trying to import a table with dates into Access. Whenever I try to import, Access automatically reads my dates as Short Text instead of a number or a Date with Time. If I try to change the data type (either while importing or after), I end up with a ton of Data Type errors (I'm assuming because a string takes more space than an integer). The original file is a .csv file, but there's no particular reason that it needs to stay that way.

    Honestly, at this point, I don't care if it gets imported looking short date or a date value. I think I can change it into something "date-looking" after I get it imported, I just really need to get Access to import anything other than text.

    Here are things I've tried:
    • Changing the type to short date, number, and general while in Excel
    • Importing as .xlsx and as .csv
    • Changing the data type after the fact
    • Changing the data type after importing
    • Getting rid of my Column Names (so that there are no letters to pick up)


    Someone I work with suggested using a macro, but I don't know how to use VBA, and while I'm willing to learn, I'm not sure I can afford to put in the time right now unless there's some assurance it would work (I know--I'm the worst).

    Does anyone have an idea? You'd be my hero.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    CSV files are text but even so Access tries to intelligently 'guess' the datatype unless you specify a specific datatype to be used.

    If you import using an import specification, you SHOULD be able to solve this
    But I've found that can fail as well

    The way I deal with this is as follows:
    Import to a buffer table - all fields can be text if it helps

    Then use a procedure to append from the buffer table to the final table where you use CDate, CLng, Cdbl, CBool etc for each field as appropriate if there is any possibility of confusion by Access.
    That WILL require you to use VBA
    Whilst it takes a little longer to setup, it is guaranteed to work EVERY time
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Join Date
    Apr 2018
    Posts
    4
    Thank you so much for your answer, ridders52. If you think it will work, then I will try going the VBA route. I guess the only thing I'm wondering is how this will be different from changing the Data Type using Table Tools>Fields>Data Type (which hasn't been working)? Please forgive me if this is super basic stuff--I haven't started working with Access until pretty recently.

    Thank you for the advice!

  5. #5
    Join Date
    Apr 2018
    Posts
    4
    I actually figured it out (I should have realized this sooner): The report I was using had "-" entered for null values. Once I got rid of them, it worked--hooray!

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

Similar Threads

  1. Replies: 4
    Last Post: 12-08-2017, 11:30 AM
  2. Replies: 5
    Last Post: 10-28-2016, 08:03 AM
  3. While Importing Change in data type
    By drunkenneo in forum Import/Export Data
    Replies: 1
    Last Post: 09-01-2014, 06:49 AM
  4. Problem with data type when importing CSV via VBA
    By raynman1972 in forum Import/Export Data
    Replies: 1
    Last Post: 04-30-2013, 11:40 AM
  5. Type error when importing Excel spreadsheet
    By jlmnjem in forum Database Design
    Replies: 2
    Last Post: 08-27-2006, 07:26 PM

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