Tuesday 11 August 2015

Parse Excel File

Many a times, we need to import data from excel into PRPC data structures. There are quite a few ways to do so, here I am describing an OOTB internal activity to import data from Excel (XSLX) into PRPC clipboard.

As a pre requisite we need a template excel file uploaded as Rule-File-Binary.  Later this template file is used to interpret the uploaded spread sheets values and map into appropriate clipboard structures. Always have the first row as heading, and second row should have following syntax

{.PageListName().PropertyName input}

Next write an activity, to get the file location of the uploaded excel file (use FileUpload control to upload the excel sheet), and then call OOTB internal activity named MSOParseExcelFile. This activity takes two mandatory properties, first is the location and name of the uploaded excel file, second is the name of the template file.

With this MSOParseExcelFile would map the contents of the excel file in to appropriate clip board structures.

Here is a scenario illustrating the same

Scenario: Upload Employee Details (Name, ID, Manager, Age) from an excel file

Step1: Create Template File and upload it is Rule-File-Binary






Step 2: Write an activity to call MSOParseExcelFile


Param Supplied:

FSFFileName : Name and Location of the uploaded excel file.
TemplateRFB: Name of the uploaded binary file, it should follow <Directory name>!<Rule Name>!<extension>

Step 3: Run the activity to check results



Hope the above article is covers most of the required stuff. Please leave a comment with your practical experiences on how useful this article was and also any thing needs further explanations. 

Feedback's are welcomed in the form of comments.

Vamshi

14 comments:

  1. Running 7.1.8 and I don't see MSOParseExcelFile as a method. Also, I don't want to use a listener, I want to use an attachment. Any thoughts?

    ReplyDelete
  2. MSOParseExcelFile is OOTB, try content search else directly use it in activity call. If you want you use attchements, then you need to download the attachments in to a staging directory and provide that location details as inputs for FSFFileName parameter.

    Vamshi

    ReplyDelete
  3. Thanks Vamshi for posting this.
    Can you help me in finding a solution where there would be more than 50000 lines in excel which i want to push it to DB and then show records using pagination in a repeating grid using Report Definition.
    We are currently using 7.1.8.
    any suggestions on how to proceed?

    Thanks & regards
    Kishore

    ReplyDelete
  4. This article has been very helpful on starting this process.

    ** The uploaded file template could not be recognized. Please upload a valid file. Refer to template TestPersonTemplate for details.

    I am getting the above error when I try to parse a file I have attached to the workobject. Do you have any insights on how I might fix this issue?

    Thanks

    ReplyDelete
  5. Your blog was a life saver :) Thank You!

    ReplyDelete
  6. Impressed Bro.. Nvvu Supper.saved a lot of my development time

    ReplyDelete
  7. Great help Vamsi.. Thanks a lot!

    ReplyDelete
  8. Yes, you need to create your section with File Upload and write an activity to read the file and its location.

    Have a look into Pega --> Application --> Distribution --> Import wizard. This wizard uses File Upload control. You can upload a file here and check Clipboard pages(pxRequestor.pyFileUpload) to get the location of where the file is stored on the server. Default it is Static Content folders.

    Hope this helps

    Vamshi

    ReplyDelete
    Replies
    1. thank u so much for ur reply.... sorry to bother u again.. do u mean file upload control will automatically upload the file.. could u please xplain the steps to read the file and its location.. from fileupload control i could able to browse and get the locaion of the file... but how to upload it.... thanks in advance

      Delete
  9. File upload control would upload the file in a location on the server, which is available @ pxRequestor.pyFileUpload.

    ReplyDelete
    Replies
    1. I tried File upload control, i could not see the value (file name) being changed in clipboard "pxRequestor.pyFileUpload".

      Am I missing something?

      Delete
  10. Excellent Blog, Good to implement in very first time too.

    ReplyDelete