Results 1 to 7 of 7
  1. #1
    Susy is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    134

    import csv with weird date

    Hi there,

    I am trying to import a csv into a table in Access.

    The problem is that one field has the following format: yyyy-mm-yy hh:mm:ss.0

    The error I get in the "error" table created by Access is: Type Conversion Failure.

    I tried to upload this field as text, but it did not work.



    Any Ideas?

  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,825
    Did you mean yyyy-mm-dd hh:mm:ss.0?

    I did a test importing that structure as text and had no problem. Provide sample of your data.
    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
    Susy is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    134
    Hi June, yes, I meant yyyy-mm-dd hh:mm:ss.0 like 2017-01-31 10:35:15.0 --> Date/Time Data Type...

    csv Tab Delimited

    File Format: Delimited
    Field Delimiter: , (Comma)
    Text Qualifier: "

    Language: Spanish
    Code Page: Unicode (UTF-8)

    Date Order: YMD
    Date Delimiter: -
    Time Delimiter: :
    Four Digit Years: Checked
    Decimal Symbol: . (Point)


    How did you do it? Of course the data type of this field in the table should be Date/Time.... but how did you manage to input this type of date 2017-01-31 10:35:15.0? When I do it I get an error message " The value you entered does not match the Date/Time data type in this column."

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Import the field into a temporary table as a text field. Then convert it to a date before adding it to the main table. CDate(textfield) will convert it to a date/time data type, remove the rightmost 2 characters.

    IIf(Not IsNull([datetext]),CDate(Left([datetext],19)))

  5. #5
    Susy is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    134
    Thanks alot, I did that already... import as text in a temp table, and then append it to another table with date format using mostly bad syntax:
    Code:
    Trim(Left([wierdDate];Nz(Len([wierdDate])-2)))
    But it worked!

    I will now check your formula. Thanks again aytee111

    Have a nice weekend you all!

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No, it was a text type field, not date/time. Text cannot be inserted into date/time field because it is really a number type of field. I assumed because you said you were importing as text that your destination field was also text type. I am confused about what the original issue was if you were already attempting temp table solution and a conversion function. No matter now, as long as you have a solution.
    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.

  7. #7
    Susy is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    134
    Thanks June7 and sorry for the misunderstanding.

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

Similar Threads

  1. Weird excel import error
    By Ndain in forum Import/Export Data
    Replies: 4
    Last Post: 02-10-2017, 04:08 PM
  2. Weird Date Picker Query Problem
    By Tim777 in forum Queries
    Replies: 11
    Last Post: 03-30-2012, 04:18 PM
  3. Replies: 2
    Last Post: 03-27-2012, 01:59 AM
  4. import date
    By slimjen in forum Import/Export Data
    Replies: 14
    Last Post: 01-26-2012, 08:07 PM
  5. Date Paramater prints weird
    By NOTLguy in forum Reports
    Replies: 9
    Last Post: 10-20-2010, 07:58 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