Skip to Content
0
Apr 21, 2009 at 03:14 PM

File Adapter content conversion delimited/possitional file format.

22 Views

Hi,

I have the following file to JDBC scenario, but having some issues with the file content conversion due to the file structure.

Example:

=======

000038A020301

000038A020101=AA1=AC1=AD=AG1=AH1=AI1=AK3049572=BN01 =BOMETLSS_ML_STD_30A7

000038A020200=AA96=AB001=AC17000.000=AD1200=AF13021537=AE=AG8005992427=AH10

OLRENDZZZZ

Example 2:

========

000040A020301

000040A020101=AA1=AC1=AD=AG1=AH1=AI1=AK3049570=BN01 =BOMETLSS_ML_STD_30A7

000040A020200=AA96=AB001=AC17000.000=AD1200=AF13021537=AE=AG8005992425=AH10

000041A020301

000041A020101=AA1=AC1=AD=AG1=AH1=AI1=AK3049571=BN01 =BOMETLSS_ML_STD_30A7

000041A020200=AA96=AB001=AC17000.000=AD1200=AF13021537=AE=AG8005992426=AH10

000042A020301

000042A020101=AA1=AC1=AD=AG1=AH1=AI1=AK3049572=BN01 =BOMETLSS_ML_STD_30A7

000042A020200=AA96=AB001=AC17000.000=AD1200=AF13021537=AE=AG8005992427=AH10

000043A020301

000043A020104=AA1=AC1=AD200619=AG1=AH1=AI1=AK3049568=BN01

000043A020200=AA73=AB001=AC3700.000=AD1300=AF13047285=AE200619=AG8005992423=AH10

000043A020200=AA73=AB002=AC5500.000=AD1300=AF13047285=AE200619=AG8005992423=AH10

000043A020200=AA73=AB003=AC1800.000=AD1300=AF13047285=AE200619=AG8005992423=AH10

000043A020200=AA73=AB004=AC5000.000=AD1300=AF13047285=AE200619=AG8005992423=AH10

000044A020301

000044A020104=AA1=AC1=AD200619=AG1=AH1=AI1=AK3049569=BN01

000044A020200=AA73=AB001=AC3700.000=AD1300=AF10008536=AE200619=AG8005992424=AH10

000044A020200=AA73=AB002=AC5500.000=AD1300=AF10008536=AE200619=AG8005992424=AH10

000044A020200=AA73=AB003=AC2500.000=AD1300=AF10008536=AE200619=AG8005992424=AH10

000044A020200=AA73=AB004=AC5000.000=AD1300=AF10008536=AE200619=AG8005992424=AH10

OLRENDZZZZ

Example Explained:

==============

Position 1-9 is a "Transactional number".

Position 10-11 is "Record type".

Position 12-13 is "Line Item count".

Four record types exist:

03 = Location header

01 = Transactional Header

02 = Line Item

OLRENDZZZZ = EoF marker.

The equal sign "=" is a field separator/delimiter.

In each delimited field, after the first equal sign in the record, the first two characters represent a field qualifier/field name tag/identifier and there only the data begins until the following delimiter.

Each record is ended in a "CLRF"/'nl'.

The file is build up, but not locked and only completed until the EoF marker "OLRENDZZZZ" is inserted by the application on the last record of the file.

My solution so far:

=============

Record Structure: row,*

Record Sequence: Ascending

row.fieldNames: field1,field2,field3,ect.......

row.fieldSeparator: =

row.endSeparator: 'nl'

row.keyFieldInStructure: ignore

ignoreRecordsetName: true

This brings the file into the integation server as xml as follow:

============================================

<?xml version="1.0" encoding="utf-8"?>

<ns:SAPtoFuelFACS xmlns:ns="urn:engenoil-com:i_fuel_facs_sap">

<row>

<field1>000038A020301</field1>

</row>

<row>

<field1>000038A020101</field1>

<field2>AA1</field2>

<field3>AC1</field3>

<field4>AD</field4>

<field5>AG1</field5>

<field6>AH1</field6>

<field7>AI1</field7>

<field8>AK3049572</field8>

<field9>BN01</field9>

<field10>BOMETLSS_ML_STD_30A7</field10>

<field11>BP0003049572</field11>

</row>

<row>

<field1>000038A020200</field1>

<field2>AA96</field2>

<field3>AB001</field3>

<field4>AC17000.000</field4>

<field5>AD1200</field5>

<field6>AF13021537</field6>

<field7>AE</field7>

<field8>AG8005992427</field8>

<field9>AH10</field9>

</row>

<row>

<field1>OLRENDZZZZ</field1>

</row>

</ns:SAPtoFuelFACS>

So far, so good.

The problem I am having is that I have to check for the EoF marker "OLRENDZZZZ" to be present before picking up the file, else the file is not completed.

I have tried a script to rename files in msg pre-processing in the channel, but the problem is the file channel has to be triggered and the original file mask is necessary for this, but then this mask is a valid pickup file mask. So to me it seems the only way is to do this is during the content conversion process as the files not matching the file criteria, where a EoF "OLRENDZZZZ" definition is not present, will not be picked up and be ignored until it is present or totally independent with a batch job.

If someone has a more elegant way to solve this problem with just using the file channel configuration where every thing is pretty much apparent, I would greatly appreciate it if you could assist.

Regards

Willie Hugo