October 28, 2013 at 7:27 am
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
October 28, 2013 at 8:03 am
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.
October 28, 2013 at 8:55 am
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
October 28, 2013 at 9:20 am
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
October 28, 2013 at 10:18 am
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
Change is inevitable... Change for the better is not.
October 29, 2013 at 4:16 am
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.
October 29, 2013 at 10:58 am
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
Change is inevitable... Change for the better is not.
October 31, 2013 at 7:46 am
column/max. length
Pos-Nr 5
item description 50
color 20
price 5
thanks for your help.
October 31, 2013 at 8:57 am
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.
October 31, 2013 at 4:02 pm
Steve Goram (10/31/2013)
column/max. lengthPos-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
Change is inevitable... Change for the better is not.
October 31, 2013 at 4:05 pm
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
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply