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
 General SQL Server Forums
 Script Library
 Import Excel Sheet without knowing the sheet name
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

alemos
Starting Member

Brazil
16 Posts

Posted - 12/06/2009 :  09:52:20  Show Profile  Visit alemos's Homepage  Reply with Quote
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

USA
5072 Posts

Posted - 12/06/2009 :  12:19:13  Show Profile  Visit russell's Homepage  Reply with Quote
is it guarenteed to always be the first (or only) sheet in the workbook?
Go to Top of Page

alemos
Starting Member

Brazil
16 Posts

Posted - 12/06/2009 :  16:34:13  Show Profile  Visit alemos's Homepage  Reply with Quote
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
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 12/06/2009 :  21:45:52  Show Profile  Visit russell's Homepage  Reply with Quote
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?
Go to Top of Page

alemos
Starting Member

Brazil
16 Posts

Posted - 12/08/2009 :  05:38:36  Show Profile  Visit alemos's Homepage  Reply with Quote
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
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