Results 1 to 2 of 2
  1. #1
    wardw is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    May 2013
    Location
    Hines, Oregon
    Posts
    41

    Import from Excel file fails because of date formats

    I've solved this myself, but in case anyone else has this problem: I was trying to import an Excel worksheet as an Access table. Several of the worksheet columns were formatted as dates, but the import failed because of many type conversion errors in two of the date columns. I tried changing the date formats in Excel, but none of my changes seem to fix the problem. I exported the entire Excel file as a tab-delimited text file and imported that into Access, specifying Date/Time format for each of the date fields, but the same errors occurred.

    I finally opened the Access table in Design view and check the formats of the fields I wanted to be dates. Their Data Type was Date/Time all right, but under Field Properties I hadn't specified anything for Format. I specified Short Date (6/19/2007), which is what I wanted anyway, and tried the import again. This time the import was successful, with no errors.



    Oddly, when I rechecked the date field's formats in Access after the import, I found they had reverted to blanks in the Field Properties' Format field. Oh, well. . .

  2. #2
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    152
    The records in Excel is not consistant format hence you are getting Error while importing and due to in consistancy the Field Properties' Format field become blank.
    First Use Format cell command in Excel to convert all the Date to desired format eg mm/dd/yyyy than change Field Properties' Format field to mm/dd/yyyy
    than re import

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

Similar Threads

  1. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  2. Import Excel file based on a date and time in file name
    By nhylan in forum Import/Export Data
    Replies: 4
    Last Post: 04-16-2013, 03:26 PM
  3. Import Data from Excel File
    By zwieback89 in forum Import/Export Data
    Replies: 1
    Last Post: 06-27-2012, 08:44 AM
  4. Import Excel and date formats
    By thart21 in forum Import/Export Data
    Replies: 1
    Last Post: 04-19-2011, 03:49 PM
  5. Replies: 2
    Last Post: 08-17-2010, 01:10 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
  •  
Tech Forums: Microsoft Office Forums