Convert XLS data to a SQL Server table

A stored procedure to properly import data from an Excel table into SQL Server.

When you import an Excel table into SQL Server you wind up with a table with all the columns from the Excel table as fields and all the rows as records. However, in a RDBMS you normally want a table with three fields: row, col, and value. To do the conversion, I created a stored procedure without any cursors.

To start, let's say you have a "testtable" imported from Excel and an empty table "test_res" with three fields: name, date, value(bedrag). The Excel data looks like this:

name  [01/01/2000]  [01/02/2000] 
aaa           10             20 
bbb           30             40 

Here's the stored procedure:

CREATE PROCEDURE [dbo].[transform] AS 
declare @t_id int 
declare @i int 
declare @sql nvarchar(500) 
declare @naam nvarchar(500) 
declare @datum datetime 
SET NOCOUNT OFF 

create table #col 
        ( 
        id int identity, 
        datum datetime, 
        datchar nvarchar(40) 
                          ) 

create table #col2 
        ( 
        id int identity, 
        naam nvarchar(40) 
                          ) 
select @t_id=id  from sysobjects where name = 'testtable' 
insert into #col (datum,datchar) select 
       convert(datetime,substring(name,2,len(name)-2)),name 
       from syscolumns where id = @t_id and colid > 1 order by colid 

insert into #col2 (naam) select  naam   from testtable 
truncate table test_res 

insert into test_res (naam,datum,bedrag) 
       select #col2.naam,#col.datum,0 from #col cross join #col2 

set @i = 2 
while @i < ((select count(*) from #col ) + 2) 
begin 
 set @sql = N'update test_res set bedrag =  
       [' + COL_NAME(OBJECT_ID('testtable'), @i) +  ']
       from test_res inner join  testtable 
       on test_res.naam = testtable.naam inner join  #col 
       on #col.datum= test_res.datum and #col.id = ' + 
       CONVERT(char(5),@i - 1)

 execute sp_executesql @sql 
 select @i = @i +1 
end 

select * from test_res 

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best SQL Server Web Links: tips, tutorials, scripts, and more.
  • Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in February 2005

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close