FCC issue with the Next Line character

[sherin_jose4]

Hi all,

I am doing an interface involving sender FCC for a CSV file.

The fields and the sample data are,

Name, Id, Description, emailId

<Sherry>,< 234655>, <Test description for the next line issue ( return character )Thanks and Regards ( return character) Sherin>, <sherrysap.com>

When done with FCC, the xml formed is like

<Data>
   <item>
      <Name>Sherry</Name>
      <Id>234655</Id>
      <Description>Test description for the next line issue</Description>
  </item>
  <item>
     <Name>Thanks and Regards</Name>
     <Id>Sherin</Id>
  </item>
  <item>
      <Name>sherrysap.com</Name>
  </item>
</Data>

Since there is a return character in the Description field, it takes the next line as completely next record and forms the next tag which screws up my xml formation.

The FCC parameter that i have used for converting CSV is,

Data.fieldSeparator : ,

Data.endSeparator : nl

Data.fieldNames : Name, Id, Description, emailId

Data.enclosureSign : "

Data.additionalLastFields : ignore

ignoreRecordsetName : true

Hope, someone can address my issue.

Thanks & Regards,

Sherin Jose P

[Former Member · Accepted solution]

Hi Sherin,

There is a open source java code available for reading CSV File. Please refer this wiki.

https://wiki.sdn.sap.com/wiki/display/NWTech/JavaMappingtoReadComplexCSVFile

Regards

Ragu

[sherin_jose4 · Accepted solution]

Thanks a lot guys for your tedious efforts.

Ragu,

Your wiki entry has done the trick exactly for me Thanks a lot for sharing.

Anupam and Chandrasekar,

Thanks a ton for spending your precious time on this issue and helping me on the codes. It did come close but i was somewhere getting the errors and had to put this work on hold for sometime till Ragu came with the code that completely solved my purpose.

Thanks & Regards,

Sherin Jose P

[sherin_jose4]

The exact csv file that i am getting is,

"Name","Id","Description","emailId"
"Sherry","234655","Test description for the next line issue
Thanks and Regards,
Sherin","sherrysap.com"
"Ram","234688","Test description for the next line issue
Thanks and Regards,
Ram","Ramsap.com",

The exact xml that needs to be formed is,

<Data>
    <item>
         <Name>Sherry</Name>
         <Id>234655<Id>
         <Description>Test description for the next line issue
                               Thanks and Regards,
                               Sherin</Description>
         <emailId>sherrysap.com</emailId>
    <item>
    <item>
         <Name>Ram</Name>
         <Id>234688<Id>
         <Description>Test description for the next line issue
                               Thanks and Regards,
                               Ram</Description>
         <emailId>Ramsap.com</emailId>
    <item>
<Data>

Could anyone help me in the java mapping for this particular scenario.

[Former Member]

In the java Mapping, start reading each line/ record

where "s" is the record i.e. line in the file.

StringTokenizer hd = new StringTokenizer(s,",");

Vector in = new Vector();

while (hd.hasMoreTokens()) {

in.addElement(hd.nextToken());

}

String name = in.get(0).toString()

String id=in.get(1).toString();

String Desc1= in.get(2).toString();

String Desc2= in.get(3).toString();

String EmailID = in.get(4).toString();

Out=Out+"<Item>" "<Name>"name"</Name> <Id>"id"</Id>""<Description>" + Desc1 "," Desc2"</Description> <emailId>"EmailID"</emailId>""</Item>";

This might help

[anupam_ghosh2]

Hi Sherin Jose P ,

This was a real stuff to work with. This is one of the complicated java mapping programs I ever wrote. Now here I explain some points on the program before I write down the code here

1. We are not sure that after exactly how many lines we are going to read the emailID of the person. So I had to stop reading each "item" after I read a line having content ".com" in it. Now this email id may vary example: ".com","co.in";,".org" etc. Fortunately we do not have too many options. Please modify this code to include all possibilities using "||" construct in java (OR) . Due to lack of time I was unable to include all options in emailid.

