Fill up position numbers

Question

Post reply

Fill up position numbers

Steve Goram

SSC Enthusiast

Points: 111
More actions
October 28, 2013 at 7:27 am

#147993

Hello,
I have imported article data from a text file:
Pos item
---------- --------------------
1 tire
NULL black
NULL 50 $
2 car jack
NULL blue
NULL 35 $
3 screwdriver
NULL red
NULL 3 $
and I want it like this:
Pos item
---------- --------------------
1 tire
1 black
1 50 $
2 car jack
2 blue
2 35 $
3 screwdriver
3 red
3 3 $
Can anyone help ?
Thanks
Steve

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

David Burrows SSC Guru Points: 65138 More actions · Answer 1

How are you importing?

What version of SQL Server are you using?

To make this work properly you will need to import the data into a table with an IDENTITY columns to preserve sequence.

Then there are a few ways to process the data in the way you ask.

Far away is close at hand in the images of elsewhere.
Anon.

Steve Goram SSC Enthusiast Points: 111 More actions · Answer 2

Hi,

I use SQL Server 2005.

I have used Bulk Insert.

I have put an identity column to the table. Now it looks like this:

Pos item SID

---------- -------------------- -----------

1 tire 1

black 2

50 $ 3

2 car jack 4

blue 5

35 $ 6

3 screwdriver 7

red 8

3 $ 9

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Using SQL Server 2005, there's an interesting and really fast way to do it. You should read all the explanation and be sure to understand it before using it. You should test that it works correctly before full implementation.

The explanation is here:

http://www.sqlservercentral.com/articles/T-SQL/68467/

And the example is here:

WITH SampleData(Pos, item, SID) AS(

SELECT 1, 'tire', 1 UNION ALL SELECT

NULL, 'black', 2 UNION ALL SELECT

NULL, '50 $', 3 UNION ALL SELECT

2, 'car jack', 4 UNION ALL SELECT

NULL, 'blue', 5 UNION ALL SELECT

NULL, '35 $', 6 UNION ALL SELECT

3, 'screwdriver', 7 UNION ALL SELECT

NULL, 'red', 8 UNION ALL SELECT

NULL, '3 $', 9)

SELECT *

INTO #Samp

FROM SampleData

CREATE CLUSTERED INDEX SampID ON #Samp(SID)

SELECT *

FROM #Samp

DECLARE @Pos int, @anchor varchar(100)

SELECT TOP 1 @Pos = Pos

FROM #Samp

ORDER BY SID

UPDATE a SET

@Pos = Pos = ISNULL( Pos, @Pos),

@anchor = item

FROM #Samp a WITH (TABLOCKX)

OPTION (MAXDOP 1)

SELECT *

FROM #Samp

DROP TABLE #Samp

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 4

Steve Goram (10/28/2013)
Hello,
I have imported article data from a text file:
Pos item
---------- --------------------
1 tire
NULL black
NULL 50 $
2 car jack
NULL blue
NULL 35 $
3 screwdriver
NULL red
NULL 3 $
and I want it like this:
Pos item
---------- --------------------
1 tire
1 black
1 50 $
2 car jack
2 blue
2 35 $
3 screwdriver
3 red
3 3 $
Can anyone help ?
Thanks
Steve

For the data you posted above, please post what the raw data in your import file looks like because this is definitely the wrong way to do it. Once you post that data from the file, then I'll be able to show you how to greatly simplify this process and properly normalize the data all at the same time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Goram SSC Enthusiast Points: 111 More actions · Answer 5

This is my file (fixed lengths):

item 1 tire

color black

price 50

item 2 car jack

color blue

price 35

item 3 screwdriver

color red

price 3

My idea was to import it to a (temp) table first and copy the position number (which is on position 20 in the item line) to a new column. Then fill up the position numbers to indicate which property (color, price) belongs to pos 1,2,3. Maybe there is an easier way to import the data.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

Steve Goram (10/29/2013)
This is my file (fixed lengths):
item 1 tire
color black
price 50
item 2 car jack
color blue
price 35
item 3 screwdriver
color red
price 3
My idea was to import it to a (temp) table first and copy the position number (which is on position 20 in the item line) to a new column. Then fill up the position numbers to indicate which property (color, price) belongs to pos 1,2,3. Maybe there is an easier way to import the data.

Unless the file has trailing spaces on each line, it's not actually "fixed length". If it does have such trailing spaces, let me know how long that second field is.

We could greatly simplify this process using what I call a "Multi-line Definition" in a BCP format file and it would automatically include the necessary "pivot" to strip out the header information and put all of the information for each item in a correct column. Let me know if you're interested in that and I'll give it a shot for ya after work.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Goram SSC Enthusiast Points: 111 More actions · Answer 7

column/max. length

Pos-Nr 5

item description 50

color 20

price 5

thanks for your help.

David Burrows SSC Guru Points: 65138 More actions · Answer 8

My solution (bet Jeff picks holes in it :Whistling:)

Create a format file containing

9.0

6

1 SQLCHAR 0 5 "" 1 Pos-Nr ""

2 SQLCHAR 0 50 "\r" 2 itemdescription ""

3 SQLCHAR 0 5 "" 0 Dummay1 ""

4 SQLCHAR 0 20 "\r" 3 color ""

5 SQLCHAR 0 5 "" 0 Dummay1 ""

6 SQLCHAR 0 5 "\r" 4 price ""

use the following query

WITH t (N) AS (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3)

SELECTa.[Pos-Nr],

CASE t.N WHEN 1 THEN a.itemdescription WHEN 2 THEN a.color WHEN 3 THEN a.price ELSE '' END AS [Value]

FROM t

CROSS JOIN OPENROWSET(BULK 'textfile', FORMATFILE = 'formatfile') a

ORDER BY a.[Pos-Nr] ASC, t.N ASC

Far away is close at hand in the images of elsewhere.
Anon.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

Steve Goram (10/31/2013)
column/max. length
Pos-Nr 5
item description 50
color 20
price 5
thanks for your help.

Where'd the "Pos-Nr" come from? I don't see it in your test data. Based on this post, I have to ask again, what does the data in the file actually look like because the description above doesn't match any data that you've posted recently.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 10

David Burrows (10/31/2013)
My solution (bet Jeff picks holes in it :Whistling:)
Create a format file containing...

No. You're absolutely on the same track I was going to go with except I might play with the terminators a bit more. The problem is that the op just posted a format that doesn't look like any of the data he posted. You know me... I don't like to post code that I have tested and I can't test because I don't actually know what the op's data actually looks like.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)