locked
Load/Update a csv file into an existing MS Access 2016 table RRS feed

  • Question

  •   I have a csv file, I want to load into an MS Access table.  The csv file has 65 columns, yet I need to map only 10 of them to columns in my table.  Also, I may need to skip some of the records.  I.E. I have data from year 2010 thru 2019.  I only want data from 2017 thru 2019.  Should I take the table and remove 2010 thru 2017 and leave on 2017 in the file?  Can I use a filter to just day where year >= 2017?  Also, how do I map just the fields from the csv file to certain columns in the table.  Should I be using an import for this or can I write the logic for this?  I may need to concatenate or trim data to get the correct format/values I want for the table?  Hopefully I can build a routine that I could run each month for this process.  Also, if I already have some existing data in the table, that would be updated and not just created, any ideas for this.  This is the reason, when I would like to write the code, since if I try an import, I may end up creating duplicate rows.  Can someone give me some ideas on the best way you view how to do this?  Thanx in advance.

    Jerry

    Monday, February 18, 2019 3:31 PM

All replies

  • First, link the file.

    Then use the linked table as source in a straight select query where you filter and select and alias fields until the desired output.

    Now, use this query as source in an append query that will add the records to your table.

    To prevent duplicates, add a unique index to the table using the fields that - when compound - must be unique.

    This proven setup is very easy to create step by step, to debug as needed, and - later - to adjust, should the source file or the table be changed in structure.


    Gustav Brock

    Monday, February 18, 2019 6:49 PM