Upload bulk data in Appian (Excel or CSV)

Best practice is to split the upload into two phases:

1. upload the data to a "Staging" table - that is, load it without any indication of whether the content is valid or not, and then provide an acknowledgement to the User that the data has been uploaded and will be validated off-line
2. The "Staging" table will mirror the Excel file, and will have a few extra columns (primary key, a status to indicate if valid or not, validation error message, a "correlation" value that allows the system to communicate with the User who uploaded the data, and a column to track the status of the data (so that, at some point, it can be deleted from Staging as you don't want to keep it forever!)
3. Validate the data - build a validation process to determine that the uploaded data is fit for purpose. Understand whether the business case allows for data to be carried forwards either as individual rows or for the whole batch (i.e. if one row out of 10k rows has a validation error, should you accept the 9,999 and reject the one row, or do you have to reject the whole 10k batch?)
4. You'll need a correlation method to communicate to the User which upload you're referring to when providing validation errors, and a communication mechanism (e.g. email, or Task or some such, whatever makes sense for the role the User has)
5. Validated data should then be copied across to a target data model (this may be very similar to the Staging Table, but may also be very different, and this depends entirely on the data and the processing you want to conduct on that data)

Thank you Steward for the quick response. Just couple of questions,

1. Do you suggest using 'Import Excel to Database' smart service and 'New Column' parameter for additional column requirements. Is the smart service reliable and can we expect standard updates to the plugins for feature releases. Or do you suggest a custom approach.

2. Any performance issues if executed during business hours. If not, can you advise how this works technically to handle large dataset.

• I'm not entirely familiar with the plug-ins that can be used (I believe 'Excel Tools' supports both CSV-to-Database and Excel-to-Database and you'd' best refer to the specific documentation for each option to see what works best for you. Plug-ins tend to be stable between releases of Appian but nothing is guaranteed, so you need to manage this risk between releases. Typically this means conducting aggression testing in one upgraded environment to establish whether it's safe to progress the upgrade to the remainder of your environments. 
• A quick look at the Excel tools plug-in suggests that you can batch the transfer from the uploaded document to the database. If you're concerned about any impacts on the performance of your environment during business hours you could easily design a pattern that separates the upload of the document from the transfer of the content to the database, so you could defer the transfer to an overnight window. This brings with it two issues:
  1. support - having to call out someone overnight if something goes wrong!
  2. a larger time-frame between uploading the file and any feedback to the User regarding any validation errors.
• neither issue is "wrong" they're just the consequences of the choices you have to make.

Thank you Stewart. You have summarized on most of the questions I had on this topic. My only concern on plugins is that some plugins were deprecated in future releases of Appian. And, if I consider using plugins (Import Excel or CSV) as part of critical data load solutions, these plugins should only be enhanced/ upgraded and not to be deprecated. I am new to Appian, so not sure how & when a plugin will be deprecated, and will I find an alternate solution in such case.

By their very nature plug-ins a re "plugging' a gap in the Appian functionality (either a functional gap or an ease-of-use gap) and tend to be well supported until such time as the gap is filled by native Appian functionality. Like all choices it comes with a risk - if you choose NOT to use a plug-in you have to accept a lesser option (or even forego being able to do at all what you want to do - e.g. instead of uploading a file of 10k rows and 200 columns you could implement a Process/UI to let User key in their data directly! That might not be palatable but it IS a choice with a different risk profile)). If you choose to use a plug-in then the risk of it breaking after a new release of Appian has to be managed as described earlier.

Thank you Stewart for sharing your valuable insight. 

Thank you Stewart for sharing your valuable insight.