Extract Mainframe File

ds2000 · Post by **ds2000** » Tue Jun 17, 2008 12:23 pm

Getting a flat file from mainframe in the following format (fixed length). an have the schema as follows. How to handle this data.

Schema:
---------
type char(20)
code char(10)
effdate date
language char(1)
office smallint
mainoff smallint
prov char(2)

Data:
-----
ADDR_PROV 99 0000000000000000E001070ON
OFF_CODE CMP 0000000000000000E001070ON
OFF_CODE 43A 0000000000000000E001070ON
TRAN_TYPE 1 0000000000000000E001070ON

ray.wurlod · Post by **ray.wurlod** » Tue Jun 17, 2008 6:35 pm

Complex Flat File stage.

shamshad · Post by **shamshad** » Tue Jun 17, 2008 8:10 pm

Design 2 step process.

(1) Step 1: Download the file as "Binary" as only 1 column for the whole
row. For example if everyrow is 800 characters long in total
in transformer/ or FT plug in stage define only 1 column
like getdate=CHAR(800) rather than defining each and every
column individually. Dump the data in a .TXT or dataset.

(2) Step 2: Use the .TXT /dataset as Source and then apply the CFF stage
and here give all column definition of your source.

ds2000 · Post by **ds2000** » Thu Jul 10, 2008 8:31 pm

File is already downloaded and exist in .txt format. Any specific columns format needs to be defined ?

ray.wurlod · Post by **ray.wurlod** » Thu Jul 10, 2008 9:38 pm

You should have said so.
Column widths are stored as "Display width" in the table definition.
Import the table definition of the text file, check the "fixed width" check box, and enter 20,10,8,1,6,6,2 in the column widths field. Edit the date column definition to specify that 00000000 is the representation of NULL (you have to do this because 00000000, which occurs in your data, is not a valid date).