How to read insert data from spreadsheets

[plevey]

I need to insert data into an SQL table by reading multiple Excel spreadsheets. I've figured out how to code the loop but decided to test with a single sheet.

When I run the following code in Query Analyser I get:
Server: Msg 7202, Level 11, State 2, Line 32
Could not find server 'Excel' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.
(line 32 is the INSERT).
However, it doesn't seem to be executing the code - I tried inserting a few random 'Print' statements before the INSERT and none were executed. sp_helpserver shows that the linked server has not been addded. If I execute the SET @SQLCommand and EXEC @SELCommand separately the linked server is addded and I can run select * from Excel...Data and see the spreadsheet data. Any advice gratefully received


The Code

Truncate Table ForecastArray

declare @WsheetName VarChar(100)
declare @WsheetPath VarChar(100)
declare @SQLCommand VarChar(600)


-- set path to worksheets

set @WsheetPath = 'C:\Test\'
set @WsheetName = 'Forecast01.xls'

-- Create a linked server connection to the worksheet.

SET @SQLCommand =
'EXEC master..sp_addlinkedserver '+
'''' + 'Excel' + '''' + ',' +
'''' + 'Jet 4.0' + '''' + ',' +
'''' + 'Microsoft.Jet.OLEDB.4.0' +
'''' + ',' + '''' + @Wsheetpath +
@WsheetName + '''' + ',' +
'NULL,' + '''' + 'Excel 8.0' + ''''

EXEC (@SQLCommand)

-- login to the linked server

EXEC sp_addlinkedsrvlogin 'Excel', FALSE

-- Now that the worksheet is "attached" insert its data into table ForecastArray. -

INSERT into ForecastArray select * from Excel...Data

-- Cleanup

EXEC sp_droplinkedsrvlogin 'Excel',NULL
EXEC sp_dropserver 'Excel', NULL

GO

 

[Rhys666]

Had a quick test on my local machine and it looks like the linked server isn't added to sysservers until the EXEC(@SQLCommand) command is committed.

Try breaking the process up into a couple of stages in Query Analyzer like this;

Truncate Table ForecastArray

declare @WsheetName    VarChar(100)
declare @WsheetPath VarChar(100)
declare @SQLCommand VarChar(600)


 -- set path to worksheets

set @WsheetPath = 'C:\Test\'
set @WsheetName = 'Forecast01.xls'

-- Create a linked server connection to the worksheet. 

      SET    @SQLCommand = 
            'EXEC master..sp_addlinkedserver '+ 
                        '''' + 'Excel'  + '''' + ',' + 
                        '''' + 'Jet 4.0' + '''' + ',' + 
                        '''' +     'Microsoft.Jet.OLEDB.4.0' + 
                        '''' + ',' + '''' + @Wsheetpath + 
                        @WsheetName + '''' + ',' + 
                        'NULL,' + '''' + 'Excel 8.0' + ''''

     EXEC (@SQLCommand)
GO
-- login to the linked server

    EXEC sp_addlinkedsrvlogin 'Excel', FALSE

-- Now that the worksheet is "attached" insert its data into table ForecastArray. -

INSERT into ForecastArray  select * from Excel...Data

-- Cleanup

  EXEC sp_droplinkedsrvlogin 'Excel',NULL
  EXEC sp_dropserver 'Excel', NULL

GO

Rhys

""Vampireware /n/, a project, capable of sucking the lifeblood out of anyone unfortunate enough to be assigned to it, which never actually sees the light of day, but nonetheless refuses to die."

My Home

 

[plevey]

Thanks for the post Rhys but sadly inserting the GO in the middle of a BEGIN / END screws up my looping process - See below. Any other thoughts ?

Truncate Table ForecastArray

declare @WsheetName VarChar(100)
declare @WsheetPath VarChar(100)
declare @Command VarChar(100)
declare @SQLCommand VarChar(600)

-- set path to worksheets

set @WsheetPath = 'C:\Test\'
set @WsheetName = ''


--Create a temporary table #Wsheets to hold list of Excel worksheets.

CREATE TABLE #Wsheets (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[WsheetName] [varchar] (400) NULL
) ON [PRIMARY]

-- load the temporary table with the worksheets to be processed

SET @Command = 'dir ' + @WsheetPath + '*.xls /s > ' + @WsheetPath + 'Excel.txt'
EXEC master.dbo.xp_cmdshell @command

SET @Command = 'type ' + @WsheetPath + 'Excel.txt'
INSERT INTO #Wsheets EXEC master.dbo.xp_cmdshell @command

-- Clean the temporary table

DELETE from #Wsheets
WHERE WsheetName NOT LIKE '%.xls' OR WsheetName IS NULL

-- Clean up the worksheet name

UPDATE #Wsheets
SET WsheetName = SUBSTRING(WsheetName,(CHARINDEX ( 'Forecast', WsheetName)) ,200)
WHERE WsheetName LIKE '%.xls'

-- Process the temporary table #Wsheets and get the data out of workshhet into ForecastArray


declare wsheet_cur CURSOR FOR SELECT [WsheetName] from #wsheets

OPEN wsheet_cur
FETCH NEXT FROM wsheet_cur INTO @WsheetName

WHILE @@FETCH_STATUS = 0
BEGIN

-- Create a linked server connection to the worksheet.

SET @SQLCommand =
'EXEC master..sp_addlinkedserver '+
'''' + 'Excel' + '''' + ',' +
'''' + 'Jet 4.0' + '''' + ',' +
'''' + 'Microsoft.Jet.OLEDB.4.0' +
'''' + ',' + '''' + @Wsheetpath +
@WsheetName + '''' + ',' +
'NULL,' + '''' + 'Excel 8.0' + ''''

EXEC (@SQLCommand)

GO

-- login to the linked server

EXEC sp_addlinkedsrvlogin 'Excel', FALSE,NULL,NULL

-- Now that the worksheet is "attached" insert its data into table ForecastArray. -

INSERT into ForecastArray select * from Excel...Data

-- Now DROP the current link so we can create the next one.

EXEC sp_droplinkedsrvlogin 'Excel',NULL
EXEC sp_dropserver 'Excel', NULL

FETCH NEXT FROM wsheet_cur INTO @WsheetName

END

-- Final cleanup

drop table #Wsheets
-- EXEC sp_droplinkedsrvlogin 'Excel',NULL
-- EXEC sp_dropserver 'Excel', NULL
CLOSE wsheet_cur
DEALLOCATE wsheet_cur

 

[plevey]

Cracked it. Created a parameterised stored proc (1) to addlinkedserver and perform the insert. Then created a second sp to loop through the list of spreadsheets and call sp1) passing path/filename.