2. If you notice the file carefully you will see there is a comma after "Ramsap.com"; which was missing after "sherrysap.com";.

3. You need new line character in description field. I am afraid this is not quite possible in xml. XML parser will ignore all new line feeds. Thus you need multiple description field Thus min-max occurence for your xml structure has to be something like this

 
data --- 0-1 
  item-----0- unbounded 
     name----- 1-1 
     Id----------1-1 
     Description----- 1-unbounded  (since you r not sure about how many new lines are going to arrive) 
     emailid----- 1- 1    

Thus all fields are mandatory.

Here goes your code

 
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 TxtFileMapping implements StreamTransformation {

	/**
	 * @param args
	 */
	
    public void setParameter(Map arg0) {
		
		
	}
   public boolean AlphaNumeric(String s)
   {
	   try
	   {
		   int i,l;
		   char c;
		   l=s.length();
		   for(i=0;i<l;++i)
		   {
			   c=s.charAt(i);
			   if((c>='a' && c<='z')||(c>='A' && c<='Z')||(c>='0' && c<='9'))
			   {
				   return true;
			   }
				   
		   }
	   }
	   catch(Exception e)
	   {
		   e.printStackTrace();
	   }
	   return false;
   }
	public void execute(InputStream in, OutputStream out)
	{
		try
		{
			String line = null,dataItem=null,temp="",s[],a[]; 
                        int i,l,count=0,start=0,end=-1;
                        boolean status=false;
                        String openTags[]={"<Name>","<Id>","<Description>","<emailId>"};
                        String closeTags[]={"</Name>","</Id>","</Description>","</emailId>"};
	                BufferedReader bin = new BufferedReader(new InputStreamReader(in)); 
	                out.write("<?xml version ='1.0' encoding='UTF-8'?>".getBytes()); 
	                out.write("<ns0:Data xmlns:ns0=\"urn:javamapping_test\">".getBytes());
			//ignore header 
			bin.readLine();
			while((line=bin.readLine())!=null)
			{
			   	//copy everything unless email is reached
				temp+=line;
				
				if(line.indexOf(".com")>0)
			   	{
					
					
			   		s=temp.split("\"", -1);
			   		
			   		out.write("<item>".getBytes());
			   		for(i=0,count=-1;i<s.length;++i)
			   		{
			   			if(!AlphaNumeric(s<i>))
			   			{
			   				continue;
			   			}
			   			if(count<2 || s<i>.indexOf(".com")>0)
			   			{
			   				count++;
			   			}	
			   			out.write(openTags[count].getBytes());
			   			out.write(s<i>.getBytes());	
			   			out.write(closeTags[count].getBytes());
			   			
			   		}	
			   		out.write("</item>".getBytes());
			   		temp="";
			   	}
				else
				{
					temp+="\"\"";
				}
			}
			out.write("</ns0:Data>".getBytes());

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		
		
	}

	
	public static void main(String[] args) {
		
		try
		{
			TxtFileMapping t=new TxtFileMapping();
			FileInputStream in=new FileInputStream("C:/Apps/sjp.txt");
	    	         FileOutputStream out = new FileOutputStream("C:/Apps/out.xml");
	    	         t.execute(in, out);
			
		}
		catch(Exception e)
		{
			
		}
        
	}

}


  

The source file sjp.txt

 

"Name","Id","Description","emailId" 
"Sherry","234655","Test description for the next line issue 
Thanks and Regards, 
Sherin","sherrysap.com"; 
"Ram","234688","Test description for the next line issue 
Thanks and Regards, 
Ram","Ramsap.com";,


 

hope this helps.

regards

Anupam

Edited by: anupamsap on Jun 29, 2011 6:21 AM

[Former Member]

Hi Sherin,

maintain your mail account info in business card, i will send you the code

[anupam_ghosh2]

Hi Sherin Jose P ,

Here is the xml output due to strange formatting errors I could not post it in my earlier post

  <?xml version="1.0" encoding="UTF-8" ?> 
- <ns0:Data xmlns:ns0="urn:javamapping_test">
- <item>
  <Name>Sherry</Name> 
  <Id>234655</Id> 
  <Description>Test description for the next line issue</Description> 
  <Description>Thanks and Regards,</Description> 
  <Description>Sherin</Description> 
  <emailId>sherrysap.com</emailId> 
  </item>
- <item>
  <Name>Ram</Name> 
  <Id>234688</Id> 
  <Description>Test description for the next line issue</Description> 
  <Description>Thanks and Regards,</Description> 
  <Description>Ram</Description> 
  <emailId>Ramsap.com</emailId> 
  </item>
  </ns0:Data>

regards

Anupam

Edited by: anupamsap on Jun 29, 2011 11:27 AM

Edited by: anupamsap on Jun 29, 2011 11:33 AM

[Former Member]

Hi,

Code to generate the output

package com.sap;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.util.Map;
import java.util.StringTokenizer;
import java.util.Vector;

import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;

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

public class TestNewLine implements StreamTransformation {

	private Map param; 
	BufferedReader docInput;
	String inputLine = "";
	String out = "";

	private String finalOutput = "";
	StringBuffer inputData = new StringBuffer();
	StringBuffer outputData = new StringBuffer();
	File file = null;
	StringBuffer fixed = new StringBuffer(); 
	StringBuffer endTag = new StringBuffer(); 
	
	StringBuffer out_final               = new StringBuffer();
	StringBuffer outputList_Line_1   	 = new StringBuffer();    //Line 1
	StringBuffer outputList_Line_2       = new StringBuffer();    //Line 2		


	public void execute(InputStream input, OutputStream output)
			throws StreamTransformationException {
		docInput = 	new BufferedReader(new InputStreamReader(input));
		try 
		{
			while ((inputLine = docInput.readLine()) != null) {
				check_line(inputLine);
			}
		}
		catch(Exception exception1) { }
		createStartTag();
		createEndTag();
		String out23 = fixed + out + endTag;
		out_final.append(fixed);
		out_final.append(out);
		out_final.append(endTag);
		try {
			output.write(out23.getBytes());
		} catch (IOException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
			output.flush();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
		public void createStartTag(){
		final String fXML ="<?xml version=\"1.0\" encoding=\"UTF-8\"?>"
			+"<ns0:MT_XML xmlns:ns0=\"urn:test\">";
		fixed.append(fXML+"<Data>");				

		}
		public void createEndTag(){
		final String EndMainTag = "</Data>"; 
		final String EndMT= "</ns0:MT_XML>";
		endTag.append(EndMainTag + EndMT);	
		}
		public void check_line(String line){
		if(line.contains("Thanks and Regards")){
			String Desc2 = line;
			 out = out + ","+ Desc2;
		}
		else if(line.contains(".com")){
			StringTokenizer hd = new StringTokenizer(line,",");
			Vector in = new Vector();
			while (hd.hasMoreTokens()) {
			  in.addElement(hd.nextToken());
			}
			String Desc3 = in.get(0).toString();
			String EmailId = in.get(1).toString();
			out = out + Desc3+ "</Description> <EmailId>"+EmailId+"</EmailId>"+"</Item>";
		}
		else{
			StringTokenizer hd = new StringTokenizer(line,",");
			Vector in = new Vector();
			while (hd.hasMoreTokens()) {
		  			 in.addElement(hd.nextToken());
			}
			String name = in.get(0).toString();
			String id=in.get(1).toString();
			String Desc1= in.get(2).toString();
			out = out+"<Item>" +"<Name>"+name+"</Name> <Id>"+id+"</Id>"+"<Description>" + Desc1;
		}
		}
		
	public void setParameter(Map arg0) {
		 		Map map = param;
	}

	/**
	 * @param args
	 * @throws FileNotFoundException 
	 * @throws StreamTransformationException 
	 */
	public static void main(String[] args) throws FileNotFoundException, StreamTransformationException {
		FileInputStream f = new FileInputStream("C:\\NewLineTest.txt");
		
		FileOutputStream output = new FileOutputStream("C:\\NewLineTest.xml");
		TestNewLine ts = new TestNewLine();
		ts.execute(f,output);

	}

}

output is

<?xml version="1.0" encoding="UTF-8" ?>

- <ns0:MT_XML xmlns:ns0="urn:test">

- <Data>

- <Item>

<Name>"Sherry"</Name>

<Id>"234655"</Id>

<Description>"Test description for the next line issue , Thanks and Regards, Sherin"</Description>

<EmailId>"sherrysap.com"</EmailId>

</Item>

- <Item>

<Name>"Ram"</Name>

<Id>"234688"</Id>

<Description>"Test description for the next line issue , Thanks and Regards, Ram"</Description>

<EmailId>"Ramsap.com"</EmailId>

</Item>

</Data>

</ns0:MT_XML>

[sherin_jose4]

None of the ideas worked actually.

Not sure if i am doing something wrong, but definitely looks like this is not the solution.

I also tried with the Java mapping, it didn't work in my scenario.

Would like to see some more solutions for this issue.

Thanks & Regards,

Sherin Jose P

[Former Member]

Hi,

What was the issue in Java mapping?

Since each line in your file has exactly 4 fields, in your Java code, you can try reading each line and then use StringTokenizer to split the line into individual fields.

Alternatively, you can split the entire file using String Tokenizer and then use a counter to read the fields. Once the counter equals 4, reset it to 1, which will indicate the particular field that you are reading.

If you have issues with StringTokenizer, you can also use the split function of strings to split the file/line into individual fields.

Regards

[Former Member]

create ur xml structure using JAVA mapping, use ',' as a string tokenizer.

Let us know what is the issue ur facing while running the java mapping.

[anupam_ghosh2]

Hi Sherin Jose P,

Shiladitya has already posted the links to blogs which will definately solve your problem. I would just like to add few more steps or tests before you finally decide the course of action. Are you receiving the data in this format as you have posted (format 1)

<Sherry>,< 234655>, <Test description for the next line issue ( return character )Thanks and Regards ( return character) Sherin>, <sherrysap.com>

or in this format (format 2)

"Sherry", 234655, Test description for the next line issue ( return character )Thanks and Regards ( return character) Sherin,

sherrysap.com

______________________________________________________________________________________________________

In case of (format 1) try using this FCC parameter

Data.endSeparator : '>\n'

In case of (format 2) and your operating system is Windows then try this

Data.endSeparator : '\r\n'

else

I guess you have to try out as per Shiladitya's advice.

Here is the output expected as out.xml from my post below. For strange reasons this is not visible from my post below. so pasting it here.

  <?xml version="1.0" encoding="UTF-8" ?> 
- <ns0:Data xmlns:ns0="urn:javamapping_test">
- <item>
  <Name>Sherry</Name> 
  <Id>234655</Id> 
  <Description>Test description for the next line issue</Description> 
  <Description>Thanks and Regards,</Description> 
  <Description>Sherin</Description> 
  <emailId>sherrysap.com</emailId> 
  </item>
- <item>
  <Name>Ram</Name> 
  <Id>234688</Id> 
  <Description>Test description for the next line issue</Description> 
  <Description>Thanks and Regards,</Description> 
  <Description>Ram</Description> 
  <emailId>Ramsap.com</emailId> 
  </item>
  </ns0:Data>

regards

Anupam

Edited by: anupamsap on Jun 29, 2011 6:03 AM

[Former Member]

Hi,

You can have a look at the following blog for FCC

/people/shabarish.vijayakumar/blog/2006/02/27/content-conversion-the-key-field-problem

Also try the FCC parameter : enclosureSignEscape to see if this works for newLine character

One solution would be to go for a Java mapping

/people/rahul.nawale2/blog/2006/07/18/java-mapping-an-alternate-way-of-reading-a-csv-file

Regards