generic adapter module to read any xl file

[Former Member]

Hi Experts,

I have a requirement where i was suposed to read xl file using sender file adapter module.

Now i see ther are three more xls files of a completely different format, is ther any generic mdue to read so..can i be developed..

or do i have to develope three different modules for this requirement..

pls help me out

[jyothi_anagani · Accepted solution]

Hi Ravindra,

If 3 excel files are having 3 different formats, you should have 3 Modules. I have developed a module to read an Excel sheet as more generalized. That can read the Excel file. You just explain the 3 formats of your Excel Files Then I can give you the code to read.

There is no predefined module to do This.

Thanks.

[Former Member · Accepted solution]

Hi Jyothi,

Cold please let me know the code... Thnx a ton

[Former Member · Accepted solution]

Teja,

Thats what he is trying to tell you. Give the format and an example of how do they like so that jyothi or somebody can help you out here.

As he mentioned if you have different formats always try to go for individual module which would make the maintenance also easy when compared to the generic one. If you have one more format tomorrow then you may need to extend the generic, instead if you have each one seperate then you can go for the new module. A simple example for this approach is given here:

/people/sap.user72/blog/2005/07/04/read-excel-instead-of-xml-through-fileadapter

Regards,

---Satish

[Former Member · Accepted solution]

Hello Jyothi,

I have one xl file with 7 cols and 24 rows

second xl file with 6 cols and 45 rows

third xl file with 9 cols and 30 rows..

pls let me know if u require any more detail

[jyothi_anagani · Accepted solution]

This message was moderated.

[jyothi_anagani · Accepted solution]

Hi Ravindra

	public ModuleData process(
			ModuleContext mc,
			ModuleData imd)
			throws ModuleException {
			Object obj = null;
			Message msg = null;
			String msgType = null;
			String nameSpace = null;
			try {
				  obj = imd.getPrincipalData();
				  msg = (Message) obj;
				  msgType = (String) mc.getContextData("msgType");
				  nameSpace = (String) mc.getContextData("nameSpace");
				  				  if (msg.getMessageDirection()== MessageDirection.INBOUND)
					amk = new AuditMessageKey(msg.getMessageId(),AuditDirection.INBOUND);
				  else
					amk = new AuditMessageKey(msg.getMessageId(),AuditDirection.OUTBOUND);
			  					  XMLPayload xp = msg.getDocument();
				  Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Now got the xml payload object.");
				  if (xp != null) 
				  {
						Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Now calling the Convert Method to convert Excel to XML.");
						byte by[] = convert(xp.getContent(),msgType,nameSpace);
						Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Conversion Done Successfully.");
						xp.setContent(by);
				  }
				  imd.setPrincipalData(msg);
				  Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Principle data is set successfully.");
			} catch (Exception e) 
			{
				Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Module Exception Caught .");
				ModuleException me = new ModuleException(e);
				throw me;
			}
			return imd;
	  }

[jyothi_anagani · Accepted solution]

continuitition of above post.

public byte[] convert(byte src[],String msgType,String nameSpace) throws Exception 
	{
	String xmldata = "";
	try 
	{
		  ByteArrayInputStream byteArr= new ByteArrayInputStream(src);
		  Workbook wb = Workbook.getWorkbook(byteArr);
		  Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Conversion Started.");
		  xmldata ="<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n\n"+ "<ns0:"+msgType+" "+"xmlns:ns0=\""+nameSpace+"\">";
		  Cell[] cells ;
		  Cell[] cellNames ;
		  cellNames = wb.getSheet(0).getRow(0);
		  for(int j=1;j<wb.getSheet(0).getRows();j++)
		  {
				xmldata = xmldata+"<Record>";
				cells = wb.getSheet(0).getRow(j);
				for(int i=0;i<wb.getSheet(0).getColumns();i++)
				{
					xmldata = xmldata+"<"+cellNames<i>.getContents()+">"+cells<i>.getContents()+"</"+cellNames<i>.getContents()+">";
				}
				xmldata = xmldata+"</Record>";
		  }
		  xmldata = xmldata+"</ns0:"+msgType+">";	  
		  wb.close();
	 }
	 catch (Exception e)
	 {
		Audit.addAuditLogEntry(amk, AuditLogStatus.SUCCESS,"AO: Exception in Convert Method.");
	 }
	 return xmldata.getBytes();
	}

Thanks.

[jyothi_anagani · Accepted solution]

Hi ,

This is the total code of the Module..

On First post i have used the convert method inside the process method.

On second post I gave the code for convert Method.

Use that Code.

and Here I have used jxl.jar file to read an excel sheet..

This Module can read multiple records each having multiple columns and creates the xml structure using the cell values specified in the First Record. 

Eg: If the first record contains 3 cells like Name, EmpId, Design then it creates 3 element tags with the same name inside Record node in xml structure as shown below. 

Similarly if it contains 5 cells then it creates 5 element tags inside the Record node in xml structure. 

Thanks.

[Former Member · Accepted solution]

Thanks a ton jyothi.

Lemme try and get back to you if i have any issues...once again..thank you

[former_member184944]

Hi ,

I read your post as well as the answere. I am trying to implement a similar adapter . But my requirement is to create a custome adapter module to identify generic xsds. For eg: The format of the incoming file is not known. It could be any of the 5 formats known. Incoming file will be a single field having the entire xml .

Could you please help me with the code here how to extract the entire string into xml or how to read the content of the string and populate another field in the payload with some information (format type)

[former_member207622]

Can you check the below discussion having UDF / XSL mapping to solve your purpose

http://scn.sap.com/thread/3269146

http://scn.sap.com/message/2786502

Thanks

Ninad Sane

[Former Member]

Hi,

Please go thru this link it might be helpfull.

/people/sap.user72/blog/2005/07/04/read-excel-instead-of-xml-through-fileadapter

Thanks,

Arijit Guha

[prateek]

Java can do anything. But this might be a difficult solution to implement than implementing specific ones.

Regards,

Prateek