Results 1 to 8 of 8
  1. #1
    The Wonderer is offline Novice
    Windows 11 Access 2016
    Join Date
    Feb 2023
    Posts
    4

    Question How to import data to update a table

    Hi, I am just getting started with Access and struggling!



    My fundamental problem is that I can not work out how to import data from a CSV file (without a primary key) to a Table (with a primary key) and update fields where there has been a change and create new rows where needed.

    I am trying to create an index of documents and files on a hard drive. I have created a baseline Index Table and use the Primary Key of this Table as the file reference in other Tables of the database.

    The majority of these files will not change. However, overtime some will be moved, modified or deleted. New files will also be added.

    The raw data of any update to the Table Index is captured in a CSV file generated by Powershell. However, there is not a unique identifier that is consistent from one update to the next. (e.g. There maybe duplicate filenames in different folders)

    How can I update any changed fields in the Index Table whilst preserving the primary key for unchanged and modified files and append any new files?

    I have tried creating a "merged" table of current & update. I can then identify duplicates and new, but am left with struggling to update the modified files and identifying the deleted files.

    I am hoping that there is a way of updating fields in the Index Table based upon multiple field criteria in the imported update table (i.e. if "Field1, Field 3 and Field x" are the same in both Tables THEN "update row" in Index Table).

    My problem is that I can't work out how to define such a query (if it is possible?).

    Easy steps please :

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Need some common value(s) that provide a unique record identifier. Yes, multiple fields can serve as that identifier.
    Once you have that, can possibly do what is called an UPSERT. Review https://stackoverflow.com/questions/...g-in-ms-access

    Deleting records is another action. Do you really want to eliminate data or just flag record as "Archived"?
    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
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Can you please provide some more info?
    - What is the primary key in your table?
    - What fields are there in the CSV-file?
    Groeten,

    Peter

  4. #4
    The Wonderer is offline Novice
    Windows 11 Access 2016
    Join Date
    Feb 2023
    Posts
    4
    Thanks for having a look.

    Happy to overwrite Fields as current data is of interest. Would be interested in keeping the record of deleted files.

    So the primary key in the Index Table is the Access default so 1,2,3......N.

    The Index Table consist of:

    Primary Key
    Filename Short text - 255 Char - The same filename may appear more than once - never changes
    File Size Double Number - not unique - May change if file modified
    Date Created Date - not unique - Forever fixed with Filename
    Date Modified Date - not unique - may change if file modified
    Directory Short Text - 255 Char - Not unique - would change if File is moved. "Filename + Directory" would be a unique identifier. Is that possible? Would it not be a Long text?

    In the CSV file you have the same fields but no Primary Key. There is no guarantee that the file list would be in the same order. That is the challenge in the importing!

    Thanks

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Does combination of those 5 fields provide a unique identifier for 1-to-1 link of datasets?

    Record order should be irrelevant.
    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.

  6. #6
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    On the basis of this data, this seems to me an impossible task. There is no combination of fields in the CSV file that is unique.
    Only if Date Created were a date plus time would it work. The chance that two files with the same name were created at the same time is not that great (although the chance is not zero). The chance that two files with the same name were created on the same day is already much higher, so that combination is not usable.
    Groeten,

    Peter

  7. #7
    The Wonderer is offline Novice
    Windows 11 Access 2016
    Join Date
    Feb 2023
    Posts
    4
    I think that is the problem. Since "size", "modified date" and "Directory" could change between updates there can not be a direct unique identifier between the two datasets.

    a. "Filename" & "Directory" would identify any unmoved files (unchanged or modified) - majority of cases
    b. "Filename" & "size" would identify any unchanged files (moved or not) - most of the remaining
    c. "Filename" & "Date Created" would find remaining modified & moved files
    d. Records not matched in Index Table would be files that have been deleted
    e. Records not matched in the Update would be New files

    I want to identify matches identified in (a) and update appropriate fields in Index Table. Then of the remaining records use (b), then (c) etc.

    Still dont see how it could work

  8. #8
    The Wonderer is offline Novice
    Windows 11 Access 2016
    Join Date
    Feb 2023
    Posts
    4
    Quote Originally Posted by xps35 View Post
    On the basis of this data, this seems to me an impossible task. There is no combination of fields in the CSV file that is unique.
    Only if Date Created were a date plus time would it work. The chance that two files with the same name were created at the same time is not that great (although the chance is not zero). The chance that two files with the same name were created on the same day is already much higher, so that combination is not usable.
    I think I agree. It is worst than you think. If you consider a common Template file then it could be repeated in many directories!!!

    I am more expert in excel but the index is getting too big and I was hoping to move to access - fear it is not possible.

    I can't believe it is a problem that has not been seen before - computer file indexing!!

    All thanks for have a look

    Cheers

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

Similar Threads

  1. Import from Excel to update table
    By Pillic in forum Import/Export Data
    Replies: 3
    Last Post: 01-26-2021, 01:42 AM
  2. Replies: 5
    Last Post: 04-26-2016, 05:30 AM
  3. Replies: 13
    Last Post: 01-25-2016, 01:36 PM
  4. Replies: 17
    Last Post: 10-27-2014, 04:15 AM
  5. Replies: 8
    Last Post: 10-13-2014, 12:19 PM

Tags for this Thread

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