SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL SPLIT COMMA STRING INTO MULTIPLE COLUMNS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

michaelhutcheson
Starting Member

2 Posts

Posted - 06/11/2013 :  11:33:04  Show Profile  Reply with Quote
I am newish to SQL (SQL Server 2008) so please excuse any ignorance. I have a table, called 'temp' that contains one Field, (called Field1) with rows and rows of long comma seperated strings, thus:

Temp table
Field1
ABC123,1,Z,some text
ABC456,1,q,some text
ABC789,1,r,some text
ABC111,1,y,some text

I then have a another table, called Detail, with 4 headings. So how do I insert the above from the temp table to the temp table to look like this:

Detail Table
Field1 Field 2 Field 3 Field 4
ABC123 1 Z some text
ABC456 1 q some text
ABC789 1 r some text
ABC111 1 y some text


I will then be using reporting reporting services to report on the detail table. I've been playing about with the below in my SP but it isn't working:

INSERT INTO Detail
WITH Field1 (Field, xmlfields)
AS
(
SELECT Field1 AS Field,
CONVERT(XML,'<Fields><field>'
+ REPLACE(Field1,',', '</field><field>') + '</field></Fields>') AS xmlfields
FROM Temp
)

I've had to put it in a temp table to start as the file is coming from a legacy system whereby some of the fields have double quotes and some do not. SQL specifies that either none or all of the values in a data field are enclosed in quotation marks (""), so I first import into a temp table, then cleanup to remove the " ".

Thank you in advance for any help.


Regards,

Michael

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 06/11/2013 :  11:49:43  Show Profile  Reply with Quote
This article shows a split fuction that you can use the parse your comma separated values in the row:
http://www.sqlservercentral.com/articles/Tally+Table/72993/

Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/11/2013 :  12:14:43  Show Profile  Reply with Quote
may be other ways too but following are the two solution coming into my mind
1) you may need a parser (logic inside a function) to parse each value, and devide it into four columns. example

declare @x varchar(100),@itr smallint
declare @tab table (col1 varchar(10),col2 varchar(10),col3 varchar(10),col4 varchar(10))
set @itr=1
set @x='ABC123,1,Z,some text'
While @itr<5
BEGIN
If @itr=1
INSERT INTO @tab (col1) values (LEFT(@x,Charindex(',',@x)-1))
If @itr=2
update @tab set col2 = LEFT(@x,Charindex(',',@x)-1)
If @itr=3
update @tab set col3 = LEFT(@x,Charindex(',',@x)-1)

If @itr=4
update @tab set col4 = @x

SET @x=Replace(@x,LEFT(@x,Charindex(',',@x)),'')
SEt @itr=@itr+1
END
select * from @tab

2) since you're importing from a lagacy system (in files), why not just read the CSV file straight away using the following sql and dump it into the target table in column format

BULK
INSERT CSVTest -- is the target table name
FROM 'c:\csvtest.txt' -- the file that has the CSV data
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)


Cheers
MIK

Edited by - MIK_2008 on 06/11/2013 12:15:30
Go to Top of Page

michaelhutcheson
Starting Member

2 Posts

Posted - 06/11/2013 :  12:17:13  Show Profile  Reply with Quote
Thanks Mik,

I wil take a look at your first solution. I was unable to perform the second solution as the file is coming from a legacy system whereby some of the fields have double quotes and some do not. SQL specifies that either none or all of the values in a data field are enclosed in quotation marks (""), so I first import into a temp table, then cleanup to remove the " ".
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 06/11/2013 :  13:16:14  Show Profile  Reply with Quote
quote:
Originally posted by michaelhutcheson

Thanks Mik,

I wil take a look at your first solution. I was unable to perform the second solution as the file is coming from a legacy system whereby some of the fields have double quotes and some do not. SQL specifies that either none or all of the values in a data field are enclosed in quotation marks (""), so I first import into a temp table, then cleanup to remove the " ".




I believe you should not be facing any issue using the second method, unless the data have additional comma(s) inside the data unit e.g.
ABC,123,1,Z,SomeText
Or
ABC123,1,Z,Some,Text

As far as the double qoutes or any other (unwanted) character(s), you can remove it simply using the Replace function once the data is imported

Cheers
MIK
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000