Hello Everyone,

I have a excel file with variable column header information and one of
the column header has some data which needs to be read. I couldn't find
a way to read the column header information. Does anyone know how to
read the column name?

The data under the column can read as DTSSource("Col1"). But my case I
want to read the column name also.

Thanks.

Hi,

I had a similar problem and I developed a work around. Basically I import
the excel spread sheet into a temp table. This temp table will have its
columns names set to values of the first row in excel. The code of the proc
given will basically select the column names of the temp table from the
syscolumns table and build an insert statement to add this first row into the
actual table you want data in. You will have to change the values of the
@column_names, @Excel_Import_Table and @Target_Import_Table variables. I hope
this helps and I would be interested in a more elegant solution if you find
one.

CREATE PROCEDURE dbo.ImportExcelFirstRow_P
AS
DECLARE @errorsave int

BEGIN
SET @errorsave = 0
DECLARE @insert_stmt nvarchar(4000)
DECLARE @values_stmt varchar(4000)
DECLARE @column_names varchar(4000)
DECLARE @Excel_Import_Table varchar(255)
DECLARE @Target_Import_Table varchar(255)

SET @column_names = '[Column1], [Column2], [Column3]'
SET @Excel_Import_Table = 'Temp table name populated with spreadh sheet data'
SET @Target_Import_Table = 'Actual Target Table Name'

SET @values_stmt = ''
SELECT
@values_stmt = @values_stmt + '''' + NAME + ''','
FROM dbo.SYSCOLUMNS
WHERE ID = (
SELECT
ID
FROM dbo.sysobjects
WHERE xtype = 'U'
AND NAME = @Excel_Import_Table

)
ORDER BY colorder

-- Remove trailing comma
SET @values_stmt = SUBSTRING(@values_stmt, 0, LEN(@values_stmt))
-- Add Insert section of statement
SET @insert_stmt = 'Insert Into ' + @Target_Import_Table + '( '+
@column_names + ') Values(' + @values_stmt + ')'
PRINT @insert_stmt

EXEC @errorsave = sp_executesql @insert_stmt
IF @errorsave <> 0
RETURN @errorsave

SET @errorsave = @@ERROR
RETURN @errorsave
END