Row limit when converting from TXT to CSV file format via Aspose.Cells in .NET

Aspose.Cells Product Family
1

hi
i am trying to do the following

workbook sourceCSV = new workbook (mybigfile.txt , loadoptions)

mybigfile is about 250,000 lines

when i cheked the workbook.cell.rows.count it takes only 65535 lines why ???

is there a limitation in this object ???

thenks ,

eyal

2

@eyalas,

Thanks for your query.

Well, which MS Excel version and Aspose.Cells version you are using? For your information rows limit depends upon MS Excel versions. MS Excel 97-2003 (XLS) can have upto 65535 rows in a worksheet. But newer versions (e.g MS Excel 2007, 2010/2013, 2016 and later (for XLSX file format)) can have 1,048,576 rows in a worksheet. Please try using our latest version (if you are not already using) as it should not be any issue. If you still find the issue, kindly do provide your sample file and paste your sample code (runnable) to evaluate the issue, we will check it soon.

3

hi ,

some more details

TxtLoadOptions loadoptions = new TxtLoadOptions(LoadFormat.CSV)
workbook sourceCSV = new workbook (mybigfile.txt , loadoptions)

** sourceCSV.FileFormat is ->>> excel97To2003 but i did not set is so is it came from the new TxtLoadOptions(LoadFormat.CSV) ???

is the loadoption can case the workbook row limitation ??
i am using
ASPOSE.CELL 18.5.1
Microsoft excel 2016

4

@eyalas,

May be your so called .txt or CSV file is XLS file, so you are getting this value for File format type. Anyways, as requested earlier, kindly do provide your sample file and paste your sample code (runnable) to evaluate the issue. We will check your issue and figure it out soon.

PS. We recommend you to give it a try to the latest version, e.g Aspose.Cells 19.8, it might fixes your issue.

5

i saw in my code that after this line
File.WriteAllBytes("@c:\Temp\x.csv",sourceCSV.SaveToStream().ToArray());
the sourceCSV.FileFormat has been change to excel97To2003 —>
then this object prop is changing

sourceCSV.FileFormat

why is it connected ? and if so why dont i get the full csv streem as it save only a partial size of the file content and immediately change is FileFormat to Excel97
i need this csv file for debug perpse and after this row i am continue of using the
sourceCSV object and it is impotent that the FileFormat will not be change to excel97

my content has a lot of content more then 65535 row size

you suggested that my file is xls that was terend to CSV , maybe i am greeting it as txt file , do i have a way to now from the txt that the file is xls that was save as csv ?

thanks , eyal

6

@eyalas,

Please do not use Workbook.SaveToStream method as this will save to XLS (older) format. For your information, SaveToStream method is an older method which saves the workbook data to XLS streams and as you know XLS has only 65536 records in a worksheet. Instead please change your code accordingly, see a sample code segment for your reference (please see the code segment and then modify your code segment accordingly).
e.g
Sample code:

............      
//Save the workbook to stream in XLSX file format.
                  MemoryStream stream = new MemoryStream();
                  workbook.Save(stream, SaveFormat.Xlsx);
                  stream.Seek(0, SeekOrigin.Begin); 
............

Let us know if you still find any issue.

7

this behavior is confusing is it not a bug ?

8

@eyalas,

No, this is not a bug. As I told you Workbook.SaveToStream will save in XLS file format which itself have limitation with having not more than 65536 rows in a sheet. For your information, XLS file format can only have 65536 rows in a single worksheet, this is the behavior of XLS file format and nothing to do with Aspose.Cells APIs.