Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35

    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,463
    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 Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    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
    16,716
    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,463
    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
    16,716
    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:	43 
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 Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    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:	43 
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
    16,716

  9. #9
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi Orange, this has been working great when I do it manually. I am importing updated data a couple of times a week so I would like to automate it if possible. I've worked out how to automate the import part but I'm struggling with how to add the new field and run the SQL. Could you point me in the right direction?

    Thanks

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    How exactly are you importing the csv file(s)? (Details please)
    Can you post:
    -a small set of csv data
    -a copy of your database with no personal info. Just enough info to see your table structure.

    In general, I would use 2 steps.
    1-import the data as text strings
    2-run an update query to convert the Date info to a Date using CDate(the string data)

  11. #11
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi Orange,


    I have a few csv files that are updated regularly and I have been reimporting those in full which overwrites the existing tables. No real reason for doing it this way other than finding a Youtube video that suggested doing it this way.


    When I did the first import I saved the steps and was planning to add these to a macro to make reimporting quicker.


    At present I import 5 csv files into 5 tables (BallByBall, Matches, ScorecardBatting, ScorecardBowling and Lineups), run the SQL on the dates then run some queries to combine fields into different tables. I have deleted some data but have tried without luck to attach a copy. Ive uploaded it here instead


    https://drive.google.com/file/d/1VgK...ew?usp=sharing


    Also attached is an extract of the csv file that has the date issue.





    Quote Originally Posted by orange View Post
    How exactly are you importing the csv file(s)? (Details please)
    Can you post:
    -a small set of csv data
    -a copy of your database with no personal info. Just enough info to see your table structure.

    In general, I would use 2 steps.
    1-import the data as text strings
    2-run an update query to convert the Date info to a Date using CDate(the string data)
    Attached Files Attached Files

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

    I'm looking at your files. Loaded the accdb, and copied the csv to a local drive.
    I made a copy of your Matches table as W. Then deleted all records in W.
    Then, looked at your csv data in Notepad++, I see some double quotes. I imported the csv data to W and said no delimiters
    When I check the contents of W, I see things are garbled.
    SO. I then deleted W, did a reimport indicating comma separate and quote delimited, imported to W, -no import errors.Then ran the Update query
    Code:
    UPDATE w SET w.ConvDate = CDate(Date); and all looks well.
    What exactly would you like from my end?

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Rob,
    I tried returning your zip file in which I have added my copy of your database. Mine is called RobSmithCopyJ.accdb. It had all the materials below. Table W, basMod etc. BUT CAN NOT GET ANY ZIP FILES TO ATTACH ON THIS SITE (not getting any error, just doesn't attach)

    I have attached it here <<<<<<<<<<<<<<

    I have created an automated routine in module basMod to do the import of the matches csv you sent, then do the convert of the Date.
    The routine is dependent on an Import Specification called "OrangeImportSpecification" and uses my csv file name and is imported into table W. You can change the file name and the table name to suit your needs.
    I created the Import Specification during the import and reference it in the routine.

    This code was used to test the import, convert the date 10 times.
    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: testit
    ' Purpose: Routine to exercise the import matches  csv, convert the Date 10 times
    ' Procedure Kind: Sub
    ' Procedure Access: Public
    ' Author: Jack
    ' Date: 01-Mar-20
    ' ----------------------------------------------------------------
    Sub testit()
    Debug.Print "Starting a session of 10 import/deletes"
        Dim i As Integer
        i = 10
        For i = 1 To 10
            ImportMatches
        Next i
    Debug.Print "Finished the session of 10 import/deletes"
    End Sub
    Below is the code that Deletes the contents of table W, then does the import and Convert Date.
    You can remove or comment these lines from the routine.
    Debug.Print "Deleting current contents of table W @ " & Now
    CurrentDb.Execute "Delete * from W;", dbFailOnError
    Here is the ROUTINE ImportMatches

    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: ImportMatches
    ' Purpose: Routine to empty table W, then import to table W, then convert the Date.
    ' Procedure Kind: Sub
    ' Procedure Access: Public
    ' Author: Jack
    ' Date: 01-Mar-20
    ' ----------------------------------------------------------------
    Sub ImportMatches()
        On Error GoTo ImportMatches_Error
    Debug.Print "Deleting current contents of table W @ " & Now  'can be removed
    CurrentDb.Execute "Delete * from W;", dbFailOnError              'can be removed
    DoEvents  'a short break to the OS
    Debug.Print "Starting to process the Matches csv file @ " & Now
    DoCmd.TransferText acImportDelim, "OrangeImportSpecification", "w", "c:\users\jack\documents\Matches For Orange.csv", True
    Debug.Print "Finished importing the csv"
    DoEvents 'a short break to the OS
    CurrentDb.Execute "UPDATE w SET w.ConvDate = CDate(Date)", dbFailOnError
    Debug.Print "Finished updating the Date on table W"
    Debug.Print "All done @ " & Now
     
        On Error GoTo 0
    ImportMatches_Exit:
        Exit Sub
    
    ImportMatches_Error:
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportMatches, line " & Erl & "."
        GoTo ImportMatches_Exit
    End Sub
    Here is the output from session to exercise the ImportMatches routine 10 times.

    Code:
    Starting a session of 10 import/deletes
    Deleting current contents of table W @ 01-Mar-20 6:33:08 PM
    Starting to process the Matches csv file @ 01-Mar-20 6:33:08 PM
    Finished importing the csv
    Finished updating the Date on table W
    All done @ 01-Mar-20 6:33:09 PM
    Deleting current contents of table W @ 01-Mar-20 6:33:09 PM
    Starting to process the Matches csv file @ 01-Mar-20 6:33:09 PM
    Finished importing the csv
    Finished updating the Date on table W
    All done @ 01-Mar-20 6:33:10 PM
    Deleting current contents of table W @ 01-Mar-20 6:33:10 PM
    Starting to process the Matches csv file @ 01-Mar-20 6:33:10 PM
    Finished importing the csv
    Finished updating the Date on table W
    All done @ 01-Mar-20 6:33:11 PM
    Deleting current contents of table W @ 01-Mar-20 6:33:11 PM
    Starting to process the Matches csv file @ 01-Mar-20 6:33:11 PM
    Finished importing the csv
    Finished updating the Date on table W
    All done @ 01-Mar-20 6:33:12 PM
    Deleting current contents of table W @ 01-Mar-20 6:33:12 PM
    Starting to process the Matches csv file @ 01-Mar-20 6:33:12 PM
    Finished importing the csv
    Finished updating the Date on table W
    All done @ 01-Mar-20 6:33:13 PM
    Deleting current contents of table W @ 01-Mar-20 6:33:13 PM
    Starting to process the Matches csv file @ 01-Mar-20 6:33:13 PM
    Finished importing the csv
    Finished updating the Date on table W
    All done @ 01-Mar-20 6:33:14 PM
    Deleting current contents of table W @ 01-Mar-20 6:33:14 PM
    Starting to process the Matches csv file @ 01-Mar-20 6:33:14 PM
    Finished importing the csv
    Finished updating the Date on table W
    All done @ 01-Mar-20 6:33:15 PM
    Deleting current contents of table W @ 01-Mar-20 6:33:15 PM
    Starting to process the Matches csv file @ 01-Mar-20 6:33:15 PM
    Finished importing the csv
    Finished updating the Date on table W
    All done @ 01-Mar-20 6:33:15 PM
    Deleting current contents of table W @ 01-Mar-20 6:33:15 PM
    Starting to process the Matches csv file @ 01-Mar-20 6:33:15 PM
    Finished importing the csv
    Finished updating the Date on table W
    All done @ 01-Mar-20 6:33:16 PM
    Deleting current contents of table W @ 01-Mar-20 6:33:16 PM
    Starting to process the Matches csv file @ 01-Mar-20 6:33:16 PM
    Finished importing the csv
    Finished updating the Date on table W
    All done @ 01-Mar-20 6:33:17 PM
    Finished the session of 10 import/deletes
    Good luck.

  14. #14
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi Orange, thanks very much for this.

    I have amended the code as follows:

    Code:
    Sub ImportMatches()    On Error GoTo ImportMatches_Error
    DoEvents  'a short break to the OS
    Debug.Print "Starting to process the Matches csv file @ " & Now
    DoCmd.TransferText acImportDelim, "Matches Import Specification", "Matches", "C:\Users\Rob\Documents\Database Test\Matches.csv", True
    Debug.Print "Finished importing the csv"
    DoEvents 'a short break to the OS
    CurrentDb.Execute "UPDATE Matches SET Matches.ConvDate = CDate(Date)", dbFailOnError
    Debug.Print "Finished updating the Date on table Matches"
    Debug.Print "All done @ " & Now
     
        On Error GoTo 0
    ImportMatches_Exit:
        Exit Sub
    
    
    ImportMatches_Error:
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ImportMatches, line " & Erl & "."
        GoTo ImportMatches_Exit
    End Sub
    But get the 3061 error shown in the attached.

    I've double checked the name of the import specification, the table and the file path and all are ok.

    Can you see what I've done wrong?

    Thanks
    Click image for larger version. 

Name:	Untitled.jpg 
Views:	21 
Size:	45.7 KB 
ID:	41128

  15. #15
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What is Matches Import Specification ?

    Did you test the procedure using the database I returned to you? Did it work OK?
    If your csv and Table design are the same as you sent me, then I would expect "OrangeImportSpecification" to work for you. The specification is the only thing I see different.
    You could adjust the first line of the ImportMatches to put the OnError part on a separate line (I don't think it's the culprit), but I don't see anything obvious.

    Make sure the database I sent back to you works as it did for me.

    3061 is usually a missing or misspelled parameter.

    UPDATE: Another thought did occur. I used the Advanced option when creating the import spec. This causes it to be stored in
    MsysIMEXSpecs and the details in MSysIMEXColumns. I don't think that is an issue necessarily,but if you don't use the Advanced option then the Specification is stored in xml in the internals of the system tables. Can't see it being the problem, but it is possible.

    Good luck.

Page 1 of 3 123 LastLast
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
  •  
Other Forums: Microsoft Office Forums