Create schema for a CSV file with different set of records in multiple lines

We face this issue in most of the integration project.

We get Order information in CSV file which can have header,details , shipping etc information in different line in different format.

Developer often face issues in creating a schema for this kind of file.

In this exercise we will try to create a schema for such kind of CSV file and will then try to process it using soa suite file adapter.

I have following set of CSV file

0,4321432143214321,0316,VISA
Rahi,Arpit,Varthur Hobli,Bangalore,KA,12345,5127691108
Rahi,Arpit,Varthur Hobli,Bangalore,KA,12345,5127691108
Standard,ar@test.com
32779,5,4.72,hard drive,Ramson,2008,hard drive Ramson
30421,16,10.72,RAM,Intel,2005,RAM Intel
32861,11,60.72,Chain,alloy,1829,Ring chain alloy

which needs to be processed.

Here if you can review the file closely you can find that there are four distinct records. So while creating the schema for the csv file you have to ensure that your schema should be able to process this file and it will also take care of the repeating elements.

We will see how we can do this.

We will create a file adapter and will use Define native schema format wizard to create a schema for our schema

I will directly jump to native schema builder.

As soon as you will click on this a gui will come up



say next and update the schema name



Say next and select the default option of delimited file as it is a CSV file



select your csv file and validate it is loaded properly



Say next and choose multiple records are of different type otherwise it will process all the record as a single type.



Say next and give some name to the schema



Say next and validate the delimiter and the end of line



Say next and you will observer it is automatically creating a record for you. However the strange part is that it has created 6 record type instead of 7

In my original csv file i have 7 lines however the record created is 6

The reason being second and third record start with same value and in condition value we should have a unique value.

we will just go ahead with the default configuration by Jdeveloper and later we will change it to see how we can modify this or how we can generalize it for other records as well.

you can go to next page, select the individual record and update the names to some logical name



Again say next, it will generate a default schema for you



Say next and finish the wizard.

create a bpel process to receive the file and deploy the process.


Test the service and you will see a successful instance getting created And you will find the correct payload as you were expecting

<Receive1_Read_InputVariable>
-<part xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" name="body">
-<Root-Element xmlns="http://TargetNamespace.com/fileService">
-<RECORD1>
<C2>4321432143214321</C2>
<C3>0316</C3>
<C4>VISA</C4>
</RECORD1>
-<RECORD2>
<C2>Arpit</C2>
<C3>Varthur Hobli</C3>
<C4>Bangalore</C4>
<C5>KA</C5>
<C6>12345</C6>
<C7>5127691108</C7>
</RECORD2>
-<RECORD2>
<C2>Arpit</C2>
<C3>Varthur Hobli</C3>
<C4>Bangalore</C4>
<C5>KA</C5>
<C6>12345</C6>
<C7>5127691108</C7>
</RECORD2>
-<RECORD3>
<C2>ar@test.com</C2>
</RECORD3>
-<RECORD4>
<C2>5</C2>
<C3>4.72</C3>
<C4>hard drive</C4>
<C5>Ramson</C5>
<C6>2008</C6>
<C7>hard drive Ramson</C7>
</RECORD4>
-<RECORD5>
<C2>16</C2>
<C3>10.72</C3>
<C4>RAM</C4>
<C5>Intel</C5>
<C6>2005</C6>
<C7>RAM Intel</C7>
</RECORD5>
-<RECORD6>
<C2>11</C2>
<C3>60.72</C3>
<C4>Chain</C4>
<C5>alloy</C5>
<C6>1829</C6>
<C7>Ring chain alloy</C7>
</RECORD6>
</Root-Element>
</part>
</Receive1_Read_InputVariable>


however there is lot of things that we need to understand here. We can observer that the first value in every record is missing.

We will try to analyse it with the schema that got generated by default

<?xml version = '1.0' encoding = 'UTF-8'?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:nxsd="http://xmlns.oracle.com/pcbpel/nxsd" xmlns:tns="http://TargetNamespace.com/fileService" targetNamespace="http://TargetNamespace.com/fileService" elementFormDefault="qualified" attributeFormDefault="unqualified" nxsd:version="NXSD" nxsd:stream="chars" nxsd:encoding="US-ASCII">


<xsd:element name="Root-Element">
<xsd:complexType>
<xsd:choice minOccurs="1" maxOccurs="unbounded" nxsd:choiceCondition="terminated" nxsd:terminatedBy=",">
<xsd:element name="RECORD1" nxsd:conditionValue="0">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="""/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="RECORD2" nxsd:conditionValue="Rahi">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C3" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C5" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C6" type="xsd:int" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C7" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="""/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="RECORD3" nxsd:conditionValue="Standard">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="C2" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="""/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="RECORD4" nxsd:conditionValue="32779">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="C2" type="xsd:int" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C3" type="xsd:decimal" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C5" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C6" type="xsd:int" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C7" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="""/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="RECORD5" nxsd:conditionValue="30421">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="C2" type="xsd:int" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C3" type="xsd:decimal" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C5" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C6" type="xsd:int" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C7" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="""/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="RECORD6" nxsd:conditionValue="32861">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="C2" type="xsd:int" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C3" type="xsd:decimal" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C4" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C5" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C6" type="xsd:int" nxsd:style="terminated" nxsd:terminatedBy="," nxsd:quotedBy="""/>
<xsd:element name="C7" type="xsd:string" nxsd:style="terminated" nxsd:terminatedBy="${eol}" nxsd:quotedBy="""/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:choice>
</xsd:complexType>
</xsd:element>
</xsd:schema>


You can observe here that for every record element there is a conditional value which is nothing but the first column of the record.
So ideally we were expecting all the records but some how we got the record will one less value.

We will now try to see what all changes we can do to get all the records.