Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Fill up position numbers Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 7:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 7:24 AM
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
Post #1508869
Posted Monday, October 28, 2013 8:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:01 AM
Points: 6,954, Visits: 7,077
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.

Post #1508894
Posted Monday, October 28, 2013 8:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 7:24 AM
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

Post #1508939
Posted Monday, October 28, 2013 9:20 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:13 AM
Points: 3,783, Visits: 8,483
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1508959
Posted Monday, October 28, 2013 10:18 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1509014
Posted Tuesday, October 29, 2013 4:16 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 7:24 AM
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.


Post #1509251
Posted Tuesday, October 29, 2013 10:58 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1509474
Posted Thursday, October 31, 2013 7:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 20, 2013 7:24 AM
Points: 5, Visits: 24
column/max. length
Pos-Nr 5
item description 50
color 20
price 5

thanks for your help.


Post #1510180
Posted Thursday, October 31, 2013 8:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:01 AM
Points: 6,954, Visits: 7,077
My solution (bet Jeff picks holes in it )

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.

Post #1510241
Posted Thursday, October 31, 2013 4:02 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1510445
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse