Import Excel Sheet without knowing the sheet name

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

General SQL Server Forums

Script Library

Import Excel Sheet without knowing the sheet name

Author

Topic

alemos
Starting Member

16 Posts

Posted - 2009-12-06 : 09:52:20

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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-06 : 12:19:13

is it guarenteed to always be the first (or only) sheet in the workbook?

alemos
Starting Member

16 Posts

Posted - 2009-12-06 : 16:34:13

Hi russell,

yes! All the workbooks will have only the first sheet filled in.

Thanks

Aécio Lemos
http://www.vlsweb.com.br
O primeiro provedor de hospedagem gerenciada do Brasil

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-06 : 21:45:52

you can use sp_OACreate to do this in vbScript. See here: http://support.microsoft.com/kb/257819

Wouldn't it be better to use DTS/SSIS for this though?

alemos
Starting Member

16 Posts

Posted - 2009-12-08 : 05:38:36

Thanks, I'll take a look at that link. Can I use DTS is I don't know the name of the file or the sheet? I have never used SSIS, does the express version have SSIS?

Thanks again.

Aécio Lemos
http://www.vlsweb.com.br
O primeiro provedor de hospedagem gerenciada do Brasil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources