Hello Everyone, We have a scenario where an user will upload an excel

shailjas over 8 years ago

Hello Everyone,

We have a scenario where an user will upload an excel file containing data and system will parse the data and write the details to the database.
Each record in the excel corresponds to a record in the data base. We are using the Read Excel plugin to parse and Appian Write to data stores to write to the database.
There is no upper limit on the number of records in the excel. User can upload 40000 or even more records. If we try to write such large data sets into the database, it is causing a server outage.

Also, after the data is written to the database, an approval task is generated. In this task, an approver can upload a new version of the Excel File.
If a new version is available, then all the records written to the database earlier must be deleted and new records will be written for the case.

We would like to know the various design approaches we can consider to write and delete large number of records into the database.

Discussion posts and replies are publicly visible

Parents

0 mohanp over 8 years ago

Hi Shailja, AFAIK
To Read a excel file, you may also utilize "Parse Excel Spreadsheet to CDT".
To Write CDT data into DB, you can design batch size operation, i.e,
1) Get first 1-1000 index values from CDT
2) Store return data into temporary process variable of same data type
3) Pass this temp variable to a sub process which will write CDT data into Database.
4) Once data is written reset your temp variable with null value.
5) Get another 1001-2000 index values and repeat step 2 to 5 until you finish writing all the data.
6) Delete process instance as soon as you finsh all writing activities.

Addition to this I have few questions on your requirement.
1) When Approver is uploading a new version of file, Is all the data is different than what previous data has been written? (i.e, all rows and column values are different or only few rows/column values are different) If all rows are not modified than it's better you indentify which rows are modified and only update modfied rows to DB.
2) Is there any specific need to write data into Database before Approval task? if not,I would suggest you to write data once when you have a final file from the Approval Task?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Reply

0 mohanp over 8 years ago

Hi Shailja, AFAIK
To Read a excel file, you may also utilize "Parse Excel Spreadsheet to CDT".
To Write CDT data into DB, you can design batch size operation, i.e,
1) Get first 1-1000 index values from CDT
2) Store return data into temporary process variable of same data type
3) Pass this temp variable to a sub process which will write CDT data into Database.
4) Once data is written reset your temp variable with null value.
5) Get another 1001-2000 index values and repeat step 2 to 5 until you finish writing all the data.
6) Delete process instance as soon as you finsh all writing activities.

Addition to this I have few questions on your requirement.
1) When Approver is uploading a new version of file, Is all the data is different than what previous data has been written? (i.e, all rows and column values are different or only few rows/column values are different) If all rows are not modified than it's better you indentify which rows are modified and only update modfied rows to DB.
2) Is there any specific need to write data into Database before Approval task? if not,I would suggest you to write data once when you have a final file from the Approval Task?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel

Children

No Data