how to load multiple header ,detail and footer records into a file

[Mkrishna]

hi, iam having a data in a file with multi schema like header and detail&footer now

1.how can i load this data into a single file

see my input (Positional file)

HA00000000051070UPAULINA             Wdavid                 john

DA00000000051070S000009003RESSupply    01012015Summer Charge   240 kwh 200 

DA00000000051070S000009003RESSupply    01012015Summer Charge   240 kwh 220

FA00000000051070S000009003M000004576ELECT1 010120150131201531

HA00000000155650UPAULINA             Wraj                 warner

DA00000000155650S000009003RESSupply    01012015Summer Charge   140 kwh 110 

DA00000000155650000009003RESSupply    01012015Summer Charge   340 kwh 300

FA00000000155650S000009003M000004576ELECT1 010120150131201531

like data is for more than one customer with multiple header,detail&footer 

[Mkrishna]

i have to load a file with header detail and footer record in to a single file but when we load the records separate sequence number should be created for header and detail record, how can we achieve this.

example source

BIA00000000051070UPAULINA
SVA00000000051070S000009003RESFee
SVA00000000051070S000009003RESSupply
SVA00000000051070S000009003RESSupply
MTA00000000051070S000009003M000004576EL
BIA00000000091687UJACKSON
SVA00000000091687S000001365RESFee
SVA00000000091687S000001365RESSupply
SVA00000000091687S000001365RESSupply
MTA00000000091687S000001365M000001772E

Here BI is header and sva is detail mat is footer records

Req: i need to generate two sequence no as  below as

 seaqno

    1   BIA00000000051070UPAULINA
    1 1SVA00000000051070S000009003RESFee
    1 2 SVA00000000051070S000009003RESSupply
    1 3 SVA00000000051070S000009003RESSupply
    1 4 MTA00000000051070S000009003M000004576EL
    2    BIA00000000091687UJACKSON
    2 1 SVA00000000091687S000001365RESFee
    2 2 SVA00000000091687S000001365RESSupply
    2 3 SVA00000000091687S000001365RESSupply
    2 4 MTA00000000091687S000001365M000001772E

[Anonymous]

Define the schema as  as 1,* in tFileinputPositional as from the data given it appears that 1st character signifies which type of row we are currently on using this you will avoid the incorrect spacing issue that arises when you append the file from multiple sources , hence you will have only 2 columns 1st being the rowtype character, rest being the rest of row if there are any values that need to be modified use substring() method to pick out the value from the file.

[Sid3]

which is the header,footer and detail in this example?

what do you mean by multi schema here, you have 3 data sets in the source file?

Please explain in detail.

[Mkrishna]

Hi aranax thank you for answering,

 can you please explain me how to achieve this.......................... becoz here Header fields (H)rows are different lenght and Detail (D) fields are different length and footer fields are different length.

[Anonymous]

TO start off, the tFileInputPositional pattern should be 1,*  and set the the length of the column as the length of row,.

eg.g : length of header is 80 , length of Record is 94

setting the pattern to 1,* will separate the entire string into two parts:

 H,"rest of header"  -- (1,93 [79 in use and rest white space])

R,"rest of record"- (1,93)

using this data will be ingested you use the trim function to remove the white space that will come in header and footer.

There may be other solutions also but this is what came to mind in this case

[Mkrishna]

Hi sid4u

see my input it is positional file and it has header detail and footer rows for multiple records, now i want to load into a file

1.how can i load this data into a single file

see my input (Positional file)

HA00000000051070UPAULINA             Wdavid                 john

DA00000000051070S000009003RESSupply    01012015Summer Charge   240 kwh 200 

DA00000000051070S000009003RESSupply    01012015Summer Charge   240 kwh 220

FA00000000051070S000009003M000004576ELECT1 010120150131201531

HA00000000155650UPAULINA             Wraj                 warner

DA00000000155650S000009003RESSupply    01012015Summer Charge   140 kwh 110 

DA00000000155650000009003RESSupply    01012015Summer Charge   340 kwh 300

FA00000000155650S000009003M000004576ELECT1 010120150131201531

here H is header D is detail and F is for footer field lenght is varying for header and detail and footer

[vboppudi]

Hi Krishna,

You want to load only detail records into single file?

Regards,

[vboppudi]

If you are expecting below output, you can achieve this by using tFileInputMSPositional component.

Starting job job_PositionMultiSchema at 07:52 25/04/2017.

[statistics] connecting to socket on port 3945
[statistics] connected
.----------------------------------------------------------------.
| Header |
|=--------------------------------------------------------------=|
|Data |
|=--------------------------------------------------------------=|
|HA00000000051070UPAULINA Wdavid john|
|HA00000000155650UPAULINA Wraj warner|
'----------------------------------------------------------------'

.--------------------------------------------------------------------------.
| Detail |
|=------------------------------------------------------------------------=|
|Data |
|=------------------------------------------------------------------------=|
|DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 200|
|DA00000000051070S000009003RESSupply 01012015Summer Charge 240 kwh 220|
|DA00000000155650S000009003RESSupply 01012015Summer Charge 140 kwh 110|
|DA00000000155650000009003RESSupply 01012015Summer Charge 340 kwh 300 |
'--------------------------------------------------------------------------'

.-------------------------------------------------------------.
| Footer |
|=-----------------------------------------------------------=|
|Data |
|=-----------------------------------------------------------=|
|FA00000000051070S000009003M000004576ELECT1 010120150131201531|
|FA00000000155650S000009003M000004576ELECT1 010120150131201531|
'-------------------------------------------------------------'

[statistics] disconnected
Job job_PositionMultiSchema ended at 07:52 25/04/2017. [exit code=0]

Regards,

[Mkrishna]

Hi Vboppudi,

No, i want to load all the header and detail and footer records into a single file

Problem is :

   1. header fields are different length , detail fields are different length and footer is also diff length.

  2. main thing is input file is positional file , not a delimited one

  3. Header  may be it has 50 fields , detail it has 20 fields, footer it has 25 fields (fields are different here)