Import from Text file - Single column to multiple columns

One of the users asked this question in the SQL forum

I have data in the text as follows

AccountNo: 00234543
AccountName: Kickser
City: Chicago
AccountNo: 00234543
AccountName: Annis
City: Seatle
AccountNo: 12234456
AccountName: pargenezzer
City: NYC
AccountNo: 12233477
AccountName: PILIP
City: Edison


How do I store data to SQL Server table that has columns AccountNo,AccountName,City which is normalised?

Here is one of the methods that does it

--Create statging table to load data

create table test_data(data varchar(1000),value varchar(1000))


--Load data from text file to staging table

BULK INSERT test_data FROM 'file_path'  WITH


--Add identity column

alter table test_Data add id int identity(1,1)


--Do process and convert each column value to multiple columns

declare @count int,@sql varchar(8000),@columns varchar(1000),@i int

select @count=count(distinct data) from test_Data
select @sql='', @i=1

while @i<=@count
select @sql=@sql+'select id,value as value'+cast(@i as varchar(10))+' from test_Data where id%'+cast(@count as 
(2))+'='+cast(case when @i=@count then 0 else @i end as varchar(2))+') as t'+cast(@i as varchar(2))+
case when @i>1 then ' on'+cast(@i-1 as varchar(2))+'='+'t'+cast(@i as varchar(2))+'.id' else '' end+
case when @i<>@count then ' inner join (' else '' end,
@columns=coalesce(@columns+',','')+'t'+cast(@i as varchar(2))+'.value'+cast(@i as varchar(10))

        select @i=@i+1


--Show the result

@sql='select '+@columns+' from



Now the result can be copied to another normalised table


Published Tuesday, May 5, 2009 10:37 AM by Madhivanan


# re: Import from Text file - Single column to multiple columns

Wednesday, May 6, 2009 11:56 AM by kongstad

I think this select statement is a lot more intuitive than what you suggested.


MAX(case when data='AccountNo' then value else null end) AccountNo,

MAX(case when data='AccountName' then value else null end) AccountName,

MAX(case when data='City' then value else null end) City,

MAX(case when data='Country' then value else null end) Country


(select (id-1)/4 id, data,value from test_data) a

group by id

It replaces all your code after you have added the identity column.

It assumes that the data is ordered, and uses the id column to make a new value as (i-1)/4, assuring that connected rows have the same id.

It then simply uses the max function to unpivot the table.

# re: Import from Text file - Single column to multiple columns

Wednesday, May 6, 2009 1:22 PM by Madhivanan


It is ok for fixed number of columns. What if new column is added like State? You need to modify the query. Thats why I used dynamic sql which will take care of any number of columns