Export SQL to pipe delimited text file (row limitation)

Aspose.Cells Product Family
1

I’m trying to get around some Excel row limitations. Here is the sample code I’m using:


Aspose.Cells.Workbook excel = new Aspose.Cells.Workbook();
string sSql = @" SELECT FieldName from Table"; //returns 2 million plus rows.

Aspose.Cells.Worksheet sheet = excel.Worksheets[0];
//…get sqlDataReader
sheet.Cells.ImportDataReader(sqlDataReader, true, 0, 0, false);

Aspose.Cells.TxtSaveOptions options = new Aspose.Cells.TxtSaveOptions();
options.Separator = Convert.ToChar("|");
excel.Save(“c:\temp\asposeoutput” + DateTime.Now.ToFileTimeUtc() + “.txt”, options);

The problem I’m having is that the output file is stopped after 1048576 rows as is limited by Excel.

Is there another ASPOSE way of taking a SQL query and putting it into a pipe delimited txt file when the number of rows exceeds the 1048576 mark (without breaking up the SQL query to do it in parts – not easily done in my case) ?

Thanks.
2

Hi,


Thanks for providing us some details and sample code.

Well, in MS Excel (2007/2010 and 2013), there is a restriction of storing number of records in a worksheet, i.e., you can have upto 1048576 rows only, you may confirm this in MS Excel manually. How could you create such a Text (delimited) file with this number of records in MS Excel manually? You cannot create such a TXT/CSV file in MS Excel with more than 1048576 records in the sheet. Aspose.Cells follows Ms Exel standards, so you cannot create a Text/CSV (Delimited) file with more than 1048576 rows via Aspose.Cells APIs.

Thank you.