Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • avatar image
    Former Member
    Feb 19, 2010 at 02:20 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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();

      }

      }

      }

  • avatar image
    Former Member
    Feb 19, 2010 at 07:02 PM

    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...

    Add comment
    10|10000 characters needed characters exceeded