Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fill up position numbers


Fill up position numbers

Author
Message
Steve Goram
Steve Goram
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 24
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
David Burrows
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7965 Visits: 9417
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
Steve Goram
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 24
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
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18130
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45076 Visits: 39909
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Goram
Steve Goram
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 24
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45076 Visits: 39909
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steve Goram
Steve Goram
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 24
column/max. length
Pos-Nr 5
item description 50
color 20
price 5

thanks for your help.
David Burrows
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7965 Visits: 9417
My solution (bet Jeff picks holes in it :WhistlingSmile

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)
SELECT a.[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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45076 Visits: 39909
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search