SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Is it possible to compare Excel and SQL Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hambergler
Starting Member

9 Posts

Posted - 06/18/2010 :  04:52:18  Show Profile  Reply with Quote
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

India
22772 Posts

Posted - 06/18/2010 :  05:13:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

hambergler
Starting Member

9 Posts

Posted - 06/18/2010 :  05:38:40  Show Profile  Reply with Quote
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?



Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 06/18/2010 :  07:06:47  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

hambergler
Starting Member

9 Posts

Posted - 06/18/2010 :  08:19:54  Show Profile  Reply with Quote
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!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 06/18/2010 :  08:32:28  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
What columns do you see whrn running the code?
You need to use that column

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

hambergler
Starting Member

9 Posts

Posted - 06/18/2010 :  09:20:56  Show Profile  Reply with Quote
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.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 06/18/2010 :  09:31:38  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

hambergler
Starting Member

9 Posts

Posted - 06/18/2010 :  10:06:02  Show Profile  Reply with Quote
I see now! Thanks, this worked!

Take care!
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 06/18/2010 :  10:09:11  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by hambergler

I see now! Thanks, this worked!

Take care!


You are welcome

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

hambergler
Starting Member

9 Posts

Posted - 06/18/2010 :  10:12:21  Show Profile  Reply with Quote
One last thing!

Sorry, how would I change this into a DELETE? Just put my Select query inside the DELETE?
Go to Top of Page

hambergler
Starting Member

9 Posts

Posted - 06/18/2010 :  10:25:51  Show Profile  Reply with Quote
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?
Go to Top of Page

jaysysa
Starting Member

India
3 Posts

Posted - 05/14/2014 :  12:55:19  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 06/17/2014 :  03:28:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000