Multi formatted fixed width file

jdmiceli · Post by **jdmiceli** » Tue Oct 31, 2006 5:00 pm

Hi all,

I have a requirement to generate a multi formated fixed width file from two different formatted files.

Source file 1:
Field1(3) Field2(8) Field3(10) Field4(2) Field5 (2) Field6(5)
POL 20061031 2345623SQ 10 re 25367
Source file 2:
Field1(3) Field2 (10) Field3(2) Field4(8) Field5(4) Field6(5)
INS abcdefghdd 10 agaiskod dtdyt

Target file should look like:

POL200610312345623SQ10re25367
INSabcdefghddagaiskod dtdyt

Note:Source file 1.Field4 and source file2.field3 are key fields.
Source files are initially generated from SQL Server tables.

Any solution for my query will be really helpfull for me.

Thank you in advace.

Regards

ray.wurlod · Post by **ray.wurlod** » Tue Oct 31, 2006 6:56 pm

And the requirement is to use DataStage TX maps?

jdmiceli · Post by **jdmiceli** » Wed Nov 01, 2006 8:15 am

Yes, I have to create the target file through Datastage maps.

Is this possible?

-Regards.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 01, 2006 8:20 am

I don't know, since TX is not my area of expertise. Certainly regular DS jobs could do it. I would imagine that you could separate processing into two streams, one for each format, and bring them together at the end (maybe by writing stream 2 into a temporary file and using an O/S command like cat to append it to the "real" output file).

jvmerc · Post by **jvmerc** » Wed Nov 01, 2006 10:15 am

Do your records need to appear is a specific order; POL, INS, POL, INS and so on or can it be POL, POL, POL, INS...... ?

Can you bypass the files and extract your data directly from the tables?

jdmiceli · Post by **jdmiceli** » Wed Nov 01, 2006 11:08 am

jvmerc wrote:Do your records need to appear is a specific order; POL, INS, POL, INS and so on or can it be POL, POL, POL, INS...... ?

Can you bypass the files and extract your data directly from the tables?

I'm working on normal Datastage server edition not the Datastage TX.
I appologize for the confusion.

It is not madatory to exract data to files and then to TGT file. I can also extract data directly from tables.

Also the order of the records should be
POL
INS
POL
INS

-Regards

ray.wurlod · Post by **ray.wurlod** » Wed Nov 01, 2006 12:49 pm

In a server job the easiest approach is to build the entire record as a single field (using stage variables as needed). The Fmt() function is useful for right padding. Output metadata is a single VarChar column (since your row lengths are 30 and 32 characters respectively).

jdmiceli · Post by **jdmiceli** » Mon Nov 20, 2006 8:24 pm

ray.wurlod wrote:In a server job the easiest approach is to build the entire record as a single field (using stage variables as needed). The Fmt() function is useful for right padding. Output metadata is a single VarChar column (since your row lengths are 30 and 32 characters respectively).

I appreciate everyone's input on this. They changed the requirements adding a third file as well, so I ended up dealing with Policy, Insured and Vehicle information. The Policy info acts as the driving factor for the other files, and we had to take into account ADD and DEL records for daily processing. The data in the files comes in no order and we have no control over what comes when other than we know the files are related.

Ultimately, due to time constrictions, I chose to write a solution in Perl to combine the files, ordered and grouped by Policy stuff and related records from the other two files by ADD/DEL to create a delimited output file.

Thanks for the input,

Bestest!

jvmerc · Post by **jvmerc** » Thu Nov 23, 2006 1:23 am

Remeber this is the DSTX forum so to avoid confusion......

DSXchange