We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. (Last updated on: June 24, 2019).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

ImportDataTable throws System.OutOfMemoryException

Thread ID:

Created:

Updated:

Platform:

Replies:

144806 May 23,2019 02:13 PM UTC Sep 23,2019 09:01 AM UTC WinForms 8
loading
Tags: XlsIO
Paul Meems
Asked On May 23, 2019 02:13 PM UTC

I'm trying to merge several Excel files into 1 file.
For this I read each file and save the data in a DataTable and I perform some additional actions on the data.

Next I want to export this DataTable using ImportDataTable().

I have several sets of files and most of them work fine, but sometimes a System.OutOfMemoryException is thrown.

This is the code:
worksheetResult.ImportDataTable(dataTable, true, 1, 1, true);

This is the exception:
System.OutOfMemoryException: 
   bij System.Collections.Generic.Dictionary`2.Resize(Int32 newSize, Boolean forceNewHashCodes)
   bij System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
   bij Syncfusion.XlsIO.Implementation.SSTDictionary.AddIncrease(Object key, Boolean bIncrease)
   bij Syncfusion.XlsIO.Implementation.WorksheetImpl.SetString(Int32 iRow, Int32 iColumn, String value)
   bij Syncfusion.XlsIO.Implementation.WorksheetImpl.ImportDataTableWithoutCheckPreserve(DataTable dataTable, Int32 firstRow, Int32 firstColumn, Int32 maxRows, Int32 maxColumns, DataColumn[] arrColumns)
   bij Syncfusion.XlsIO.Implementation.WorksheetImpl.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 maxRows, Int32 maxColumns, DataColumn[] arrDataColumns, Boolean bPreserveTypes, Boolean bImportOnSave)
   bij Syncfusion.XlsIO.Implementation.WorksheetImpl.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Boolean preserveTypes)

The DataTable has 575356 rows.

I'm using v17.1.0.47 for .NET v4.6

How to overcome this issue?

Sivaneswaran Amsu [Syncfusion]
Replied On May 24, 2019 04:45 PM UTC

Hi Paul, 

Thank you for contacting Syncfusion support. 

We have maintained the data in both list and dictionary collection internally to improve the performance which lead the higher memory consumption. We recommend you to set the following property as FALSE to avoid out of memory exception. 
 
Code Example: 
(workbook as WorkbookImpl).InnerSST.UseHashForSearching = false; 

If you are still facing issue with above suggestion, then we request you to use the import on save functionality to import large data with less memory consumption. 

Code snippet: 
************ 
worksheet.ImportDataTable(dataTable, 1, 1, true); 

Please refer the following UG documentation link to know more about the ImportOnSave. 


Kindly try this and let us know if this resolve the issue. 

Regards, 
Sivaneswaran . A

Paul Meems
Replied On May 27, 2019 08:20 AM UTC

Adding 
(workbook as WorkbookImpl).InnerSST.UseHashForSearching = false; 
Did not solve my problem. I keep getting the OutOfMemoryException

Then I tried 
worksheet.ImportDataTable(dataTable, 1, 1, true);
This results in an Error on save and an empty workbook:
ExcelWorkbookNotSavedException: Excel Binary workbook was not saved.

Next I tried splitting the datatable is smaller pieces:
const int step = 20_000;
var start = 0;
while (rowCount > start)
{
    Debug.WriteLine("Start: " + start);
    worksheetResult.ImportDataTable(dataTable.AsEnumerable().Skip(start).Take(step).CopyToDataTable(),
        start == 0,
        start == 0 ? start + 1 : start + 2, 1, true);
    start += step;
}
This doesn't work either. After 15 iterations I get the ExcelWorkbookNotSavedException again.

Next try was to use 
worksheet.ImportDataTable(dataTable, 1, 1, true);
inside the loop.
But this doesn't work either.
First of all the columns A to N are not exported. It starts at O, with the data of O. Not sure why it is skipping the first 15 columns.
Although I save the workbook after each ImportDataTable I only get data in O560002 to AZ575357

                var start = 0;
                workbookResult.SaveAs(newFileLocation, ExcelSaveType.SaveAsXLS);
                while (rowCount > start)
                {
                    Debug.WriteLine("Start: " + start);
                    worksheetResult.ImportDataTable(dataTable.AsEnumerable().Skip(start).Take(step).CopyToDataTable(),
                        start == 0 ? start + 1 : start + 2, 1, true);
                    workbookResult.Save();
                    start += step;
                }

I've attached the definition of my datatable. Could you fill it with 575356 rows and show me a working example of how to export that data into an Excel-file?


Attachment: LargeDataTableDef_88349567.7z

Abirami Varadharajan [Syncfusion]
Replied On May 30, 2019 04:12 PM UTC

Hi Paul, 

We are able to reproduce the out of memory exception issue in 32bit project due to import of large data (575356 rows and 53 columns). So, we request you to use 64bit project to import large data and save it as Excel file without any exception. We have prepared a sample in 64bit and shared for your reference which can be downloaded from the following link. 


The data is imported from column O instead of A because, UseHashForSearching property is set as FALSE. This property should not be set as FALSE when importing data table with importonsave option. So, we request you to remove that line to resolve the issue. 

Kindly try this and let us know if this resolve the issue. 

Regards, 
Abirami 


Paul Meems
Replied On May 31, 2019 02:28 PM UTC

Thanks for the sample. Changing to 64Bit did the trick.
I was building for 'Any CPU' but Visual Studio also ticked 'Prefer 32Bit', which I didn't realize.
Unticking it solved my memory problem.

I also want to add my headers as well.
I thought to be smart and use the 'normal' ImportDataTable() for the first row and use the optimized ImportDataTable() for the remaining rows.
But when using the ImportDataTable with importOnSave all other data seems to be cleared.

This is working:
   worksheet.ImportDataTable(dataTable.AsEnumerable().Take(1).CopyToDataTable(), true, 1, 1, true);
   workbook.SaveAs(newFileLocation, ExcelSaveType.SaveAsXLS);
I get my headers on my first row and my first data on the second row.

This is also working:
   worksheet.ImportDataTable(dataTable.AsEnumerable().Skip(1).CopyToDataTable(), 3, 1, true);
   workbook.SaveAs(newFileLocation, ExcelSaveType.SaveAsXLS);
The first two rows in the sheet are skipped and the first row from the datatable is skipped as well.

But when I want to combine these two, I always end up with the first two rows empty.
And it doesn't matter if I call ImportDataTable(.., true, 1, 1, true) before or after ImportDataTable(.., 3, 1, true)

Please assist one more time and explain to me how to get the headers in my file.

Abirami Varadharajan [Syncfusion]
Replied On June 3, 2019 12:50 PM UTC

Hi Paul, 

While importing datatable with importOnSave option, the values in the existing cells are cleared for performance concerns and the values only in the datatable are set to cells while saving the workbook. We don’t have support for Accepting both importOnSave and isFieldNameShown options on importing data table. However, we will consider this enhancement and include this support in our release version 17.2 SP – 1 which will be available by July 2019. 

You can track the status of this enhancement from the following link. 

Regards, 
Abirami 


Paul Meems
Replied On June 3, 2019 01:21 PM UTC

Thanks for your reply and for adding this enhancement.

For now I noticed the 'old' 
worksheetResult.ImportDataTable(dataTable, true, 1, 1, true);
is working fine with my large datatable after compiling for x64.

Abirami Varadharajan [Syncfusion]
Replied On June 4, 2019 08:54 AM UTC

Hi Paul, 

Thank you for updating us. 

We will update you once the enhancement is implemented. 

Regards, 
Abirami 


Abirami Varadharajan [Syncfusion]
Replied On September 23, 2019 09:01 AM UTC

Hi Paul 
  
We are glad to announce that our Essential Studio 2019 Volume 3 Beta Release version 17.3.0.9 is rolled out with the feature “ImportOnSave option should import column headers” and it is available for download under the following link.  
   

Sample Link: 



We thank you for your support and appreciate your patience in waiting for this release. Please get in touch with us if you would require any further assistance.  

Regards, 
Abirami 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Please sign in to access our forum

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

Live Chat Icon For mobile
Live Chat Icon