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

Setting a Primary key to subsequential Data (Header - Line) Expand / Collapse
Author
Message
Posted Friday, September 14, 2012 2:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 20, 2013 1:59 AM
Points: 36, Visits: 273
Thank you for the Help. The thing is that I am not allowed to use SSIS. Just Sql and executables are allowed... I know thats really silly but what can I do? I could call the SSIS through the sql command but I would probably get fired the next day . But you have give me a good Idea to improve the simple vb.code that I had wrote. I will everytime when I find the Header Identifier create a new Guid instead to increase my integer!!! Thanks, thats a first step to improve the whole thing!!


Dim strFileName() As String
strFileName = IO.File.ReadAllLines(filename, Encoding.GetEncoding(1253))

Dim headerIdent as string = "H"
Dim g As Guid
For Each myLine In strFileName
If Not myLine = Chr(endCharacter) Then
If myLine.Substring(0, 2) = headerIdent Then

g = Guid.NewGuid()

End If
tempStr &= g & ";" & myLine.ToString & Environment.NewLine

End If
Next

Post #1359670
Posted Friday, September 14, 2012 3:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007

The thing is that I am not allowed to use SSIS. Just Sql and executables are allowed


Sheesh!!! Some companies have the strangest rules/regulations about things. There is a tool that you already own that is designed to do this complicated task that you are trying to deal with but the politics get in the way.

It seems you are well on your way of making the best of a painful situation. Post back if you have any issues.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1359677
Posted Friday, September 14, 2012 3:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 20, 2013 1:59 AM
Points: 36, Visits: 273
Ok, I am really thankful for your Help. My Company has created a ETL tool that we must use for import/Export Data. Sometimes I am allowed to use SSIS to create Excels or to read XML Data etc. Thank god!

Goodnight everyone (its Midnight here in Europe and I am so tired from work...)
and thanks again
Post #1359688
Posted Friday, September 14, 2012 3:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
rena24 (9/14/2012)
Ok, I am really thankful for your Help. My Company has created a ETL tool that we must use for import/Export Data. Sometimes I am allowed to use SSIS to create Excels or to read XML Data etc. Thank god!

Goodnight everyone (its Midnight here in Europe and I am so tired from work...)
and thanks again


You are very welcome!!! Glad we could help...now go get some sleep!!!


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1359691
Posted Tuesday, September 18, 2012 1:29 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: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
Now I'm fairly sure someone is going to think that I'm crazy and perhaps I am, but why not use XML for this?

Let's set up the incoming data just slightly differently:

DECLARE @t TABLE (ItemID INT, strcol VARCHAR(100))

INSERT INTO @t
SELECT 222, 'H;123442;23%;78.99;52000990889'
UNION ALL SELECT 222, 'L;socks;blue;100'
UNION ALL SELECT 222, 'L;socks;red;200'
UNION ALL SELECT 223, 'H;123443;23%;500.99;52000990111'
UNION ALL SELECT 223, 'L;skirt;blue;100'
UNION ALL SELECT 223, 'L;pants;red;200'


Then reformat the delimited strings into something that can be CAST to XML datatype, and put this into a temporary table (or table variable):

DECLARE @x TABLE (ItemID INT, xmlcol XML)

