Parent Child records in same output file

• Carlos Chacon

  Newbie

  Points: 2

  More actions

  August 19, 2011 at 7:44 am

  #255895

  I am trying to export Vendor information about Parent and Children data in the same file. This is a requirement of vendor I am sending the file to. I am exporting to a flat file and they would like the file to look like this.

  ParentHeader|Col1|Col2|Col3|

  ChildHeader|Col1|Col2|Col3|Col4|Col5|Col6|Col7|Col8|

  ParentRecord|Col1|Col2|Col3|

  ParentRecord|Col1|Col2|Col3|

  ChildRecord|Col1|Col2|Col3|Col4|Col5|Col6|Col7|Col8|

  ChildRecord|Col1|Col2|Col3|Col4|Col5|Col6|Col7|Col8|

  ChildRecord|Col1|Col2|Col3|Col4|Col5|Col6|Col7|Col8|

  The parent record and the child records are different queries and I can't quite understand how to get two different sets of columns to have the same destination. My attempts have gotten me this far. I can't have the extra 'columns' on the parent records.

  ParentHeader|Col1|Col2|Col3||||||

  ChildHeader|Col1|Col2|Col3|Col4|Col5|Col6|Col7|Col8|

  ParentRecord|Col1|Col2|Col3||||||

  ParentRecord|Col1|Col2|Col3||||||

  ChildRecord|Col1|Col2|Col3|Col4|Col5|Col6|Col7|Col8|

  ChildRecord|Col1|Col2|Col3|Col4|Col5|Col6|Col7|Col8|

  ChildRecord|Col1|Col2|Col3|Col4|Col5|Col6|Col7|Col8|

  Any ideas would be appreciated. Thanks.

• kramaswamy

  SSCoach

  Points: 18181

  More actions

  August 19, 2011 at 8:51 am

  #1370792

  A somewhat ugly and brute-force solution, but which would probably work for you, is to get the output exactly as you've posted, with the extra pipes at the end. Output that to a flat file destination.

  Then, have a raw file source, and read in that file. The file will come in as a single string per line.

  For each line, run a derived column, and strip away the trailing pipes after the last pipe. You could do that with an expression similar to:

  SUBSTRING([YourString], 1, FINDSTRING([YourString], "||", 1))

  Then output that to a new file. Your new file should be in the format you want.

• Carlos Chacon

  Newbie

  Points: 2

  More actions

  August 19, 2011 at 1:50 pm

  #1370963

  Yeah, I was hoping for a little more control over the individual columns, but I think I have resigned to simply exporting the entire row as a single column and the destination has only one column that both sources export to.