ImportDataTable throws System.OutOfMemoryException

[Paul Meems]

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]

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. 

UG link : https://help.syncfusion.com/file-formats/xlsio/improving-performance#importing-datatable  

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

Regards, 

Sivaneswaran . A

[Paul Meems]

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]

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. 

Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/Exception_Issue-377320235.zip 

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]

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]

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. 

https://www.syncfusion.com/feedback/6694/add-overload-for-accepting-both-importonsave-and-isfieldnameshown-options-on-importing-data-table 

Regards, 

Abirami 

[Paul Meems]

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]

Hi Paul, 

Thank you for updating us. 

We will update you once the enhancement is implemented. 

Regards, 

Abirami 

[Abirami Varadharajan]

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.  

   

https://www.syncfusion.com/forums/147704/essential-studio-2019-volume-3-beta-release-v17-3-0-9-is-available-for-download 

Sample Link: 

https://www.syncfusion.com/downloads/support/directtrac/general/ze/ImprotOnSaveOptionwithImportColumnHeader-1687959-1300602664.zip  

Documentation Link: https://help.syncfusion.com/cr/file-formats/Syncfusion.XlsIO.IWorksheet.html#Syncfusion_XlsIO_IWorksheet_ImportDataTable_System_Data_DataTable_System_Int32_System_Int32_System_Boolean_System_Boolean_  

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