Question

How to insert data into DB from an excel sheet.

Hi.I am new in pega and working with 6.2 version.There is a requirement to insert data to database from an excel sheet( having multiple rows of data).the data also needs to be shown in an window before inserting it to database.can anyone help me.The excel sheet format is predefined.

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.

Comments

Keep up to date on this post and subscribe to comments

Pega
September 28, 2015 - 5:51am

Hi

Have you explored the option of using a data table . You can check : https://pdn.pega.com/sites/pdn.pega.com/files/help_v62/procomhelpmain.htm#tools/datatableeditor/datatableeditor2.htm for details on how you can enter data to datatable from excel.

September 28, 2015 - 6:00am
Response to Santanu

that's the process how one can add/delete/update data in a data table.I am looking for a process to insert data to DB from a predefined excel sheet,which also contain date type value..

September 28, 2015 - 6:22am

Hi Dhritiman,

I think it can be done as follows:=

1.you will save the excel file after entering data as a CSV(tab,comma etc..,)

2.Then using custom activity you can parse this to the clipboard page and then you can save it to the DB.

September 28, 2015 - 6:34am
Response to DileepCSSA

Hi Dileep,

can you pls illustrate the whole process step by step.as usr will only fiil up the required data set in excel sheet.click on attach and browse button to browse to the path where excel sheet is kept and then to show whole excel data in an window and then click on submit button to insert data into database.and while doing so it will check whether same data exist in the data base or not.suppose the excel contain 7 rows of data.while inserting it shows Duplicate=1,data Inserted=6

September 28, 2015 - 7:12am
Response to DHRITIMANC

I think its like a fully user story

1.i will tell what done in our application cannot show you how it is done(client restrictions)

2.users will enter the data in the excel but I think there might some references in that excel like mostly headers

3.Now after file upload is done you will use parse delimited rule in the post activity of the upload file button action.

4.Now you will map the data from the file to the clipboard it might be a list or single page Mapping is done based on those headers

5.Now once data is available to you in clipboard ,and through looping on each paqe you can check the existence of the inserted item(as you are inserting in DB then you might have a key to identify the Unique Data)

6.then as u can identify the duplicates you can the user in the screen .

7.i assume the first screen you will use file upload, second will show file contents from clipboard to ui ,third insert it and also validate the data as u need.

hope it helps some what ...

September 28, 2015 - 7:26am
Response to DileepCSSA

can you pls give me in detail process like activity steps to upload the excel file,parsing it so that clipboard page gets populated.in short the entire steps/activity.I have found an activity "MSOParseExcelFile" @baseclass but unable to customised it as per my requirement.

September 28, 2015 - 8:34am

Hi,

I do not know the answer.    So why am I replying ?  Because if I needed to know this, I would bring up something like a decision table or decision tree rule, or a data table rule, or any other rule I could think of that has an “edit in excel” sort of button on it.  Then I would use Pega tracer while I click that button (and while I return from excel), to peek at how the core Pega app achieves the excel-to-db linkage.

I hope this method helps you.  /Eric

Pega
September 28, 2015 - 9:21am

There are tools available with databases that do a better job here. Is this a requirement to make this available via PRPC as part of a process for data validation?

September 28, 2015 - 9:29am
Response to nistr

I have to insert data from a excel sheet.I need all these to do from an UI.

September 28, 2015 - 8:51pm

Hi Dhritiman,

Similar feature exists in Application Profile wizard in 6.2 version. If you have not already done, tracing this might help you with rules that needs to be implemented. This feature does not have step of showing parsed content (from uploaded excel sheet) on UI screen. It directly inserts all rows from Excel sheet into database.

Application menu -> New Application -> Application Profile. In this wizard Import feature is used in last step of wizard: 'Project Explorer'.

InsertDataFromExcel.png

Hope this helps you.

Murali...

September 29, 2015 - 9:45am

Pls help me with step by step solution

October 27, 2015 - 6:55am

I am able to import excel sheet with .xlsx extension(excel file of type 2007 MS offc) using MSOParseExcelFile.But cannot be able to import excel file with .xls

Pega
November 1, 2015 - 7:07am
Response to DHRITIMANC

>>> I am able to import excel sheet with .xlsx extension(excel file of type 2007 MS offc) using MSOParseExcelFile.But cannot be able to import excel file with .xls

MSOParseExcelFile works for xlsx format.

November 9, 2015 - 8:02am
Response to sahup1

I know that that's why I mentioned MSOParseExcelFile .But I want to import both .xlsx and .xls.

July 17, 2017 - 2:52am
Response to DHRITIMANC

Hi,

I have found below article, please check if it is going to be of any help.

https://pdn.pega.com/support-articles/unable-read-data-excel-sheet-when-file-type-xls

Regards,

Mohammed Sharfuddin

March 17, 2017 - 2:43pm
Response to DHRITIMANC

Hi

Do you mind sharing the step by step process you did to import the excel file?

Appreciate your time, thanks.