How to handle a comma in a field in CSV file during FCC ?

[Former Member]

Hi,

I am having a requirement where we have to convert a CSV file into XML using File Content Conversion . The issue is one of the field in the file is having a comma inside. So the XML parser is taking it as a field separator and throwing an error.

The contents of the file are as follows:

"02975859","New Key","9","Failed, rejected by RTI server"

How to handle a comma inside field "Failed, rejected by RTI server".

Any help would be appreciated.

Regards

Pravesh

[Former Member]

in FCC use this :

Node.fieldSeparator = "," & don't use enclosureSign

Now when you get the XML structure you will get the first value as "02975859 and the last value as Failed, rejected by RTI server" .

You can handle this " at message mapping level by using replaceString function.

Hope this workaround helps.

Regards

Soumen...

[Former Member]

No standard way.

You can

1. Make the sending business application escape comma in field and replace them by any spl char

2. You can do the same in an adapter module and then convert the CSV to XML using Message Transformation Bean

Regards

Jai

[Former Member]

Hi ,

You have to write an java mapping programm to perdromance this task , in a estandar way i think is not possible , because the fiel adapter have just one option for the delimiter character.

Here's some code that could help you

Supouse a file in this way:

1,rahul,siemens,mumbai

2,consultant,12032005

1,viswanath,sisl,hyderabad

2,systemeng,23052005

package TXTMapping;

import java.io.BufferedReader;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.InputStream;

import java.io.InputStreamReader;

import java.io.OutputStream;

import java.util.Map;

import com.sap.aii.mapping.api.StreamTransformation;

public class TMapping implements StreamTransformation {

private Map map;

public void setParameter (Map param){

map = param;

}

public void execute (InputStream in, OutputStream out){

try{

out.write("<?xml version ='1.0' encoding='UTF-8'?>".getBytes());

out.write("<ns0:Output_Data xmlns:ns0=\"urn:javamapping_test\">".getBytes());

String line = null;

BufferedReader bin = new BufferedReader(new InputStreamReader(in));

StringBuffer buffer = new StringBuffer();

while((line = bin.readLine())!= null){

String Company = null;

String Name = null;

String Place = null;

String Desgn = null;

String Since = null;

char[] str= new char[100];

str = line.toCharArray();

String[] Data = new String[10];

int S1 = 0;

int s2 = 2;

for (int i=2; i<line.length(); i++)

{

if (str<i>==',' && str[0]=='1')

{

Data[S1]= line.substring(s2,i);

S1=S1+1;

s2 = i+1;

}

if (i == line.length()-1 && str[0] == '1')

{

Data[S1]= line.substring(s2,i+1);

Name = Data[0];

Company = Data[1];

Place = Data[2];

out.write ("<Data>".getBytes());

out.write ("<Header>".getBytes());

out.write (("<Name>"Name"</Name>").getBytes());

out.write (("<Company>"Company"</Company>").getBytes());

out.write (("<Place>"Place"</Place>").getBytes());

out.write ("</Header>".getBytes());

}

if (str<i>==',' && str[0]=='2')

{

Data[S1]= line.substring(s2,i);

S1=S1+1;

s2 = i+1;

}

if (i == line.length()-1 && str[0] == '2')

{

Data[S1]= line.substring(s2,i+1);

Desgn = Data[0];

Since = Data[1];

out.write ("<Item>".getBytes());

out.write (("<Designation>"Desgn"</Designation>").getBytes());

out.write (("<Since>"Since"</Since>").getBytes());

out.write ("</Item>".getBytes());

out.write ("</Data>".getBytes());

}

}

}

out.write("</ns0:Output_Data>".getBytes());

}

catch(Throwable t){

t.printStackTrace();

}

}

}