Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

OPENROWSET when Excel worksheet name is unknown Expand / Collapse
Author
Message
Posted Wednesday, September 7, 2011 7:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 7, 2011 2:02 PM
Points: 1, Visits: 4
I've created a stored procedure that accepts two arguments: the name of an Excel workbook (@workbookBillRun), and the name of the first worksheet found in that workbook (@worksheetBillRun).

Through dynamic SQL, I'm able to construct a statement that pulls out data.

SELECT @sqlBillRun = '
SELECT [ACCTNUM], [INVNUM], [RECURRING], [NON RECURRING], [USAGE],[DISCOUNT],[TAXES]
FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;;Database=' +@workbookBillRun+ ';HDR=YES'',
''SELECT * from [' +@worksheetBillRun+ '$]'')'


The workbook name will always be known: when the user uploads the file, I change the name to match a certain pattern. Example: 2011-08 AUG.xlsx

However, I won't always know the worksheet name. The workbook *should* contain just one worksheet; and the worksheet *should* be named identically to the workbook (2011-08 AUG), but it may not be named as such. And if I pass an invalid worksheet name, the code above will fail.

I've created error handling to alert the user of an incorrectly named sheet. However, I was curious whether I could extract the name of the first worksheet in the workbook from within T-SQL.

Thanks in advance.


Post #1171084
Posted Wednesday, May 7, 2014 7:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 4:38 AM
Points: 16, Visits: 79
Did you ever got this to work ?
Post #1568456
Posted Wednesday, May 7, 2014 10:58 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 1:08 PM
Points: 263, Visits: 869
Notice that they have not logged in since 2011
Post #1568608
Posted Wednesday, May 7, 2014 11:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 4:38 AM
Points: 16, Visits: 79
Yes, I have seen this...but sometimes the person who asks the question also finds a solution him- or herself.

HOWEVER, for any future finders of this post, I will answer this one

The key is to dynamically add a (temporary) linked server to the XLSX say e.g. called TempExcelSheet

The you can then query the worksheets and columns using systems stored procedures sp_tables_ex and sp_columns_ex
Post #1568631
Posted Thursday, June 26, 2014 5:20 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 19, 2014 6:01 PM
Points: 356, Visits: 871
Hi,
What provider did you use when creating your link?
Post #1586689
Posted Friday, June 27, 2014 1:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 4:38 AM
Points: 16, Visits: 79
This is the code I used...enjoy

-- Get table (worksheet) or column (field) listings from an excel spreadsheet

DECLARE @linkedServerName sysname = 'TempExcelSpreadsheet'
DECLARE @excelFileUrl nvarchar(1000) = 'D:\\SHARE\\WLAN\\Report.xlsx'

IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable;

-- Remove existing linked server (if necessary)
if exists(select null from sys.servers where name = @linkedServerName) begin
exec sp_dropserver @server = @linkedServerName, @droplogins = 'droplogins'
end

-- Add the linked server
-- ACE 12.0 seems to work for both xsl and xslx, though some might prefer the older JET provider
exec sp_addlinkedserver
@server = @linkedServerName,
@srvproduct = 'ACE 12.0',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = @excelFileUrl,
@provstr = 'Excel 12.0;HDR=Yes'

-- Grab the current user to use as a remote login
DECLARE @suser_sname NVARCHAR(256) = SUSER_SNAME()

-- Add the current user as a login
EXEC SP_ADDLINKEDSRVLOGIN
@rmtsrvname = @linkedServerName,
@useself = 'false',
@locallogin = @suser_sname,
@rmtuser = null,
@rmtpassword = null

-- Return the table info, each worksheet pbb gets its own unique name
SELECT * INTO #MyTempTable FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;',
'EXEC sp_tables_ex TempExcelSpreadsheet');

SELECT TABLE_NAME
FROM #MyTempTable

--exec sp_executesql 'SELECT * INTO #MyTempTable FROM OPENROWSET(''SQLNCLI'', ''Server=(local);Trusted_Connection=yes;'',''EXEC sp_tables_ex TempExcelSpreadsheet'')'
--EXEC sp_tables_ex @linkedServerName
--EXEC sp_columns_ex @linkedServerName

-- Remove temp linked server
if exists(select null from sys.servers where name = @linkedServerName) begin
exec sp_dropserver @server = @linkedServerName, @droplogins = 'droplogins'
end
Post #1586733
Posted Friday, June 27, 2014 10:21 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 19, 2014 6:01 PM
Points: 356, Visits: 871
Very awesome. I'll try it out, Thanks!
Post #1586949
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse