Excel Upload scenario

[arkesh_sharma]

Hi Experts,

I am working on a scenario where I need to read a excel file from a shared directory. Please let me know how should I read the excel file from my sender file communication channel in FCC ?

I am working on this scenario for the first time so need some help and guidance.

Thanks in advance,

Arkesh

[Shabarish_Nair · Accepted solution]

if it is an excel file and not a csv, then you will need to write custom code to read the file.

You could add it as a module or java mapping. Refer: http://scn.sap.com/community/pi-and-soa-middleware/blog/2009/04/05/excel-files--how-to-handle-them-i...

[arkesh_sharma · Accepted solution]

Hi Shabarish,

The file is a CSV file. Just confirmed from the Functional Spec.

Thanks,

Arkesh

[former_member184681 · Accepted solution]

Hi,

A .csv file is not really an Excel file, it is only sometimes opened in Excel. Still, this is a semicolon- or comma-separated flat file that you can convert to a PI XML message with File Content Conversion of the sender file communication channel. No additional special approach is required.

Hope this helps,
Greg

[arkesh_sharma · Accepted solution]

Hi Greg,

I opened up the sample file present in the functional document and checked it. The file was not either comma seperated and semi-colon seperated. The first row contains the fields names and then the field values follow.

Is the comma or semi-colon hidden when the file is opened in excel? I am bit confused here so just trying to sort out things.

- Arkesh

[former_member184681 · Accepted solution]

Dear Arkesh,

>>> Is the comma or semi-colon hidden when the file is opened in excel?

Yes, Excel does some formatting when opening a .csv file, so that you do see the real content (values are put into the Excel cells). In order to see the raw file content, open it in notepad. Then, prepare your FCC parameters according to the raw file content format.

Hope this helps,
Greg

[Former Member · Accepted solution]

Open it with notepad. You will see it as a "," separated (csv) file.

[Former Member · Accepted solution]

pls confirm from functional people or third party contacts that the file placed will have a .xsl extension or .csv extension. if it will have .xsl then you are getting a excel file and then you will have to write a custom J2EE adapter module to convert it to an XML msg through your file adapter. if it will have .csv extension, then by FCC you can convert it to an XML msg. you can open a .csv file by using notepad and on opening it, the file will have comma separated values.

[former_member184681 · Accepted solution]

Hi Amol & Rajeev,

Please read the thread carefully before adding your own posts. By repeating questions that were already answered (.xls or .csv? answered here: "The file is a CSV file. Just confirmed from the Functional Spec." http://scn.sap.com/message/13146843#13146843) or repeating answers of previous users, you do not help the questioner.

Have a look at this great blog by Prateek Raj Srivastava about the quality of PI space content, especially point #3:
http://scn.sap.com/community/pi-and-soa-middleware/blog/2012/03/26/improving-the-quality-of-content-...

Regards,
Greg

[Former Member · Accepted solution]

Hi Greg,

  True, We must follow this blog and that's why I didn't ask him that question again here.. If you check my reply, I rather suggested Arkesh to open that file in notepad to see it as .csv file.

Thanks,

Amol

[former_member184681 · Accepted solution]

Ok, and that is something that I have already suggested a day earlier: http://scn.sap.com/message/13147391#13147391. So it looks like a typical example of a "me too" answer.

Regards,
Greg

[arkesh_sharma · Accepted solution]

Hi Guys,

There is a new twist in the scenario with the sample file that was present in the Functional Document. I tried opening the file in notepad and notepad++ and in both cases it gave some junk value that could not be interpreted.

As the file was embedded in the functional document, I saved it as a CSV and tried opening it in notepad. Again I got the same junk value.

I dont know whats wrong with the sample file.

The file carries the excel logo in the functional document. Can you please confirm if the file is actually a excel file and then I try to open it with the notepad then will it give some junk value ?

- Arkesh

[Former Member · Accepted solution]

pls ask FS owner to provide you the sample file separately.

excel logo seems to contain an excel file - though cant be said for sure , as it is just an image logo.

if it is excel file, then opening it in notepad can give junk values.

since you had some confusion reg what type of file it is , so in my earlier post i mentioned both about .xls processing and .csv processing.

pls confirm from FS owner, whether the input is .xls file or .csv file, as the processing for these two types of files are different.

[Former Member · Accepted solution]

Hi Arkesh,

What is the extension of sample file? Try this - Create a comma separated file in notepad and save it with extension .csv. You will see this file in an excel logo and can be open with both notepad and excel. May be you can put your question to FS owner on this ground.

-Amol

[arkesh_sharma · Accepted solution]

The functional owner doesnt know anything about csv or xls. He says "whatever is there in the FS document, you need to follow that".

[arkesh_sharma · Accepted solution]

Hi Amol,

Many thanks for your suggestion. I tried creating a comma separated value in the notepad by referring to the excel sheet but there is field which contains a comma in its value so now I am getting an error in CC monitoring stating that:

"Conversion of file content to XML failed at position 0: java.lang.Exception: ERROR converting document line no. 2 according to structure 'TData_Test':java.lang.Exception: ERROR in configuration / structure 'TData_Test.': More elements in file csv structure than field names specified!"

The first row in the test csv file I created, are field names enclosed in double quotes and the next row are the corresponding values. So, the values looks like below:

"C_Code","Amount","Tax","Cost_Center","Profit_Center"

"CC_001","0,95","T001","CST_001","PC_001"

Problem is with the amount field that it contains a comma in it. How should I overcome this problem?

Thanks,

Arkesh

[former_member184681 · Accepted solution]

Hi,

Just add the FCC parameter: NameA.enclosureSign = " (quotation mark, identical to the one in your source file). Of course, use your own structure name instead of NameA. This will make your PI consider the values included in quotations as single fields, for instance, "0,95" will be put into a single field.

Hope this helps,
Greg

[arkesh_sharma · Accepted solution]

Hi Greg,

Many thanks for your response. It helped a lot !

[Former Member]

Hi Arkesh,

Please help me with below thread

Re: Excel(.xlsx) to .csv converison in SAP PI XI

[Former Member]

Hi Arkesh,

Please check the following link:

http://www.saptechnical.com/Tutorials/XI/Adapter/Index.htm

Hope this helps.

Thanks & Regards,

Samir

[Former Member]

Hi,

Check below blogs-

http://scn.sap.com/people/michal.krawczyk2/blog/2010/12/06/pixi-reading-ms-excels-xlsx-and-xlsm-file...

http://scn.sap.com/people/shabarish.vijayakumar/blog/2009/04/05/excel-files--how-to-handle-them-in-s...

http://scn.sap.com/people/alexander.ong/blog/2009/03/30/how-to-handle-excel-documents-with-sap-pi