Getting row info from a table created by an Excel

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Getting row info from a table created by an Excel

Author

Topic

umertahir
Posting Yak Master

154 Posts

Posted - 2009-04-21 : 06:40:25

Hi All,

I have different format of spreadsheets which are then imported to SQL table.
Column headers, which can be on top row OR 3rd row or 2nd row etc, of the spreadsheet has to be identified one by one and to be put in the table as rows. So e.g.,


Spreadsheet 1:
Row 1: Langhill Herd - Calving Records
Row 2: Blank
Row 3: Freeze_Brand  Ear_Tag  Lact_No


Spreadsheet 2:
Row 1: Column1  Column2  Column3

In above spreadsheet 1 example Row # 3 contains three column names which should go in a sql table(tblColumnInformation) like this:


ColumnorderId   ColumnName
1                   Freeze_Brand
2                   Ear_Tag
3                   Lact_No

Similarly for Spreadsheet 2 example Row # 1 contains three column names which should go in a sql table(tblColumnInformation) like this:


ColumnorderId   ColumnName
1                   Column1
2                   Column2
3                   Column3

The way I import the excel file into the SQL table is by inserting the identity column so the table comes out for spreadsheet1 like this:


intOrderBy  F1                            F2       F3
1            Langhill Herd - Calving Records   NULL     NULL
2            NULL                              NULL     NULL    
3            Freeze_Brand                      Ear_Tag  Lact_No

From above example, I want intorderBy = 3 row information to be inserted into the table: tblColumnInformation with the columnorderId as they appear.

Any suggestions here will be really helpful and be much appreciated.

Thanks in advance.

umertahir
Posting Yak Master

154 Posts

Posted - 2009-04-21 : 07:02:38

I tried this way but that returns the column names from the master tables:


DECLARE @columnName varchar(4000)
DECLARE @maxColumnId INT --Stores maximum column Id number to help the WHILE loop to stop after doing its work
DECLARE @currentColumnId INT -- Stores current column Id which will be updated until reached the maximum one
DECLARE @chrImportedTableName SYSNAME

SELECT @currentColumnId = 1	
SELECT @chrImportedTableName = 'tblImportedData'

SELECT @maxColumnId = MAX(colid)
FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id
WHERE o.name = @chrImportedTableName

WHILE (@currentColumnId <= @maxColumnId)
BEGIN

			SELECT @columnName = c.name, 
			FROM syscolumns c INNER JOIN sysobjects o ON o.id = c.id
			WHERE	o.name	= @chrImportedTableName AND
					c.colid	= @currentColumnId
			
			PRINT CAST(@currentColumnId AS VARCHAR) + '  ' + @columnName
	SELECT @currentColumnId = @currentColumnId + 1	
END

Output


1  intOrderBy
2  F1
3  F2
4  F3

I want to join the table with above code so that i can get the row information from intOrderBy = 3 row.
Please note that tblImportedData is the table which has the data imported from the Spreadsheet1

umertahir
Posting Yak Master

154 Posts

Posted - 2009-04-21 : 08:46:41

any suggestions will be helpful?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources