Page 3 of 3 FirstFirst 123
Results 31 to 37 of 37
  1. #31
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Rob,
    Happy to help. But I have some questions.
    You said "I have downloaded, unzipped and run it without changing anything apart from the file path and name for the full matches csv file."

    Did you run the routine using TableW and your test version of the csv that had 35 records? That test was to ensure the logic was working as expected/required. TableW has the same structure as your Matches. This is really a "proof of concept/logic". Always test on a copy of the data.

    The error 94 with the full version of your csv and Matches could be a bad record(s)/format in the incoming csv file- could be no Date supplied, so conversion would fail.

    If you import the csv file into an empty table of the same structure as "W", you could
    ==run a query to see if all Date values are present and/or flag any that are invalid. Then review any invalids before moving to do the conversion/update.
    ==Another option is to adjust the ImportMatches to check for any invalid/valid entries in Date when doing the conversion.


    Do you still have a copy of the csv file and the Matches before you ran the update?
    Let's find and resolve the source of the error.

    In the table where you import the Matches csv data, you can run this query to identify any records where there is no Date field value.
    Change the "W" to the table in which you import the full csv.
    Go to create query; SQL view; then paste this code; run t and let us know the result.

    Code:
    SELECT 
    ID
    , matchId
    , League
    , Season
    , Date
    , convDate
    , startTime
    FROM w
    WHERE Date Is Null;
    I removed the Date from 1 record(ID 1213991) in the test csv and ran the query.

    Result was



    Code:
          
    ID matchId League Season Date convDate startTime
    1213991 1203676 Ireland tour of West Indies 2019/20 2019/20 13
    Good luck.
    Last edited by orange; 03-05-2020 at 07:23 AM. Reason: spelling

  2. #32
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Hi Orange, I unzipped a fresh version to a different folder, changed the file path and ran it now using the Matches for Orange file. It works fine.

    I then imported the full Matches csv file into a new table and ran the SQL. Initially a box popped up asking for a parameter value for convDate. I guessed this was because the Matches csv didn't have that field yet so I deleted that line from the code and ran it again.

    This was the result:

    Click image for larger version. 

Name:	Untitled.png 
Views:	17 
Size:	21.1 KB 
ID:	41180

    I looked at the new Matches table and saw that for some reason the import has added an additional ID number for a record that does not exist in the csv. See below. Could this be the issue?

    Click image for larger version. 

Name:	Untitled1.jpg 
Views:	16 
Size:	172.4 KB 
ID:	41181

    Thanks

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

    OK so the proof of concept works - good.
    I think the issue is with the creation of the convDate field. Initially, I made a copy of your Matches called "W"; then added the field convDate to that table; then imported the csv file to a table. If I remove the convDate from W, I get the messages you get.
    I'm heading out to golf, but will look at this when I return.
    But I will get back later- probably tonight EDT(Florida time).

    The addition of the field convDate was useful for my testing, but you do not have to add that field to your table.
    I have adjusted the ImportMatches procedure to not use the convDate field.
    I am attaching another zip with the latest changes to the database.

    Here is a list of changes to the routine:
    ' 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
    ' RevisionDate: 05-Mar-20
    ' Revision:
    ' -removed convDate from tableW
    ' -modified the UPDATE SQL to use only the existing Date field
    ' -Date gets updated with CDate(Date)
    ' -modified error handler to deal with 3144 when dealing with empty Date field
    ' -added query QCheckForEmptyDate to identify csv records with empty Date field
    ' -added query QCountRecordsWithEmptyDate to count total records with empty date field
    ' -added a line in Import Matches to print Count of records with Empty Date
    ' -added parameters
    ' ---sTableName for the Table to be updated
    ' ---CSVFile for full path to the csv file to update sTableName
    '

    You should check the procedure by running with the set up in this file. Just modify the location of the csv file.

    Once that is working:

    If you put your info in the parameters in the ImportMatches routine,
    you should be able to run from the Form -just push the button.

    Any records with an empty date field should be displayed in the Navigation/Main Page. The query QCheckForEmptyDate will identify such records and it is executed in the ImportMatches routine.

    Give it a try and let me know status.

    Good luck.
    Attached Files Attached Files
    Last edited by orange; 03-05-2020 at 03:52 PM. Reason: spelling

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

    I have run it as instructed and get this:

    Click image for larger version. 

Name:	Untitled.jpg 
Views:	14 
Size:	72.4 KB 
ID:	41192

    I have also spent several hours looking at the Matches csv file this morning to see if I can spot any obvious issues. It has been bugging me that when importing this file (either manually or using your code) an extra ID has suddenly started to be added. I've imported the file many times without this happening.

    I couldn't see anything obvious but I deleted the first blank row (row 2727) of the file and ran a manual import. This time the import worked ok and no extra ID was added. I then ran the code in your version from 4th March and it worked again without any issues. All rows were imported and all dates converted with no extra ID being added.

    The file has not been changed so I don't know how it could have happened but could Access have been picking up something in that row that caused a problem?

    To triple check, I then got an updated version of the Matches csv (which contains an additional 30 rows) and ran the code again. It worked as intended.

    Fingers crossed things are working now.

    I'll keep you posted and thank you once again for all your help.

  5. #35
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Rob,
    When you have a table that has an autonumber primary key defined-say ID, and you add/insert records to that table, Access will populate the ID column. You don't have to populate an autonumber field.
    So your csv starts with MatchID, in my version of the Matches table there is an ID autonumber(don't remember if I added that or if it was already there),but that is where the ID value is added automatically by Access itself.
    Good luck with your project.

    Every table should have a Primary Key. Many, including me, will use an autonumber PK (not necessarily named ID). Oteen best practice to use a meaningful name for you PK (eg SupplierID, CustomerID..)

    If you do have an ID autonumber PK and you don't want it, you can delete that field when in table design view.
    PK should not have any meaning to you, it is ONLY to provide a unique identifier for every record in the table.
    Last edited by orange; 03-06-2020 at 12:21 PM. Reason: additional info

  6. #36
    robsmith is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2020
    Posts
    35
    Thanks Orange. To square the circle on this, it seems the issues I was having with Compact & Repair were due to the file being in a folder that is backed up to Google Drive. Something to do with the syncing process causes the issue. Once the file has been synced everything compacts ok.

    Thanks again.

    Quote Originally Posted by orange View Post
    Rob,
    When you have a table that has an autonumber primary key defined-say ID, and you add/insert records to that table, Access will populate the ID column. You don't have to populate an autonumber field.
    So your csv starts with MatchID, in my version of the Matches table there is an ID autonumber(don't remember if I added that or if it was already there),but that is where the ID value is added automatically by Access itself.
    Good luck with your project.

    Every table should have a Primary Key. Many, including me, will use an autonumber PK (not necessarily named ID). Oteen best practice to use a meaningful name for you PK (eg SupplierID, CustomerID..)

    If you do have an ID autonumber PK and you don't want it, you can delete that field when in table design view.
    PK should not have any meaning to you, it is ONLY to provide a unique identifier for every record in the table.

  7. #37
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good perseverance. Glad you have it resolved.

Page 3 of 3 FirstFirst 123
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