CODEWITHSUNDEEP
sql-serverexcel
Felix D.
Felix D.

Reputation: 5083

Get data into Excel from SQL Server datasource depending on values in Excel sheet

Let's say in my Excel sheet, I have the following "table":

ZipCodes |
---------+
  4059   |
  5806   |
  4529   |

I need to get the data from a SQL Server view WHERE column ZipCodes has one of the values in my Excel table.

I want the user to be able to add as many ZipCodes as he wants, while not adjusting the query manually.

I've looked that this question but I cannot get it working.

I'm stuck with passing a list of values to the sql query. It's working if I am passing a single ZipCode.

Sth. like this would help me:

WHERE  ZipCode IN (SELECT * FROM  [sheet2$a1:a2])

but that's breaking the query.

Upvotes: 1

Views: 1051

Answers (1)

Thom A
Thom A

Reputation: 95544

One solution here would be to use OPENROWSET. After discussion, it does appear that the location of the Excel file can and will change; this is fine but means we need to build a dynamic SQL solution, as OPENROWSET requires literal strings for it's parameters (no variables).

Firstly, I don't know what what version of the ACE Drivers you have installed. As a result in my code I am using the drivers I have installed; which are the 2010 drivers (version 12). If that's not the version you're using, you'll need to change the value that I comment.

Normally, an OPENROWSET query might look like this:

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', --This declares the Driver. You may need to change this
                'Excel 8.0;HDR=YES;;Database=\\YourFileServer\yourShare\YourFile.xlsx',
                'SELECT *
                FROM [Sheet1$A1:G];');

This'll return all rows from columns A through to G, starting at Row 2. Row 1 will be assumed to contain the Header (HDR=YES); if you don't have headers use HDR=NO.

The problem here is that we can't pass a a variable here. Thus we need to do something more dynamic. This gets you something along the lines of:

DECLARE @File nvarchar(500); --This would be your parameter
SET @File = N'\\YourFileServer\yourShare\YourFile.xlsx';

DECLARE @SQL nvarchar(MAX);
SET @SQL = N'SELECT *' + NCHAR(10) +
           N'FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',' + NCHAR(10) +
           N'                ' + QUOTENAME(N'Excel 8.0;HDR=YES;Database=' + @File,N'''') + N','  + NCHAR(10) +
           N'                ''SELECT *'  + NCHAR(10) +
           N'                 FROM [Sheet1$A1:G];'');';
PRINT @SQL;
EXEC sp_executesql @SQL;

Now, finally, you want to use this data against your table/view. This, therefore, might finally look something like this (assuming your view is called Customer_vw, the data in the excel file in in column A, and the column in both datasets is called ZipCode):

DECLARE @File nvarchar(500); --This would be your parameter
SET @File = N'\\YourFileServer\yourShare\YourFile.xlsx';

DECLARE @SQL nvarchar(MAX);
SET @SQL = N'WITH ExcelZips AS (' + NCHAR(10) +
           N'    SELECT ZipCode' + NCHAR(10) +
           N'    FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',' + NCHAR(10) +
           N'                    ' + QUOTENAME(N'Excel 8.0;HDR=YES;Database=' + @File,N'''') + N','  + NCHAR(10) +
           N'                    ''SELECT *'  + NCHAR(10) +
           N'                     FROM [Sheet1$A1:A];''))' + NCHAR(10) +
           N'SELECT [YourColumns]' + NCHAR(10) +
           N'FROM Customer_vw C' + NCHAR(10) +
           N'     JOIN ExcelZips EZ ON C.ZipCode = EZ.ZipCode --Note that EZ.ZipCode will not show in intellisense' + NCHAR(10) +
           N'WHERE ...;'; --You'll need to complete the WHERE here, and add any ORDER BY etc.
PRINT @SQL;
EXEC sp_executesql @SQL;

Note that I have PRINT statements in the queries. These are your friends. I (personally) suggest that you comment out the EXEC statements first, and just use the PRINT statements. Check that the output from the PRINT looks correct. If it does then run it, and if you get an error trouble shoot the output from the PRINT, rather than the dynamic SQL. Once you've fixed the non-dynamic SQL, propagate the changes to the dynamic SQL.

Hopefully that explains everything. If you have any questions, please do ask.

Upvotes: 2

Related Questions