I have an Excel spreadsheet that is updated regularly, that I need to import into an existing database table with the press of a button.
About the Spreadsheet:
- The spreadsheet layout, structure, columns and tabs are static. It's generated with new data from another product.
- The spreadsheet name may be different because it date time stamps the file name when generated.
About the Database:
- There is a single table that needs to be updated.
- The schema of the table is not exactly the same as spreadsheet (e.g. the Name column in the spreadsheet is "hostname" in the database table)
- The schema of the table is however static.
How I imagined it to work is:
There would be a button in the access form to update the table from the spreadsheet.
The excel spreadsheet would be in a predefined location on the file system, like c:\temp\import.
The logic would search a specific column in the updated excel spreadsheet for a unique identifier, such as the name, it would then update the table record with anything in the excel row that has changed, compared to the table data. Sort of like vLookup in excel.
Any new rows in the spreadsheet would be added to the table.
Any rows in the spreadsheet that have been deleted would be marked as deleted in the database table (a Deleted field would switch from No to Yes).