Results 1 to 10 of 10
  1. #1
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39

    Create new field that is combination of two fields in csv file when importing by appending

    I have a CSV file that contains all the purchases for a budget period that is updated regularly. I'd like to get this into my database by importing the CSV file and appending the records so that there are no duplicates. The problem is there isn't a unique identifier in the CSV file, however, there are two fields that if combined would be unique. Is it possible to create a new field when importing based on two fields in the CSV to use as the primary key?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    save the CSV to the same place everytime: c:\temp\ImportCsv.csv
    then link it as an external table. (done only once)
    create an append query to do the import. set your calculated field that will be the key: [field1] & [field2]
    set the query property to UNIQUE VALUES =true ,so you don't import duplicates.

    then you just:
    1. save new csv to c:\temp\ImportCsv.csv
    2. run import query
    done.

  3. #3
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Thanks ranman. I'm still learning Access so forgive the naive question, but am I creating this append query to add to the linked table or to a new table? I'm thinking the latter because I don't think I can create a new field in a linked table.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    to add you your data table. (not the linked source csv)
    Is the target table also linked? if so, then the calculated 'key' does not need to be added, its there just to prevent duplicates.

  5. #5
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    No it's not linked. So I thought I had it right but it is appending 0 rows even though the destination table is empty. Here's what I have in the query:

    First column:
    Field = Expr1: [POs].[PO ID] & [POs].[PO Line ID]
    Append to = NewID (This keeps reverting to this when I try to specify the table name, i.e. POList.[NewID])

    Second column:
    Field = POs.*
    Table = POs
    Append to = POList.*

    Third column:
    Field = "UNIQUE VALUES"
    Criteria = True

    POs is the linked table and POList is the destination. I created the field NewID in POList that is a text field and is the primary key.

    edit: so I changed the second column table to POList but it's telling me there is a duplicate column so I'm guessing I wasn't supposed to create that as a column in advance
    edit2: ok maybe not, now it's telling me there is no field. not sure what I need to do here
    edit3: ok figured it out. I had the table I was appending to in the query. thanks!

  6. #6
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Spoke too soon... It worked once, but then when I went to run it again, it was going to append all the values again, so I tried changing the criteria but now it's not working at all even with resetting the destination table and reverting it back to how it was when it was working (or at least as I can remember). I feel like Chevy Chase in Christmas Vacation..I have no idea what's going on!!

  7. #7
    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
    Can you attach to a post some records from the CSV? And a description of what fields within that CSV - if concatenated - would form a unique identifier.

  8. #8
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    I got it working actually but have a small problem but I can live with it if necessary. It's giving me an error "Microsoft can't append all the records in the append query". It works fine so I can live with the error but if there was a way to ignore this and just have it append the new ones that would be nice. But it works so that's really all that matters! thanks for help!

  9. #9
    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
    "Microsoft can't append all the records in the append query"

    What was the remainder of the error message? key violation??

    If you need help, readers have to know the issue - clearly defined - and some sample data.

  10. #10
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Sorry forgot to mark as solved. The problem was I had included the table I was appending to in the query.

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

Similar Threads

  1. Importing specific fields from a Text File
    By Bcanfield83 in forum Import/Export Data
    Replies: 2
    Last Post: 04-09-2019, 07:23 PM
  2. Replies: 5
    Last Post: 02-24-2016, 04:32 PM
  3. Replies: 1
    Last Post: 07-02-2014, 12:42 PM
  4. Importing text file with blank fields changes to null
    By Egoyret in forum Import/Export Data
    Replies: 9
    Last Post: 11-10-2011, 01:51 PM
  5. XML file not importing all fields
    By ButlerEagle in forum Access
    Replies: 0
    Last Post: 05-17-2010, 10:39 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