Hi
I would appreciate any 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


SET @CNT = @CNT + 1
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Stop Press.
I did a better search on teh archives and found my answer
IMEX=1; was needed in the openrowset statement.
All working now.
Thanks to Rich for the pointer


"Iulio" wrote:

> Hi
> I would appreciate any 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
>
>
> SET @CNT = @CNT + 1
> END
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
>
>
>