Gurus,
I've got a rather garbled script that is giving me a major headache. Basically, I have an Excel sheet with multiple worksheets where the 1st worksheet - called "Contents" -contains a single column containing a list of all the remaining worksheet names that contain the data.
I want to cycle through this Excel file and load the contents of each data worksheet into a separate table in SQL Server 7. I've tried writing the sproc below to do this, but it keeps producing the following error when I try and actually create it which is caused by the line of code in bold:
"Invalid column name 'ID'".
For starters, I dont understand why the CREATE PROC statement should fall over because of an invalid column name cos I thought it used deferred name resolution and therefore doesnt care if objects exist or not at the time the sproc is created. As it is, this column definitely should exist, yet it still falls over.
Any ideas anyone??
Thanks,
Neil
----------------------
CREATE PROC cp_ExcelTDImport @xlsfile varchar(75)
AS
DECLARE @cmd varchar(100)
SELECT @cmd = 'copy "' + @xlsfile + '" "J:\Data\Neil\test.xls"'
EXEC master..xp_cmdshell @cmd
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'ContList')
DROP TABLE ContList
SELECT OperatorNames AS OperatorNames
INTO ContList
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0;IMEX=1;Database=J:\Data\Neil\test.xls'
,'SELECT OperatorNames FROM [Contents$]')
ALTER TABLE ContList ADD ID int IDENTITY(2,1)
DECLARE @opcount int
SELECT @opcount = COUNT(*) from ContList
DECLARE @counter int
SELECT @counter = 1
DECLARE @opname varchar (30)
DECLARE @sql varchar(500)
--Create table for each operator containing raw TD data
WHILE @counter <= @opcount
BEGIN
SELECT @opname = OperatorNames FROM OpList WHERE ID = (@counter + 1)
SELECT @sql = 'SELECT * INTO '
+ '[' + 't_' + @opname + ']' +
+ ' FROM OPENROWSET(' + "'" + 'Microsoft.Jet.OLEDB.4.0' + "'" + ',' + "'"
+ 'Excel 8.0;IMEX=1;Database=' + @xlsfile + "'"
+ ', ' + "'" + 'SELECT * FROM [' + @opname + '$]' + "'" + ')'
EXEC (@sql)
SELECT @counter = @counter + 1
END
I've got a rather garbled script that is giving me a major headache. Basically, I have an Excel sheet with multiple worksheets where the 1st worksheet - called "Contents" -contains a single column containing a list of all the remaining worksheet names that contain the data.
I want to cycle through this Excel file and load the contents of each data worksheet into a separate table in SQL Server 7. I've tried writing the sproc below to do this, but it keeps producing the following error when I try and actually create it which is caused by the line of code in bold:
"Invalid column name 'ID'".
For starters, I dont understand why the CREATE PROC statement should fall over because of an invalid column name cos I thought it used deferred name resolution and therefore doesnt care if objects exist or not at the time the sproc is created. As it is, this column definitely should exist, yet it still falls over.
Any ideas anyone??
Thanks,
Neil
----------------------
CREATE PROC cp_ExcelTDImport @xlsfile varchar(75)
AS
DECLARE @cmd varchar(100)
SELECT @cmd = 'copy "' + @xlsfile + '" "J:\Data\Neil\test.xls"'
EXEC master..xp_cmdshell @cmd
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'ContList')
DROP TABLE ContList
SELECT OperatorNames AS OperatorNames
INTO ContList
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'
,'Excel 8.0;IMEX=1;Database=J:\Data\Neil\test.xls'
,'SELECT OperatorNames FROM [Contents$]')
ALTER TABLE ContList ADD ID int IDENTITY(2,1)
DECLARE @opcount int
SELECT @opcount = COUNT(*) from ContList
DECLARE @counter int
SELECT @counter = 1
DECLARE @opname varchar (30)
DECLARE @sql varchar(500)
--Create table for each operator containing raw TD data
WHILE @counter <= @opcount
BEGIN
SELECT @opname = OperatorNames FROM OpList WHERE ID = (@counter + 1)
SELECT @sql = 'SELECT * INTO '
+ '[' + 't_' + @opname + ']' +
+ ' FROM OPENROWSET(' + "'" + 'Microsoft.Jet.OLEDB.4.0' + "'" + ',' + "'"
+ 'Excel 8.0;IMEX=1;Database=' + @xlsfile + "'"
+ ', ' + "'" + 'SELECT * FROM [' + @opname + '$]' + "'" + ')'
EXEC (@sql)
SELECT @counter = @counter + 1
END