Results 1 to 8 of 8
  1. #1
    robsmith is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    5

    Importing csv file with two different date formats

    Hi guys,

    Forgive me... I'm an Access novice and have searched around but can't find an answer to this.

    I am trying to import a csv file that has two different date formats in the same column: 29-Dec-19 and 7 January 2020. Because of this Access gives a Type Conversion Failure Error when I import.



    How do I deal with this?

    Thanks

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,317
    Make the import field type Text instead of Date ? Or is there any way in excel to modify that column to make them all the same date formats?

  3. #3
    robsmith is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    5
    Hi Bulzie, thanks. Would importing as text still allow me to run date-based queries?

  4. #4
    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
    14,592
    Once you have imported the file, you could use CDate(the date field) to get a Date. You may have to add a field called MyDate 9or similar) and update it with Cdate(the date field) for speed/ease of use. Use the new field in your queries. Try it and let us know.

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,317
    Not sure, was thinking if you were just displaying the date on form or report that might work. Once in an access table maybe see if it will let you convert to date format, look at functions DateValue or CDate.

  6. #6
    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
    14,592
    I did a small test.

    MyData all fields imported as short text
    Code:
    Customer, OrderDate
    Smith, 7 October 2019
    Jones, 29-Sep-2018
    Thomas, 17-Jul-2017
    Then added myDate as Date datatype
    Click image for larger version. 

Name:	TableDesign11.PNG 
Views:	16 
Size:	5.3 KB 
ID:	40852

    I tried using Query designer to update MyDate, but it insisted on putting quotes around OrderDate.

    So, I went to SQL view and wrote the SQL

    Code:
    UPDATE Testdate SET Testdate.MyDate = CDate(OrderDate);
    And it worked as expected.

    Result:

    ID Customer OrderDate MyDate
    1 Smith 7 October 2019 07-Oct-19
    2 Jones 29-Sep-2018 29-Sep-18
    3 Thomas 17-Jul-2017 17-Jul-17

    Note in my regional settings " 17-Jul-17 DD-MMM-YY is my default."

  7. #7
    robsmith is offline Novice
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    5
    This works perfectly. Thank you!

    Quote Originally Posted by orange View Post
    I did a small test.

    MyData all fields imported as short text
    Code:
    Customer, OrderDate
    Smith, 7 October 2019
    Jones, 29-Sep-2018
    Thomas, 17-Jul-2017
    Then added myDate as Date datatype
    Click image for larger version. 

Name:	TableDesign11.PNG 
Views:	16 
Size:	5.3 KB 
ID:	40852

    I tried using Query designer to update MyDate, but it insisted on putting quotes around OrderDate.

    So, I went to SQL view and wrote the SQL

    Code:
    UPDATE Testdate SET Testdate.MyDate = CDate(OrderDate);
    And it worked as expected.

    Result:

    ID Customer OrderDate MyDate
    1 Smith 7 October 2019 07-Oct-19
    2 Jones 29-Sep-2018 29-Sep-18
    3 Thomas 17-Jul-2017 17-Jul-17

    Note in my regional settings " 17-Jul-17 DD-MMM-YY is my default."

  8. #8
    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
    14,592

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

Similar Threads

  1. Replies: 2
    Last Post: 03-09-2016, 04:15 PM
  2. Replies: 5
    Last Post: 02-24-2016, 04:32 PM
  3. Replies: 7
    Last Post: 04-18-2014, 05:26 PM
  4. Import from Excel file fails because of date formats
    By wardw in forum Import/Export Data
    Replies: 1
    Last Post: 01-01-2014, 02:20 AM
  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