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
Country:USA
AccountNo: 00234543
AccountName: Annis
City: Seatle
Country:USA
AccountNo: 12234456
AccountName: pargenezzer
City: NYC
Country:USA
...
....
.....
AccountNo: 12233477
AccountName: PILIP
City: Edison

Country:USA

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))

GO

--Load data from text file to staging table

BULK INSERT test_data FROM 'file_path'  WITH
(
FIELDTERMINATOR =':',
ROWTERMINATOR ='\n'
)

GO

--Add identity column

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

GO

--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
Begin
       
select @sql=@sql+'select id,value as value'+cast(@i as varchar(10))+' from test_Data where id%'+cast(@count as 
        varchar
(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 t1.id+'+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

End

--Show the result

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

('+@sql

exec(@sql)

Now the result can be copied to another normalised table

 

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

Comments

# 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.

select

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

from

(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

kongstad,

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