We have delegated a data type in production for a access group. They will import the data from excel which has more than 1 million records through the OOTB import option in the data type. It takes more than 20 minutes to import the records and some times gateway time out occurs for the requester. What are the ways through which we can reduce the import time and improve the performance?
Data type import performance
if we parse the excel containing millions of records then the stream size of clipboard gets increased so even though the operation is partially succesful you may get gateway timeout
You can try apache.poi library as it has a little less memory footprint or can follow below link
https://community.pega.com/knowledgebase/articles/configuring-application-bulk-upload-data-using-excel-spreadsheet
Ragul
On-screen upload is not a recommended approach when your digits are huge. We would recommend you to try out the below approaches.
- Place the file to the Pega SFTP using OOTB file upload controls (Eg; FilePath) and then have File Listener to process the request through proper balancing.
- Have smaller chunks of the file (Max 10k records in one file) and try the same upload on-screen.
- Try uploading the file to the dataset and use data flow with partitioning to write the records to the destination table. DataSet can be sourced from a CSV file.
Let us know if you need any further information from us.
ospteam I also have another doubt. Regarding parsing the csv file in pega. If any column contains comma(,) excel adds the column values in double quotes. For eg. Consider a column named Address and the corresponding value is something like OMR, Chennai , excel converts this column value as "OMR,Chennai" . In the parse delimited rule we have the delimiter as , and the escape character as \ . We wrote a function to convert the column values in doible quotes to something like OMR\,Chennai. Is there any other way in OOTB to achieve this?