Import Bulk Excel file into Pega DB
Hi,
Need to import the xlsx file (which may contain 2 Lakhs records ) into DB table through Pega UI .
With our current design we are able to import around 80k-90k records and if try to upload 2L then it's giving Gateway timed out error .(OOTB transaction time out is 2 mins)
we can't process them in batch mode as need to perform the calculation on all records and show up the results on UI as soon as records are imported .
Please let us know if any better thought of accomplish this user case.
**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.
Tetrasoft India Private Limited
IN
Hi,
Complete error in logs may give details on the issue being faced.
Can you please share the stack trace of the error thrown?
Following link might be helpful here.
https://community.pega.com/support/support-articles/gateway-time-out-error-when-trying-export-report
Skandiabanken
SE
Getting "504 Gateway Time-Out" . I don't see any exceptions in logs but when we refresh the page then we are able to see 2L records are processed and pushed to DB table as well.
But how do we get rid of that error ?.
Tetrasoft India Private Limited
IN
If you are looking for a work around, following methodology should be useful, which imports data in database without using Data Table:
https://docs-previous.pega.com/configuring-application-bulk-upload-data-using-excel-spreadsheet
https://pdn.pega.com/comment/115842
Skandiabanken
SE
if we parse the excel(MSOParseExcel) which contains the 2L records then the stream size of clipboard gets increased . currently we are parsing one by one and push to DB and do the commit for every 3k records .
System is taking time to process the 2L records more than 2mins which is causing to 504 Gateway timed out error.
we would like to know the config element to enhance the time and impact of it .
Pegasystems Inc.
GB
To my understanding : the 'MSOParseExcel' Activity wasn't really designed to read in very large Excel files like you have here - it was something created as a utility to help out with certain Accelerators/ Wizards in PRPC - its not really a 'bulk/batch' import tool.
You might want to consider creating your own bulk-uploader using the Apache POI library (which is shipped with PRPC) : this has some low-memory footprint (streaming) modes that might be useful here.
See here for instance : https://poi.apache.org/spreadsheet/how-to.html
Intel Technology India PVT Ltd
IN
Hi John,
Thanks for your alternative, better approach. Can you please advice how to push to Pega database without using Clipboard page? I found an method that pushes to DB with clipboard page as input tools.getDatabase().save(myStepPage, false, true, false). Or is it fine to you steppage for each row and clear after commit?
Appreciate if you can suggest how to push 2L records from excel to Pega database
Cognitonic Systems
IN
Hi Bramesh, we got a requirement to export bulk data from excel sheet and save them in DB. In your post you said, now you are importing 90k data. Can you please help me the process what we need to follow to achieve this... like which rules we need to create, etc., thanks
Updated: 4 Apr 2018 14:01 EDT
Cognitonic Systems
IN
Hi Bramesh, we got a requirement to export bulk data from excel sheet and save them in DB. In your post you said, now you are importing 90k data. Can you please help me the process what we need to follow to achieve this... like which rules we need to create, etc., thanks
Pegasystems Inc.
IN
Hi,
Thank you for posting your query in the PSC. This looks like an inactive post and hence, we suggest you create a new post for your query. Click on the Write a Post button that’s available on the top right pane of this page. Once created, please reply back here with the URL of the new post.
You may also refer this discussion link as a reference in the new thread.