?
Solved

Overwrite Access data from an Excel Spreadsheet

Posted on 2014-04-08
11
Medium Priority
?
6,648 Views
Last Modified: 2014-04-14
We have an Access DB that houses imported data from a 3rd party vendor.  Unfortunately, some of the 3rd party data is incorrect.  Users have the correct data on an Excel spreadsheet.  

What we need is a method of overwriting the Access data with the data from the Excel spreadsheet.

The column headings are identical in the Access table and Excel spreadsheet.
We can't simply truncate and load the spreadsheet data because it's only some of the data that needs to be updated.

Walking through the scenario, the script or macro would have to compare the key field from the Excel file and match it with the key field in the Access DB.  If it finds it, overwrite the row data with the row data from Excel.  

It sounds simple but the only thing I've been able to find on Google is how to just import data and overwrite an entire Access table.  Not just specific rows of data.
0
Comment
Question by:kredeker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39986272
You can create a 'Find Unmatched .' query; joining on the key, (to identify/list all keys that occur in the excel sheet, but not in the table).
Then create a second (append) query (joined on the key to the unmatched query) that will only append records that were found in the unmatched query.
0
 
LVL 19

Accepted Solution

by:
regmigrant earned 510 total points
ID: 39986286
I would approach it slightly differently - load the excel data into a new temporary table and generate a relationship between the two tables using the key; then you can join the tables and identify differences or walk the smaller table and check the larger - and potentially display in a form for confirmation before overwrite. this will limit the probability that the Excel data is actually wrong and you are overwriting good with bad. You could also generate an audit trail, offer 'undo' features and report the updates back to the vendor so they can fix their own data.
0
 
LVL 7

Assisted Solution

by:COACHMAN99
COACHMAN99 earned 495 total points
ID: 39986295
If you view the unmatched query before executing it you will see what will be appended.
Q1:
SELECT table2.ID, table2.vvv
FROM table2 LEFT JOIN Table1 ON table2.[ID] = Table1.[ID]
WHERE (((Table1.ID) Is Null));

Q2:
INSERT INTO Table1 ( ID, vvv )
SELECT [table2 Without Matching Table1].ID, [table2 Without Matching Table1].vvv
FROM [table2 Without Matching Table1];
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 38

Assisted Solution

by:PatHartman
PatHartman earned 495 total points
ID: 39986533
1. Import the spreadsheet.
2. Create a query that joins the spreadsheet to the table you want to update using the unique ID.  (no need to create a permanent relationship)  
3. Change the query to an Update query and pick the columns you want to update (not the PK) and enter the column names from the spreadsheet which will be the source of the data.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39986635
no need for a permanent relationship agreed - but I suggest if this is a regular update you want to consider building a robust system rather than a one off you have to fettle each time.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39986652
If you only want to append the ones you don't have in the original table then:
1. Import the spreadsheet into a table, ('table2')
2. create the 'Find unmatched' query (via the query wizard,)
3. create an append query based on joining table2 to your 'unmatched' query

qrytable2 Without Matching Table1:
SELECT table2.ID, table2.vvv
FROM table2 LEFT JOIN Table1 ON table2.[ID] = Table1.[ID]
WHERE (((Table1.ID) Is Null));

Q2:
INSERT INTO Table1 ( ID, vvv )
SELECT [qrytable2 Without Matching Table1].ID, [qrytable2 Without Matching Table1].vvv
FROM [qrytable2 Without Matching Table1];
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 39986706
@coachman99
I think the OP wants to update existing data.

To add new rows, a simple append query would work.  It would raise errors about duplicates but those can be ignored.

Alternatively, a query with a right join will strangely enough both update existing rows and add new ones.

@regmigrant
You can't really have a permanent relationship with a table that is deleted and reimported or whose rows are deleted prior to each reimport.  Relationships are created to enforce RI and there is no RI to enforce in this particular case.  If you defined a relationship, you would have to drop it prior to the import/append and recreate it each time  so it doesn't actually do anything and therefore isn't necessary.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39986718
got it - thanks :-) - Pat's reply is 100%
0
 

Author Comment

by:kredeker
ID: 39986729
Thanks for the reply all!  I will give give your solutions a try and let you know which one I went with then give points out appropriately.
0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39988151
Pat - yes, my comment stands on its own :)
0
 

Author Comment

by:kredeker
ID: 39999959
I ended up doing the following:

1. Created a Saved Import that imports the Excel sheet into a table.
2. Created a SQL query that deletes matching records from the main table.  Created another query that appended the Excel table to the main table.

It seems to be working OK.  Thanks for the help with the solution!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question