Hi, I am trying to export the result set from Stored procedure to Excel. What is the best way to achieve this? I tried using Export DSE to Excel but it is extracting only from DSE and was not able to feed the Stored procedure results.
Discussion posts and replies are publicly visible
Hi,One solution is having your stored procedure insert the data to a table that you can then use the Export Data Store Entity to Excel smart service on. Control of stored procedure execution from a process model can be done with the Execute Stored Procedure plug-in.
Sadly the Excel Tools plug-in has deprecated the "CDT to Excel" smart service, but if you somehow have access to an older version of the plugin (2.0.0 or before, IIRC), you might try that. Note that it was supposedly deprecated due to performance concerns, though I've never had any performance issues with the latest versions even after extensive use. Appian still hasn't provided any information as to when they might provide a functionally complete replacement for this component, leaving many of us stuck using old/outdated versions.
Completely agree. As Mike mentioned below, the plugin smart service was deprecated. Ans since this could result in a lot of data going in memory in a CDT, you would want to avoid that for scalability / perf reasons. There are two options: Option 1 - create a view and map to a data store entity and use the export or Option 2 - create a model that runs the stored proc that also populates a 'temp table' that then can be exported using the export ds then clear the temp table using a delete from DS (just be sure to plan to stop race conditions)Hope that helps!
You can try to export the data to CSV Format, if that meets your requirement. To do this you need to convert the data to HTML and then you can use Convert HTML to CSV smart service to generate the document.