Hi everyone,
I have been reading the several threads on importing an excel spreadsheet. I managed to get my procedure up and running but I have one last problem. I never know beforehand the name of the Sheet to import. It could be Sheet1 or Customers or anything. I only know the filename.
An example of my test code:
DECLARE @WORKBOOK VARCHAR(200)
DECLARE @SHEET VARCHAR(200)
SET @SHEET = 'CUSTOMERS'
SET @WORKBOOK = 'c:\temp\exceltest.xls'
DECLARE @COMMAND VARCHAR(500)
--IMPORT EXCEL FILE TO TABLE
SET @COMMAND = 'SELECT * INTO TEMPIMPORT FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=' + @WORKBOOK + ''', ''SELECT * FROM [' + @SHEET + '$]'')'
IF @DEBUG = 1 PRINT @COMMAND
EXEC sys.sp_sqlexec @COMMAND
The problem is I have to specify the sheet name (Customers in that example), but I need to import without knowing the sheet name. In .net, you can get the sheets collection or find the sheet by index. Is there any way to do this in SQL?
I have been reading posts on the internet for 2 days now. The only one I didn't read is one in Expert Exchange, because those *#*%!@ charge for their help.
Thanks for any help.
Aécio Lemos
http://www.vlsweb.com.br
O primeiro provedor de hospedagem gerenciada do Brasil
Import Excel Sheet without knowing the sheet name
Posted - 12/06/2009 : 09:52:20
