Excel File with .CSV extension in ZIP

[Former Member]

Hi Experts,

In one of my requirements,

Scenario: FILE to IDOC/RFC/PROXY

Option1:

I am getting a ZIP file with files of type "Microsoft Excel Comma Separated" with file names as "TEST1.CSV","TEST2.CSV" in it.

Option2:

A non ZIPEED Excel File (.XLS) )with multiple Tabs like "TEST1","TEST2"

Now I need to choose which type of file I can go with to read it for PI Mappings

I am confused with Excel with CSV extension,

1) For this our File Adapter normal FCC parameters are enough or need to go for custom modules?

2) If I need to read Excel with extension .XSL which has multiple tabs, apart from custom module do we have any simple approach?

Which option is easy for PI handling..?

Appreciate your inputs on this

Regards

Siri

[markangelo_dihiansan · Accepted solution]

Hi Siri,

I am confused with Excel with CSV extension,
1) For this our File Adapter normal FCC parameters are enough or need to go for custom modules?

Excel files in .csv extension should be convertible via fcc. The need for custom adapter modules would of course depend on how complex or how deep the csv would be.

 2) If I need to read Excel with extension .XSL which has multiple tabs, apart from custom module do we have any simple approach?

Still my preferred option since there is only one file to be read. You can actually use java mapping to convert the XLS to xml. Keep in mind that what happens inside adapter modules are hard to trace especially if there are few traces (audit logs) and the code to convert to xml is not readily available.

Regards,

Mark

[Former Member · Accepted solution]

Hi Mark,

Thanks for Quick reply.

1) Thansk for clarification on FCC,in my case its simple plain structure not nested.So I prefer this way.

but challenge here is,need to unzip ( for this, thinking to ask network team to run a script locally at OS level for unzip file and put it in some folder with given naming convention) and read 2 files and map to single output structure.

2 channels are required if I use FCC option, since I need to read 2 files.

in this case how to build the scenario..

2) For XLS multiple tabs reading conversion. I am just going through Eng's Blog(Example Scenario 2)

http://scn.sap.com/docs/DOC-59021

For this approach I am not clear on communication channels handling as per my understanding each sheet reading required one channel,  in this case I am little confused on how to use 2 channels for one interface.

As you advised ,Reg Java mapping for reading excel sheets, I am not expert in JAVA and for easy maintenances trying to avoid java map whenever possible.

Please advise...

Thanks&Regards

Siri

[markangelo_dihiansan · Accepted solution]

Hi Siri,

1) Thansk for clarification on FCC,in my case its simple plain structure not nested.So I prefer this way.
but challenge here is,need to unzip ( for this, thinking to ask network team to run a script locally at OS level for unzip file and put it in some folder with given naming convention) and read 2 files and map to single output structure.
2 channels are required if I use FCC option, since I need to read 2 files.

Well I think there are two approaches to make this work:

1. Once you have unzipped the file, using NFS there is an option to attach another file. This way, you can attach the second file and do processing. Take note that only the main payload (or the first file) can be converted to XML via FCC, so for the second file an adapter module is required to convert it (or at least if you want it to be an XML) just before passing both messages to the integration engine. Mapping the main payload and attachment would require you to use a java mapping.

2. The second one would be bpm, there should be a correlation between the two payloads such that you'll be able to merge them (see N:1 mapping).  No adapter modules or java mapping is required.

2) For XLS multiple tabs reading conversion. I am just going through Eng's Blog(Example Scenario 2)
http://scn.sap.com/docs/DOC-59021


For this approach I am not clear on communication channels handling as per my understanding each sheet reading required one channel,  in this case I am little confused on how to use 2 channels for one interface.

No it does not say one sheet is required for one channel You only need one channel and the adapter module itself.

Regards,

Mark

[manigram]

Hello,

Option1:
 I am getting a ZIP file with files of type "Microsoft Excel Comma Separated" with file names as "TEST1.CSV","TEST2.CSV" in it.

I thnik we have a option to zip and un zip the file in the file adapter by using "PayLoadZipBean" (not sure excatly) . So unzip it and read the .csv file via FCC.

Option2:
A non ZIPEED Excel File (.XLS) )with multiple Tabs like "TEST1","TEST2"

Our PI cannot support the excel file, so we have to go for java mapping or xslt mapping. So to achive this by using java or xslt mapping.

Regards,

Manigandan

[Former Member]

Hi Mark,

Thanks for reply..

So as per Eng’s Blog, reading XLS  with given module will require only one channel for reading multiple sheets of single XLS.

1)

Sorry for my confusion, How can I declare sheetIndex value for reading two sheets..

do I need to declare sheetIndex value twice as :  sheetIndex = 0

                                                                       sheetIndex = 1

in same channel parameters?

If so,how about reading Fields in each sheet values.

2)

As I am not clear on how to build Data types\Message Types for each Excel sheet fields.

If I want to use this option could you please elaborate steps in PI.

3)

Please let me know where to keep these jar files in PI ..

