Column has multiple values - SQL Server Forums

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 2000 Forums

Import/Export (DTS) and Replication (2000)

Column has multiple values

Author

Topic

novak84
Starting Member

6 Posts

Posted - 2010-06-08 : 11:22:44

Hello,

My first post on here, i'm a novice......

I am importing a csv file into SQL 2000 and one of the columns has multiple values that is causing me problems.

Here is an example of the csv file.

BNum, CNum, PNum
1001, 4332, 1 3 34 52,
1002, 4532, 1,
1003, 6543, 5,
1006, 9876, 10 23 324 00342,

The column with the problem is the "PNum".

If it has 1 value everything is ok. but if it has multiple values in the column i am unsure how to import this.

I need one value per "PNum" column so would like to create a new row with the same BNum and CNum put with one value in the PNum field.

Does that make sense?

Thanks in advance for any pointers.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-08 : 11:28:45

My first approach would be to ask the creator of the file to do that for you

No, you're never too old to Yak'n'Roll if you're too young to die.

novak84
Starting Member

6 Posts

Posted - 2010-06-08 : 12:10:13

quote:
Originally posted by webfred

My first approach would be to ask the creator of the file to do that for you [:)

That is not an option.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-09 : 03:15:39

You need to remove the last comma

Madhivanan

Failing to plan is Planning to fail

novak84
Starting Member

6 Posts

Posted - 2010-06-09 : 07:24:12

quote:
Originally posted by madhivanan

You need to remove the last comma

Madhivanan

Failing to plan is Planning to fail

novak84
Starting Member

6 Posts

Posted - 2010-06-09 : 07:24:51

quote:
Originally posted by madhivanan

You need to remove the last comma

Madhivanan

Failing to plan is Planning to fail

What?????????????

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-09 : 08:40:22

quote:
Originally posted by novak84

quote:
Originally posted by madhivanan

You need to remove the last comma

Madhivanan

Failing to plan is Planning to fail

What?????????????

From 1 3 34 52,
remove a comma

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-09 : 09:42:02

Madhi means: a line in a csv file should not end with a field delimiter.
So are you sure that a line in your file looks like this:
1006, 9876, 10 23 324 00342,

No, you're never too old to Yak'n'Roll if you're too young to die.

novak84
Starting Member

6 Posts

Posted - 2010-06-10 : 12:28:14

Obviously that is my typo error, that is not my problem, as per original post the problem is having multiple values in one line, i would like a new line for each value that is in PNum

novak84
Starting Member

6 Posts

Posted - 2010-06-10 : 12:29:10

"the problem is having multiple values in one column, i would like a new line for each value that is in PNum"

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 01:47:18

[code]
-- First we need a function to split the values in PNUM
CREATE FUNCTION dbo.fnParseArray (@array VARCHAR(max),@separator CHAR(1))
RETURNS @T Table (occ int,col1 varchar(50))
AS
BEGIN
DECLARE @separator_position INT
DECLARE @array_value VARCHAR(max)
declare @occurence int =1
if (left(@array,1)=@separator)
begin
set @array=stuff(@array,1,1,'')
end
if (right(@array,1)<>@separator)
begin
SET @array = @array + @separator
end
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @separator + '%', @array) <> 0
BEGIN
SELECT @separator_position = PATINDEX('%' + @separator + '%',@array)
SELECT @array_value = LEFT(@array, @separator_position - 1)
INSERT into @T VALUES (@occurence,@array_value)
set @occurence = @occurence + 1
SELECT @array = STUFF(@array, 1, @separator_position, '')
END
RETURN
END

-- Now we need your sample data in a table variable to have a test
declare @yak table(BNUM int, CNUM int, PNUM varchar(255))
insert @yak
select 1001, 4332, '1 3 34 52' union all
select 1002, 4532, '1' union all
select 1003, 6543, '5' union all
select 1006, 9876, '10 23 324 00342'

-- show sample data
select * from @yak

-- the solution
-- Assuming the csv file is imported to a staging table
-- where you can work on to put the data into the destination table
select
t1.BNUM,
t1.CNUM,
t2.col1 as PNUM
from @yak t1
cross apply dbo.fnParseArray(PNUM,' ') t2
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-11 : 02:38:46

Oh sorry now I see it is SQL Server 2000.
My solution works only with 2005 or higher.