BTP CPI - CSV to XML Converter escaping special characters.

[Nijith]

How can I ignore special character in a csv file when using CSV to XML converter component in CPI

E.g. I have a csv file as follows:

EmpId,Name,Address

1,Noel,"Main Street 1,Country Road "

2,Joel,"Main Street 2,Country Road " And when I process the data using the CPI converter it splitting the address into 2 .So is there a way I can escape the , inside the address and get the address as 1 after converting to XML

[bdBais13 · Accepted solution]

I've tested this object with last version:

Input:

Output:

Schema Used:

Object Version used:

Is it similar to your test?

[bdBais13 · Accepted solution]

for xml to xsd I've used: https://www.liquid-technologies.com/online-xml-to-xsd-converter

[Nijith]

bellizia_capgemini

Thanks for sharing the groovy script .I was aware of the groovy script approach but was trying to use out of the box converter and see. Thanks a ton once again

[bdBais13]

Understood, good to know this problem, probably they didn't "trim" information between separators because is plausible to put " like 1, "hello!" said Tim in the park, poem

in this case they can't understand that apos after space.

Alternative solution is to use groovy to create your xml from csv.

In this script it "correct csv" so you don't need to have a xsd and change it everytime somebody make errors or (on Concur hell case), every line has his field structure different each other... this script normalize number of field and procuce an xml with a generico FIELDxx name with xx a counter from columns.

import com.sap.gateway.ip.core.customdev.util.Message;
import java.util.HashMap;
def Message processData(Message message) {
    //Body
    def testo = message.getBody(java.lang.String);

    def newbody = ""
    def maxCount = 0
    def myxml = "<?xml version='1.0' encoding='UTF-8'?>\r\n\t<root>\r\n"
    def delimeter = "|"
    def regexp_delimeter = /\|/
    
    testo.readLines().each {
        if (it.count(delimeter) > maxCount) { maxCount = it.count(delimeter) }
    }
    
    testo.readLines().each {
        empty = ''
         
       if (it.count(delimeter) < maxCount && it.length() > 0){
            for (i = 0; i <= maxCount-it.count(delimeter)-1; i++) {
                empty = empty + delimeter
            }
            result = it+empty
            newbody = newbody+result+"\r\n"
        } else {
            newbody = newbody+it+"\r\n";
        }
    }
    
    newbody.readLines().each {
      str = it.split( regexp_delimeter, -1 );
      Integer i = 0;   
      row = "";
      myxml = myxml + "\t\t<row>\r\n";
      for( String values : str ) {
             i = i+1;
             fieldopen = "\t\t\t<FIELD" + i.toString() +">";
             fieldclose = "</FIELD" + i.toString() +">\r\n";
             row = fieldopen + values +fieldclose;
             myxml = myxml + row;
      }
     myxml = myxml + "\t\t</row>\r\n";
    }
    myxml = myxml + "\t</root>\r\n";
    message.setBody(myxml);
    return message;
}

For example:

122,hello,1,2,3

144,123

222,my line is too long, with, a, lot, of, fields

result it's like:

<root>

<row>

<FIELD1>122</FIELD1>

<FIELD2>hello</FIELD2>

<FIELD3>1</FIELD3>

<FIELD4>2</FIELD4>

<FIELD5>3</FIELD5>

<FIELD6/>

<FIELD7/>

</row>

<row>

<FIELD1>144</FIELD1>

<FIELD2>122</FIELD2>

<FIELD3/>

<FIELD4/>

<FIELD5/>

<FIELD6/>

<FIELD7/>

</row>

<row>

<FIELD1>222</FIELD1>

<FIELD2>my line is too long</FIELD2>

<FIELD3>with</FIELD3>

<FIELD4>a</FIELD4>

<FIELD5>lot</FIELD5>

<FIELD6>of</FIELD6>

<FIELD7>fields</FIELD7>

</row>

</root>

[bdBais13]

This workaround wrote 2 years ago resolve a missing feature of xsd implementation for Integration Suite (CPI).

You can create ad adaptive xsd with different templates for different structure with same name, but it's not accepted by SAP implementation, so I resolved in this way.

[Nijith]

Hi bellizia_capgemini ,

Thank you so much for your prompt reply.I was able to figure out what the issue was ,it was because of the white space that I had in my data because of which it was not working as expected ,once I removed them it worked .

Sample data with spaces :

EmpId|Name| Address|Age

1|Noel| "Main Street 1|Country Road "|24

2|Joel| "Main Street 2|Country Road "|25

Corresponding XSD:

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">

<xs:element name="Employees">

<xs:complexType>

<xs:sequence>

<xs:element name="Employee" maxOccurs="unbounded" minOccurs="0">

<xs:complexType>

<xs:sequence>

<xs:element type="xs:byte" name="EmpId"/>

<xs:element type="xs:string" name="Name"/>

<xs:element type="xs:string" name="Address"/>

<xs:element type="xs:string" name="Age"/>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:sequence>

</xs:complexType>

</xs:element>

</xs:schema>

And the output generated with it is as follows :

<?xml version="1.0" encoding="UTF-8"?> <Employees> <Employee> <EmpId>EmpId</EmpId> <Name>Name</Name> <Address>Address</Address> <Age>Age</Age> </Employee> <Employee> <EmpId>1</EmpId> <Name>Noel</Name> <Address>"Main Street 1</Address> <Age>Country Road "</Age> </Employee> <Employee> <EmpId>2</EmpId> <Name>Joel</Name> <Address>"Main Street 2</Address> <Age>Country Road "</Age> </Employee> </Employees>

But if I remove the white space ie. old sample data.

1|Noel| "Main Street 1|Country Road "|24

2|Joel| "Main Street 2|Country Road "|25

to new sample data

1|Noel|"Main Street 1|Country Road "|24

2|Joel|"Main Street 2|Country Road "|25

it works without a problem .Thank you once again for helping me out.

<?xml version="1.0" encoding="UTF-8"?> <Employees> <Employee> <EmpId>EmpId</EmpId> <Name>Name</Name> <Address>Address</Address> <Age>Age</Age> </Employee> <Employee> <EmpId>1</EmpId> <Name>Noel</Name> <Address>Main Street 1|Country Road</Address> <Age>24</Age> </Employee> <Employee> <EmpId>2</EmpId> <Name>Joel</Name> <Address>Main Street 2|Country Road</Address> <Age>25</Age> </Employee> </Employees>

[bdBais13]

Could you share you xsd schema? Probably the problem is there.