• poi-3.9-20121203.jar
• poi-ooxml-3.9-20121203.jar
• poi-ooxml-schemas-3.9-20121203.jar
• xmlbeans-2.3.0.jar
• dom4j-1.6.1.jar

Thanks & Regards

Thansk&Regards

Siri

[Former Member]

Hi Experts,

I got answer for first 2 questions as it’s not possible to read multiple sheets with this approach.

How about, is having two communication channels for this interface where each will read one excel sheet\Tab in excel file

For question 3: got answer saying like.

"Regarding the JAR files, these are bundled in the adapter module's EAR file and are automatically deployed into the PI system once the EAR file is deployed. You do not need to manually upload them into PI's file system"

As I am not much familiar with Jar files & deployments

Does it mean using Module parameters in file adapter as per blog is enough?

What exactly mean by EAR file deployment? I have downloaded above jar files in my desktop now and don’t I need to place this anywhere in SAP PI configurations?

Thanks in advance

Siri

[markangelo_dihiansan]

Hi Siri,

I got answer for first 2 questions as it’s not possible to read multiple sheets with this approach.
How about, is having two communication channels for this interface where each will read one excel sheet\Tab in excel file

It is possible, but of course you have to modify 's code. Having two channels to read the same file will cause trouble and is not recommended.

"Regarding the JAR files, these are bundled in the adapter module's EAR file and are automatically deployed into the PI system once the EAR file is deployed. You do not need to manually upload them into PI's file system"

As I am not much familiar with Jar files & deployments
Does it mean using Module parameters in file adapter as per blog is enough?
What exactly mean by EAR file deployment? I have downloaded above jar files in my desktop now and don’t I need to place this anywhere in SAP PI configurations?

I can only say this via my personal experience. Currently, there are two ways I know of to deploy adapter modules in a PI system. The first one would be to deploy it via NWDS, if the NWDS can connect to the PI system, there is an option there to run the EAR file on the application server. The NWDS will prompt you if the deployment is successful. You can also check in NWA JNDI browser to see if the adapter module is indeed deployed.

As for the other way, in NWDS export the EAR file as an EAR file. Then in CTS+, you can attach the EAR file as a J2EE application. Afterwards, it follows normal CTS transport e.g you can release it in se09 then import to target application via STMS.

Regards,

Mark

[Former Member]

Hi Mark/Manigandan,

Thanks for your replies.

I have chosen to use .csv and its working fine.

But for other possible scenarios to use Excel files.

We don’t have NWDS for PI so I am still not sure on deploying Engs given Modules\.jar files in PI environment.

It will be helpful if I know where and how to add these .jar files in PI.

Regards
Siri

[markangelo_dihiansan]

Hi Siri,

The NWDS can be downloaded here https://nwds.sap.com/swdc/downloads/updates/netweaver/nwds/ce/ , but you will need an OSS user with the correct authorizations.

The XI jar files can be found in the java directory e.g

C:\Users\yourUser\AppData\LocalLow\Sun\Java\Deployment\cache\javaws\http\Dfqdn\Pxxxxx\DMrep\DMrepository

where

FQDN = fully qualified domain name

xxxxx = XI java port

the folder can be easily found if you have the option to show hidden files and folders.

Regards,

Mark

[Former Member]

Hi Mark/Experts,

In order to complete my flow I am in requirement of where I need to take filed value from request mapping and use it in response structure.

1. RFC <--> SOAP

Example:

Request map:

Source:                                    Target

S_Primary key =123     S_Primary key = Primary key

Response map:

Source:                                    Target

Field1                                      field1

Field2                                      field2

                                                 T_Primary key (now I need this value from my request map field of S_Primary key)

which is the easiest way to get this

Regards,

Siri

[markangelo_dihiansan]

Hi Siri,

The easiest way would be to use dynamic configuration.

Regards,

Mark

[Former Member]

Hi Mark,

Is it possible to use request mapping source field into response mapping target field.

we have 2 mappings here 1) request map

                                      2) response map

I need request source structure field to response map target structure.(In response mapping source structureI am not having relevenat filed I need it from request map source)

I think I am not confusing you..

if its possible could you please let me know how to handle it with DC.

sample code for ref

BR

Siri

[manigram]

Hello,

As per my understanding you need to capture some values from request and send the same values in the response.

In this case you have to go to the BPM only, request mapping life time is over when it completed the execution.

Regards,

Manigandan

[markangelo_dihiansan]

Hi Siri,

The dynamic configuration would of course depend on your sender/receiver adapter. In the example below, the HTTP adapter is used.

String val = new String("testValue");


DynamicConfiguration conf = (DynamicConfiguration) container.getTransformationParameters().get(StreamTransformationConstants.DYNAMIC_CONFIGURATION);
DynamicConfigurationKey key = DynamicConfigurationKey.create("http://sap.com/xi/XI/System/HTTP,"AnyName");



conf.put(key,val);

Regards,

Mark

[Former Member]

Hi Mark,

Thanks for reply..

Got it with using DC option.

and one more for your review

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

Regards

Siri