Is it possible to compare Excel and SQL Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Is it possible to compare Excel and SQL Table

Author

Topic

hambergler
Starting Member

9 Posts

Posted - 2010-06-18 : 04:52:18

I'm trying to update a table based on a list of values in an Excel spreadsheet.

Assume that my excel spreadsheet is named 'names.xls' and only contains two values (A1 = 'Bob' and A2 = 'Jerry'). I have a table where I keep the employee information for each of them and their names are in the table exactly as entered in the Excel spreadsheet.

Is it possible to run a select statement that will compare the values of my excel spreadsheet to the entries in the table?

I was trying something like this:


SELECT * FROM tableNAME
WHERE empNAME = (SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\location\names.xls;HDR=No','SELECT * FROM [Sheet1$]'));

This was returning an error that the subquery returned more than 1 value. I understand this but is there anyway around this to get what I want? I don't want to have to import the excel info into another table to compare the data if possible.

Thanks in advance!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 05:13:09

Try

SELECT t1.* FROM tableNAME as t1 inner join
(SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\location\names.xls;HDR=No','SELECT * FROM [Sheet1$]')) as t2
on t1.Empname=t2.empname;

Madhivanan

Failing to plan is Planning to fail

hambergler
Starting Member

9 Posts

Posted - 2010-06-18 : 05:38:40

Premature Yak Congratulator Madhivanan,

Thanks for the quick reply. I am getting an Invalid column name error with t2.empName. The data in the excel spreadsheet has no column name. I tried making the first cell equal to "empNAME" but that didn't work either.

Any suggestions?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 07:06:47

See the column names from this

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\location\names.xls;HDR=No','SELECT * FROM [Sheet1$]')

Madhivanan

Failing to plan is Planning to fail

hambergler
Starting Member

9 Posts

Posted - 2010-06-18 : 08:19:54

I understand but how should I change the t1.empNAME=t2.empNAME?

I've tried t1.empNAME=t2 and t1.empNAME=t2.empNAME. Both gave errors. I am only putting in the names into the Excel spreadsheet, nothing else. I need something that follows the logic of select the rows where the employees' names are found in this list (excel document).

Thanks for your continued help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 08:32:28

What columns do you see whrn running the code?
You need to use that column

Madhivanan

Failing to plan is Planning to fail

hambergler
Starting Member

9 Posts

Posted - 2010-06-18 : 09:20:56

I don't follow. I'm new to using SQL. Where should I be looking to see this column name? I can see the table for the SQL Database (empNAME) but Excel doesn't really have a column name.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 09:31:38

Did you see any column names when you run this?

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\location\names.xls;HDR=No','SELECT * FROM [Sheet1$]')

Madhivanan

Failing to plan is Planning to fail

hambergler
Starting Member

9 Posts

Posted - 2010-06-18 : 10:06:02

I see now! Thanks, this worked!

Take care!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-18 : 10:09:11

quote:
Originally posted by hambergler

I see now! Thanks, this worked!

Take care!

You are welcome

Madhivanan

Failing to plan is Planning to fail

hambergler
Starting Member

9 Posts

Posted - 2010-06-18 : 10:12:21

One last thing!

Sorry, how would I change this into a DELETE? Just put my Select query inside the DELETE?

hambergler
Starting Member

9 Posts

Posted - 2010-06-18 : 10:25:51

I did some research but I don't want to test this without some feedback.


DELETE * FROM tableNAME
WHERE empNAME IN (SELECT statement here);

Should that work?

jaysysa
Starting Member

3 Posts

Posted - 2014-05-14 : 12:55:19

Thanks for your post Madhivanan,

i have used your query:(SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\location\names.xls;HDR=No','SELECT * FROM [Sheet1$]')) as t2
on t1.Empname=t2.empname;

and got below issue

Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network

is this resionable issue?

jay

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-06-17 : 03:28:30

quote:
Originally posted by jaysysa

Thanks for your post Madhivanan,

i have used your query:(SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=D:\location\names.xls;HDR=No','SELECT * FROM [Sheet1$]')) as t2
on t1.Empname=t2.empname;

and got below issue

Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network

is this resionable issue?

jay

Note that the file should be in server's directory. Also check the sheet name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources