April 11, 2014 at 8:53 am
I have been given an excel spreadsheet that contains a column with about 10000 ids. I need to use the data from that single column (ids) in a SQL query so that I can extract the IDs that are present in the database from the list in the spreadsheet.
How do i reference the excel sheet in my query?
April 11, 2014 at 9:01 am
Sabio (4/11/2014)
I have been given an excel spreadsheet that contains a column with about 10000 ids. I need to use the data from that single column (ids) in a SQL query so that I can extract the IDs that are present in the database from the list in the spreadsheet.How do i reference the excel sheet in my query?
Either load it into a temporary staging table / to a recordset destination / or use a direct look up to the spreadsheet. You don't have to write a query to do a lookup, its like telling SSIS to build one internally..
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 11, 2014 at 9:09 am
so I am kind of new to this so for instance if the query looked something like this
select
userids.cd1
userids.cd12
userids.cd3
from userids
where userids IN ( )
How do I reference the column with 10000ids using IN clause
April 11, 2014 at 9:56 am
Use the lookup transformation after the source which can serve your purpose(Instead of Writing query).
10000 is a very small number, it should be a cake walk for ssis until there are data discrepancies.
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
April 11, 2014 at 10:52 am
You can use OPENROWSET. Assuming you have ACE driver installed:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Test\MySheet.xlsx',
'select * from [Sheet1$]')
Make sure the file is not opened by any other program.
--Vadim R.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply