Parsing a summary / detail flat file

• Jason Wolfkill

  SSCrazy Eights

  Points: 9832

  More actions

  May 13, 2013 at 12:52 pm

  #1614745

  Brandie Tarvin (5/13/2013)

  ---

  wolfkillj (5/13/2013)

  ---

  I couldn't use your sample data because the text file formatting seemed to be inconsistent from line to line

  Exactly. If you check my first post, you'll see where I made that point. That's why we import all rows into a single column in the staging table to begin with.

  I need a solution that addresses my current setup, including the inconsistent data.

  I went back and re-read your OP. If I understand you correctly, rows of different types (vendor and detail) have different data and so can't be parsed into the same set of columns. But you CAN reliably parse out the record ID value, leaving the rest of the row in a single column, and you CAN parse each type of row into its particular column set, right? If so, you can use the code I posted as a basis for identifying the rows that belong together (e.g., a record ID = 2 row with its associated record ID = 3 row(s)) and assigning them a single identifying number. Then, you can split the rows out into the different tables based on their record ID values (vendors and detail), using that identifying number as a foreign key. Does that get you where you need to go, or am I overlooking something.

  I didn't study WayneS's solution in detail, but it looks like he took a very similar approach to keeping together the groups of rows that belong together.

  Jason Wolfkill

• Evil Kraig F

  SSC Guru

  Points: 100851

  More actions

  May 13, 2013 at 4:02 pm

  #1614805

  Brandie Tarvin (5/13/2013)

  ---

  Thanks everyone for the responses. We're going to start checking them now. I'm glad I asked the question as this was not the direction I had been heading in with my thoughts.

  Brandie, in SSIS are you familiar with using a Synchronous Tranformation Script Component? They are a godsend for issues like this where you're dependant on order for transformation. Also, plus side, they aren't stream interrupts.

  Here's the short form: You fire up a script component midstream and add a column with GUIDs (easiest). Set the an internal variable to the first guid and then reset it on (1) lines. Carry through the old GUID on any other line.

  Dump to database staging table.

  Let me know if you need me to walk you through the code/setup. I'm hoping you're okay as unless you're familiar with them Script Transforms require a lot of screenshots. 😉

  EDIT: Side note: If you want to get fancy, said script transform can also split the string into components based on values and send it to multiple streams. Might be overkill on the first pass, but that's how I usually set them up.

  ---

  - 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[/url] | Forum Netiquette
  For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
  Twitter: @AnyWayDBA

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  May 13, 2013 at 4:33 pm

  #1614809

  Craig's idea sounds great!

  Before I saw it, I came up with this , which is similar but different to Wayne's method for tying the rows together...

  Just including it as an option, but probably Craig's SSIS magic is much better as this does some horrible sorting operations :sick:

  ;with magic as

  (

  select

  st.ID

  , st.Extract_Record

  , st.UNID

  , case left(st.Extract_Record,1)

  when '2' then rank() over(partition by left(st.Extract_Record,1) order by st.id)

  else row_number() over(order by st.id) - rank() over(partition by left(st.Extract_Record,1) order by st.id)

  end AS new_ID

  from staging AS st

  where st.Extract_Record like '[23]%'

  )

  update magic

  set unid = new_ID

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]