Unable to read large csv file with Aspose.Cells

Hi Team,


I am using Aspose.Cells to read to read CSV and Excel files, in one of the case we were trying to read a large csv file in Aspose.Cells which contains more than 2 million transaction. They issue we are facing here with ExportToDataTable method which is unable to export records after 1 million.

How i can read a lareg CSV file in Apose in multiple sheet.

Hi,


Thanks for providing us details.

Please try our latest version/fix: Aspose.Cells for .NET v8.8.1, you may download it from the Downloads section:

If you still find the issue with v8.8.1, kindly do create a sample console application (runnable), zip it and post us here to reproduce the issue on our end, we will check it soon. Also provide your template file(s) (e.g input CSV file).

Thank you.

Hi,


We have tried our code wit the latest DLL, still we are getting the similar issue.

The file we are trying to read contains more than 2 million rows, We want to export those rows in Datatable by using the exportDataTable method of Aspose.Cells. What we observed Workbook object loading the file and it contains only 1 million rows in worksheet. now are question here is where we can get the renaming rows? You quick response will be appreciated.

Below is are code which we have used.

LoadOptions lo = new LoadOptions(LoadFormat.TabDelimited);

lo.MemorySetting = MemorySetting.MemoryPreference;

lo.CheckExcelRestriction = false;

Workbook workbook = new Workbook(FileName, lo);

Worksheet wsheet = workbook.Worksheets[0];

ExportTableOptions exptableoption = new ExportTableOptions { SkipErrorValue = false, ExportColumnName = true, CheckMixedValueType = true };

dtable = wsheet.Cells.ExportDataTable(0, 0, wsheet.Cells.MaxRow, wsheet.Cells.MaxColumn, exptableoption);


In the above line at ExportDataTable we are getting Invalid Row end index provided.

Hi,


Thanks for sharing further details and sample code snippet.

Well, as requested earlier, we need your template CSV/Tab Delimited file which you are loading via Aspose.Cells APIs, so we could evaluate your issue precisely on our end. If the file is larger in size, you may zip it and upload to some free drive/space(e.g dropbox etc.) and provide shared link or URL to download it.

Also, you should update your line of code to:
e.g
Sample code:

dtable = wsheet.Cells.ExportDataTable(0, 0, wsheet.Cells.MaxDataRow + 1, wsheet.Cells.MaxDataColumn + 1, exptableoption);

Thank you.

Hi,


I cant share the file with you since it is private data of customer, as said earlier the file is contains more than 2 million rows in it. By using aspose i want to read the file and export the data in Database and for it i am using ExportToDataTable option, This method works till row number 1,048,576, if i increase any number in row end index its breaking. i tried to save the worksheet with new name and its exported only 1,048,576 rows in new file. Let me know if we can have call support on this issue.

Hi,


Thanks for providing further details.

I guess, your issue is actually a limitation of MS Excel (2007 - 2013/2016) file formats (XLSX or others etc.). For your information, MS Excel only allows you to have a maximum of 1048576 rows for a worksheet (you cannot save/load more data than this limitation), so you cannot cross this limit. Aspose.Cells follows MS Excel standards and specifications and cannot go beyond it. You can confirm this limitation by opening a new Workbook/file into Ms Excel (2007/2010 or 2013/2016) or even open your template CSV/Tab Delimited file and check the last row index. Also, see the document on MS Excel specifications and limitations:

Thank you.

Hi,


We are currently facing similar problem to read large CSV file using Aspose.Cell .NET. Is there any other approach to import CSV data into a DataTable using Aspose which can resolve this proble

Hi Tamal Mukherjee,


See my previous reply in this thread for your reference:
https://forum.aspose.com/t/31193

Could you open your template CSV file into MS Excel, what is your last row index you see in the worksheet. MS Excel only allows you to have a maximum of 1048576 rows for a worksheet (you cannot save/load more data than this limitation), so you cannot cross this limit. Aspose.Cells follows MS Excel standards and specifications and cannot go beyond it.

Thank you.

Is this still the case @Amjad_Sahi , coz now in 2019, I really want to use Aspose to write into CSV, not go via traditional FileWriter route.

Regards,
Kushagra Sahni

@kushagrasahi,

Well, could you provide us your template CSV/Tab Delimited file which you could create manually in Ms Excel. and which you are loading via Aspose.Cells APIs, so we could evaluate your issue. If the file is larger in size, you may zip it and upload to some free drive/space(e.g dropbox etc.) and provide shared link or URL to download it.

Also, open your desired CSV file into MS Excel, what is your last row index you see in the worksheet. MS Excel only allows you to have a maximum of 1048576 rows for a worksheet (you cannot save/load more data than this limitation), so you cannot cross this limit. For your information, Aspose.Cells follows MS Excel standards and specifications for MS Excel file formats and cannot go beyond it.

I somehow want to ignore the MS Excel Standard as I am anyway gonna save the file in CSV, and yes the row count is far beyond the number 1048576!

@kushagrasahi,

I am afraid, you might not be able to access the data even we load it into a workbook object mode. If you just want to open and save again for the CSV file, it is possible, but we do not think it would be meaningful or provide any benefit to you. For such kind of files, the better solution is you split the file by yourself and then import it into multiple workbooks/Worksheets. You can split the file into multi-files, or, just create one custom Reader from the file which could split the file and then load the data into cells by the custom reader.