;WITH CreateXML AS (
SELECT ItemID, strcol
,xmlcol=CASE SUBSTRING(strcol, 1, 1)
WHEN 'H' THEN '<h>' + STUFF(deliveryPlace + '</deliveryPlace></h>', 1, 1, '')
ELSE '<d>' + STUFF(qty + '</qty></d>', 1, 1, '') END
,rn=ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY (SELECT NULL))
FROM @t
CROSS APPLY (
SELECT STUFF(strcol, CHARINDEX(';', strcol), 1, '<inv_number>')) a(inv_number)
CROSS APPLY (
SELECT STUFF(inv_number, CHARINDEX(';', inv_number), 1, '</inv_number><vat>')) b(vat)
CROSS APPLY (
SELECT STUFF(vat, CHARINDEX(';', vat), 1, '</vat><amount>')) c(amount)
CROSS APPLY (
SELECT STUFF(amount, CHARINDEX(';', amount), 1, '</amount><deliveryPlace>')) d(deliveryPlace)
CROSS APPLY (
SELECT STUFF(strcol, CHARINDEX(';', strcol), 1, '<itemDescription>')) e(itemDescription)
CROSS APPLY (
SELECT STUFF(itemDescription, CHARINDEX(';', itemDescription), 1, '</itemDescription><color>')) f(color)
CROSS APPLY (
SELECT STUFF(color, CHARINDEX(';', color), 1, '</color><qty>')) g(qty)
),
CreateXML2 AS (
SELECT ItemID
,xmlcol=CAST((
SELECT CASE WHEN SUBSTRING(xmlcol, 2, 1) = 'h' THEN '<ItemID>' + CAST(ItemID AS VARCHAR(5)) + '</ItemID> ' ELSE '' END +
xmlcol
FROM CreateXML b
WHERE a.ItemID = b.ItemID
ORDER BY rn
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)') AS XML)
FROM CreateXML a
GROUP BY ItemID
)
INSERT INTO @x
SELECT ItemID, xmlcol
FROM CreateXML2


I know, I know. It looks pretty ugly. But now getting at the data is relatively simple:

-- Extract headers
SELECT x.row.value('(.)[1]', 'INT') AS ItemID
,x.row.value('(/h/inv_number)[1]', 'VARCHAR(50)') AS inv_number
,x.row.value('(/h/vat)[1]', 'VARCHAR(50)') AS vat
,x.row.value('(/h/amount)[1]', 'MONEY') AS amount
,x.row.value('(/h/deliveryPlace)[1]', 'VARCHAR(50)') AS deliveryPlace
FROM @x
CROSS APPLY xmlcol.nodes('/ItemID') AS x(row)

-- Extract details
SELECT y.row.value('(.)[1]', 'VARCHAR(50)') AS ItemID
,x.row.value('(/h/inv_number)[1]', 'VARCHAR(50)') AS inv_number
,x.row.value('(./itemDescription)[1]', 'VARCHAR(50)') AS itemDescription
,x.row.value('(./color)[1]', 'VARCHAR(50)') AS color
,x.row.value('(./qty)[1]', 'INT') AS qty
FROM @x
CROSS APPLY xmlcol.nodes('/d') AS x(row)
CROSS APPLY xmlcol.nodes('/ItemID') AS y(row)


Jeez, Louise! What was I thinking!

I must have too much spare time on my hands.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1360595
Posted Tuesday, September 18, 2012 2:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 20, 2013 1:59 AM
Points: 36, Visits: 273
Wow!!! Thats really cool (You are cool )!! I will try it now!! Thaaaaaaaaaank you very much!!!
It really works and I find this solution so brilliant. I know that sql can do anything and the XML object is really cool!!! Thank you again and you are really amazing!!! I love databases and sql and I really hope that one day I will be so good as you!!!
Post #1360618
Posted Tuesday, September 18, 2012 1:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 3:16 PM
Points: 5,986, Visits: 6,931
dwain.c (9/18/2012)
Now I'm fairly sure someone is going to think that I'm crazy and perhaps I am, but why not use XML for this?

Let's set up the incoming data just slightly differently:

Jeez, Louise! What was I thinking!

I must have too much spare time on my hands.


Dwain, that's pretty cool, but a question. How do you confirm you're getting the information out of the table in the same order it was in the file? Due to the lack of confirmed ordering, I fear this might be dangerous.

HOWEVER... errr, no, that's right, no SSIS. BUT, you could deliver the fully built XML from your custom code to SQL, and then run with Dwain's solution... which I admit is pretty frickin' neat.

Also, you can use XML as a datatype for a proc, so you would simply call the proc with the code-constructed XML and go from there.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1360996
Posted Tuesday, September 18, 2012 1:39 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 11:33 PM
Points: 1,789, Visits: 1,013
the way i see it there is aprimary key if you split the header from the lists
for examples all rows which start with H would go to a table Header and the one starting with L go to lists
the a simply identity/checksum column or a merger statement shud be good enough to ensure no duplicates.


Jayanth Kurup
Post #1361001
Posted Tuesday, September 18, 2012 1:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 20, 2013 1:59 AM
Points: 36, Visits: 273
Hallo again!!
You have right. I must first run the vb code to get the ref_number for each raw and then I can run the cool-xml code!! Without the generated ref_number the xml code doesnt work. As an sql freak I am very excited with this xml code, its really a new way!!! Thanks again!!

And I dont have in the first place a ref_number. My raw data is-->

SELECT 'H;123442;23%;78.99;52000990889'
UNION ALL SELECT 'L;socks;blue;100'
UNION ALL SELECT 'L;socks;red;200'
UNION ALL SELECT 'H;123443;23%;500.99;52000990111'
UNION ALL SELECT 'L;skirt;blue;100'
UNION ALL SELECT 'L;pants;red;200'
Post #1361002
Posted Tuesday, September 18, 2012 5:08 PM


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: 2 days ago @ 6:03 PM
Points: 3,590, Visits: 5,098
Evil Kraig F (9/18/2012)
dwain.c (9/18/2012)
Now I'm fairly sure someone is going to think that I'm crazy and perhaps I am, but why not use XML for this?

Let's set up the incoming data just slightly differently:

Jeez, Louise! What was I thinking!

I must have too much spare time on my hands.


Dwain, that's pretty cool, but a question. How do you confirm you're getting the information out of the table in the same order it was in the file? Due to the lack of confirmed ordering, I fear this might be dangerous.

HOWEVER... errr, no, that's right, no SSIS. BUT, you could deliver the fully built XML from your custom code to SQL, and then run with Dwain's solution... which I admit is pretty frickin' neat.

Also, you can use XML as a datatype for a proc, so you would simply call the proc with the code-constructed XML and go from there.


You've gotta have the reference number for sure. I do believe you need to be sure the H record comes before the D records also. Ordering of the D records is also not confirmed because there's no detail line number, however there is a placeholder in the code for where one could be applied (look for ORDER BY (SELECT NULL)).

Glad you all found my attempt inteesting at least.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1361059
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse