Skip to main content

This content is closed to future replies and is no longer being maintained or updated.
Links may no longer function. If you have a similar request, please write a new post.

Question

16
Replies
5153
Views
DHRITIMANC Member since 2014 8 posts
TATA Consultancy Services Ltd
Posted: Sep 28, 2015
Last activity: Jul 17, 2017
Closed
Solved

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.

To see attachments, please log in.
Data Integration

Posted: 9 years ago

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..

To see attachments, please log in.
Posted: 9 years ago

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.

To see attachments, please log in.
Posted: 9 years ago

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

To see attachments, please log in.
Posted: 9 years ago

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 ...

To see attachments, please log in.
Posted: 9 years ago

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.

To see attachments, please log in.
Posted: 9 years ago

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

To see attachments, please log in.
Posted: 9 years ago

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...

To see attachments, please log in.
Posted: 9 years ago

>>> 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.

To see attachments, please log in.

We'd prefer it if you saw us at our best.

Pega Collaboration Center has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice