Need to Export Process variable data in a PM to a formatted Excel sheet.

Question

Hello everyone, We have a client requirement where we need to export Client and Employee data recieved from document extraction in a PM to an Excel template provided by the client, where client data should be inserted in their respective positions in sheet 1 and the multiple employee data should be inserted accordingly in its desginated cells in sheet 2. 
 Attached are pictures of the Excel template formats for both sheets, as well as the format of the data we are receiving. 
 We tried using the Export Data Store Entity to Excel smart service in the process model, but the results were unsatisfactory. 
 We also used Appian RPA Excel operations, which provided a solution, but it took 25 minutes, which is not viable in all conditions. 
 We are looking for a more efficient solution to export data into Excel. Any tips or assistance would be greatly appreciated.

Mike Schmitt · Answer

This will probably be pretty easy once you are willing to use the [deprecated but still perfectly usable] Excel Tools Plug-in smart service, "CDT to Excel".

Mike Schmitt · Answer

You'll need to check your system log files (i.e. tomcat-stdout.log) to see if there's anything more relevant to your issue there. Unfortunately the error message you've posted here doesn't really tell us anything meaningful. 
 As a shot in the dark, you can post a screenshot of your configuration for the node you're trying - if you go to the "generate documentation" view under the process model's "Tools" menu, you can screenshot all items of the configuration at once. In particular we need to make sure your values are correct for "CDT" and "CDT fields to export", and make sure "sheet number" is set to 0 if you're trying to output to the first sheet (and 1 for the second sheet, etc).

Mike Schmitt · Answer

RithwinV2000 said: I was not sure on how to configure the "CDT" field 
 It should literally be an array of the CDT (data) you want to be inserted into the generated Excel. Usually we just pre-populate this into a PV - that is, query some data into a PV value containing an array of that CDT. For some reason you've pointed this at a constant, which I expect won't do anything useful. Maybe you're confusing it with the "Export DSE to Excel" smart service, where you pass in a Data Store Entity and it does the querying for you? Not sure - but in any case this is incorrect and won't do anything (other than crash). 
 Instead what I'd expect to see there would be like "pv!myData" where that PV is an array of the CDT you want, into which you've queried that data already (with the added bonus that you can, if needed, iterate over it after it's queried and adjust values as needed). The "cdt fields to export" value, determines what fields from the CDT will be actually pulled into the generated document (assuming your field names match, your configuration on that one looks like it's probably already correct).

Need to Export Process variable data in a PM to a formatted Excel sheet.

Top Replies