Issue Exporting Data with More Than 50 Columns Using "Export Data Store Entity to Excel" Plugin

Question

Hi Everyone 
 I'm currently facing an issue while exporting data from a specific table that contains more than 50 columns. I'm using the Export Data Store Entity to Excel plugin, but it throws an error during the export process. 
 Has anyone encountered a similar issue or found a workaround to export data with more than 50 columns successfully? Any suggestions or guidance would be greatly appreciated. 
 Thanks in advance!

Mike Schmitt · Accepted Answer

I believe it has a stated limit of 50 columns. 
 I believe the general suggested & supported solution, is to update the generated document with a 2nd pass through the smart service (starting at the 51st column, using the Starting Cell parameter) with any additional data. EDIT: i take this back, data is truncated below the starting row even if a completely different set of columns is written to. It's one of the reasons I find this node pretty much unusable . 
 My personal advice however would be to trim the columns you're exporting - do you truly need in excess of 50 columns? 
 I might suggest, instead, the JSON to Excel Plug-in . Though I still think you should try to reduce your used columns to something lower than 50 if possible - 100 columns in a single excel is going to be too cumbersome for most user-facing use cases.

Harsh Kumar Agarwal · Answer

As Mike suggested, there is a smart service limit of 50 columns. 
 I often encounter requirements where I need to export more than 50 columns. To handle this, I create multiple sheets, each containing a set of 50 columns. 
 Here I initialize first column of each sheet with the same unique identifier, which makes it easy to put VLOOKUP later to work in downloaded excel. 
 For example: 
 
 Columns 1&ndash;50 go into Sheet 1 
 Columns 51&ndash;100 go into Sheet 2 ...and so on. 
 
 I pass the same document for update across all sheets. Hope this helps!

Mike Schmitt · Answer

Are you going with a new sheet or going with the 51st column of the original sheet? 
 As far as I know the data only gets overwritten in rows underneath columns you actually write into, though I'd have to double check. If it's clearing data in other columns anyway, then you'd need to consider Harsh's suggestion of using an additional sheet. If you're writing to a second sheet and THAT is clearing data in the first sheet, then I'd consider that a bug which you should report. But we'll need some more details here either way. 
 (I tested myself and confirmed that the data on the same sheet IS cleared, but if you write to a new sheet, it's fine).

Mike Schmitt · Answer

The only other thing I can suggest (if you ABSOLUTELY MUST generate more than 50 columns) is the alternate approach I outlined already in my top comment above.

Shubham Aware · Answer

Smart service has a known limitation of 50 columns.
I have explained my approach in earlier discussion.

https://community.appian.com/discussions/f/data/34813/how-to-export-data-to-excel-in-a-grid-with-more-than-50-columns

Try and let me know if that works for you.

Shubham Aware · Answer

Yes you can use a GET Web API and query the data directly. In my example, I parsed the request body only to read an identifier/filter. Since you want to export all rows and are not passing any input, you can skip the body parse and directly query everything, then pass the result to exportDataSubsetToBase64().

Issue Exporting Data with More Than 50 Columns Using "Export Data Store Entity to Excel" Plugin

Top Replies