Solved

Compare SQL Server 2008 value to linked excel file

Posted on 2011-03-08
1
204 Views
Last Modified: 2012-05-11
I have a SQL Server 2008 database that I have linked to an excel file. How can I constantly compare the value of the database to the same value in the excel file? I just need to know when a number changes. For example, lets say I have a product_id, that same product_id is in an excel file that I have my database linked to that someone else maintains and I need to know when that product_id is updated or deleted and send out emails and alert my asp application. I can handle the asp part but I am stumped out the exact query needed to compare an excel worksheet to a table.
0
Comment
Question by:matthewskyle
[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
1 Comment
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 500 total points
ID: 35130160
Are you comparing an entire Excel sheet to a table, or just a single value in a single cell in an Excel sheet to some value in some table in SQL Server? Comparing the sheet to a table can be done, but it's a bit longer - a single value shouldn't be much trouble at all.

You'd use OPENROWSET to open a connection to your Excel sheet, and then you can query it just like a table and get the value you're interested in. First, make sure that "Ad Hoc Distributed Queries" is enabled, since you'll be querying an external datasource on the fly, and it's an advanced option:

EXEC sp_configure 'Ad Hoc Distributed Queries', 1 
RECONFIGURE

Open in new window


Once that's done, you can connect to your Excel sheet:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
   'Data Source=c:\YourFolder\YourExcelSheet.xls;Extended Properties=Excel 8.0')...[Sheet1$]

Open in new window


That will give you the contents of your sheet as a resultset. You can either modify that query to fetch only the value you want, or (and it may be easier depending on what you're doing) you can insert all those contents into a temp table and work with them there, comparing them to your internal SQL Server value.

If you still need some more helping getting this accomplished and you post some more details about what you're trying to do, I can help you refine the query.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

730 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