Export data to excel with millions of records and more than 130 columns

Question

Hi Team, 
 
 I almost have 2 Lakhs records and 134 columns to export into excel sheet from the Appian database. I am already using Export SQL to excel as Export data store entity to excel doesnt allow me to export more than 50 rows. when i do this it exports till certain set of data for example 1 Lakh records. But after that it is not exporting the data. 
 I tried exporting them in batches of 5000. so first 5000 will be exported into a template. and the next 5000 the output of this will become the template. we tried merging of Excel sheets to but it also fails after 10 Excel sheets. 
 
 Is there any other better way to do this please. 
 TIA 
 Harsha

Mike Schmitt · Answer

I know this isn't the answer you wanted but I'd strongly look into ways to satisfy your requirements in some other way than inserting 200,000 records into a single excel sheet. Even if Excel itself can handle that amount of data, I don't think any current Appian functionality can (as you've already found). 
 (FYI over here in the states we don't have the term " Lakh ", we just have to write out "200,000", "200k", "200 thousand", etc. Just pointing this out to help increase the number of people potentially able to give you useful advice.)

Acacio Barrado · Answer

Hi Harsha, 
 As Mike mentioned there is no straight forward solution for your scenario, but in saying that if you still need to implement it on Appian, you could try some workarounds to try to achieve your goal. Just bear in mind that this may not be consider best practices. 
 There may be a couple of ways to do this, depending on if you are using a view or table. 
 If you are using a view, I would suggest using a stored procedure to insert the data into different staging tables with 50 columns each. 
 If you are using a table, then you should be able to export 50 columns at a time into separate excel files. 
 Once you've got the separate excel files, you can use this plugin: 
 community.appian.com/.../excel-template-merge. 
 This will allow you to append the separate sheets into one, while retaining any formatting that you may have. 
 Let me know if you have any questions 
 Regards, 
 Acacio B.