Parent Child records in same output file

Question

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.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

kramaswamy SSCoach Points: 18181 More actions · Answer 1

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 · Answer 2

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.