Hi
I would appreciate any help I can get with my problem

I am a has-been informix DBA trying to get my head around the import of data
into an old version of Sql Server that does not have the latest import tools.

I need to import 70 separate excel files, comprising 1.5 gb of data into a
database.

I tried DTS but it treats the first row as columnnames when they are in fact
data.
The files have been loaded to a firewalled Server that does not have excel
loaded so I can't manipulate the files themselves to add columnnames or
change them into something that might load, like csv for instance.

Following my nose online I found some threads that showed me how to edit the
DTS package to set HDR=No, so that the first rows would load, but go figure,
they still don't load.

So I have abandoned that approach and instead have used Openrowset.

In Query Analyser I can load a file using the following script;
Insert into [slx_prod_old].[sysdba].[Lead_Electoral_Roll_Temp_load]
SELECT *  FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=No;Database=C:\LoadFolder\Testload.xls' ,
'SELECT * FROM [Sheet1$]')

All good so far.  But since there are 70 files I hoped to automate the load
a bit using a stored procedure. The files are numerically named 1_filename,
2_filename etc.
I mention this to explain why my SP has a concatenated load path. Its so i
can substitute te number on each iteration of the loop.
The problem is that the code in the SP doesn't load any rows.
I am wodnering if I am chasing my tail and that i simply can't do what i am
trying to do.
That's OK I suppose as i can just create 70 queries in query analyser to
load them, but you know....

So I'd appreciate a steer on whether I am doing the wrong thing, or doing
the right theing, wrongly.

Here's the SP (The text in the variable @sqlcommand runs perfectly when I
copy it from the debugger directly into a query window)

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER           PROCEDURE [sysdba].[2008_electoral_roll_loader] AS

DECLARE @CNT INT
DECLARE @connect varchar(75)
DECLARE @connectstr varchar(150)
DECLARE @connectstrA varchar(75)
DECLARE @connectstrB varchar(75)
DECLARE @SelectStmt varchar(75)
DECLARE @sqlCommand varchar(1000)

set Ansi_nulls on
set ansi_warnings on

Set @connect = '''Microsoft.Jet.OLEDB.4.0'''
Set @connectstrA = 'Excel 8.0;HDR=No;Database=C:\LoadFolder\'
Set @connectstrB = 'Testload.xls'
Set @SelectStmt = '''SELECT * FROM [Sheet1$]'''

SET @CNT = 1
WHILE(@CNT < 2 )
BEGIN

   SET @Sqlcommand =  'Insert into
[slx_prod_old].[sysdba].[Lead_Electoral_Roll_Temp_load] SELECT *  FROM
OPENROWSET(' +
       @connect +
       ',' +
       ' ''' +
       @connectstrA +
       -- ',' +
       @connectstrB +
       ''' ' +
       ',' +
       @SelectStmt +
       ')'
 
     Exec (@sqlcommand)

Thanks in advance for any help

   SET @CNT = @CNT + 1
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO