Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

Import only new records from an Excel file into an existing table

Hi Experts

I'm needing to import a number of Excel files (separately) into existing tables in my database using VBA.

I can successfully do this with the DoCmd.TransferSpreadsheet however, I only want to add new records in each table.

As an example, the table "AdvParticipants" has a unique "userid" field and if the value in the column "userid" of the spreadsheet already exists in the field "userid" of this table, then skip it, otherwise import the record. I only need data to the last row and column in the spreadsheet imported.

And if possible, at the time of import, I would like to add 2 other fields which do not exist in the spreadsheet - [ImportDate] = "Now()" and Process Desc] = "Round1".

Any help would be appreciated.

Thanks
darls15
0
darls15
Asked:
darls15
2 Solutions
 
als315Commented:
You can link Excel files instead of direct import and import data with query, where you can compare file with existing records and modify any field
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
alternatively, i would suggest to load the excel sheet into Staging Tables, validate the data there, and then load the data to the final tables. easier in the end...
0
 
darls15Author Commented:
Hi, thank you both for your suggestions. Using staging tables seem to be the way to go for my database. I'll award equal points for both these suggestions as they are both workable solutions. Thanks again :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now