Use JCA file adapter to Parse CSV file with master and detailed records

JCA file adapter can parse single record type CSV file easily. It also has limited support to parse mixed record type CSV files. However, it relies on the record data starts with a fixed values.

In my case, I have a CSV file with master-detailed records that look like this:

Master c1, master c2, master c3
 a, b, c
Det c1, det 2, det 3, det 4, det 5
1, 2, 3,4,5
6,7,8,9,0

Since my detailed records do not start with a fixed value (detail row 1 starts with 1, row 2 starts with 6), I cannot use JCA wizard to parse this file directly. Here is how I managed to do it. My solution is to create the two file adapters and parse the same data file twice. First parse the master record, then parse the detailed records.

For master record:

1.      Create a copy of the sample data file, and remove the detailed records

2.      Remove the spaces in the master header row

3.      Generate XSD with JCA adapter native file wizard, select uniform file, use 1^st line as header

4.      After XSD (let’s call it header.xsd) is generated, make these changes in the XSD:

·        nxsd:headerLinesTerminatedBy="${eol}" – make sure it’s like this.

·        nxsd:headerLines="1" – this is a misnomer, it simply means how many lines to skip

·        nxsd:hasHeader="false"     -- sounds contradictory to the line above. But the actual data file has spaces in the header title, this will make the parser skip header line

·        nxsd:dataLines="1"             –  so it only reads 1 line of data

Follow similar steps for detailed-records; remove the master head and records from sample data file, after XSD (call it body.xsd) is generated, make the following changes:

·        nxsd:hasHeader="false" – so it won’t parse header records, because spaces in header cause problems

·        nxsd:headerLines="3" – this will skip 3 lines in the data file

·        nxsd:headerLinesTerminatedBy="${eol}" – make sure the headers line is terminated properly

In the BPEL process, create two JCA file adapters.

The first adapter is called to load (parse) the header record into BPEL, choose header.xsd to when create the adapter. Remember, header.xsd skips the first line of the data file, and parse the 1^st line, since we set dataLines=”1”, it will skip rest of the detailed records.

Also make sure modify the .jca file, so after header record is loaded, do not delete the data file:
  <property name="DeleteFile" value="false"/>

Create the 2^nd file adapter and select body.xsd. Based on the XSD, the 2^nd adapter will skip the first 3 lines (master record header, data and detailed record header.

With this two-pass approach,  you can load both header and detailed records into BPEL.