ExcelDataReader how to set column types before putting data into table when using AsDataSet

Question

I am trying to get an excel spreadsheet into a datatable. The problem I have right now is that empty cells get imported into the datatable as empty Object{} instead of nulls. My code for getting the data into the dataset is as follows:

var result = reader.AsDataSet(new ExcelDataSetConfiguration()
    {
        ConfigureDataTable = (DataTableReader) => new ExcelDataTableConfiguration()
        {
            UseHeaderRow = true,
            

        }
         
    });


var table = result.Tables.Cast<DataTable>().First();

My current solution is to create a new datatable with the right columns and column datatypes and then clone the table from the excel sheet into the new table. Unfortunately this means running Import row 90000 times with the current size of my sheet like this:

DataTable dataTableWithCorrectColumnTypes = table.Clone();
dataTableWithCorrectColumnTypes.Columns[0].DataType = typeof(float);
dataTableWithCorrectColumnTypes.Columns[1].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[2].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[3].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[4].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[5].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[6].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[7].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[8].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[9].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[10].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[11].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[12].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[13].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[14].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[15].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[16].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[17].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[18].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[19].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[20].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[21].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[22].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[23].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[24].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[25].DataType = typeof(string);
dataTableWithCorrectColumnTypes.Columns[26].DataType = typeof(DateTime);
dataTableWithCorrectColumnTypes.Columns[27].DataType = typeof(DateTime);
dataTableWithCorrectColumnTypes.Columns.Add("DateCreated", typeof(DateTime));
dataTableWithCorrectColumnTypes.Columns[28].DefaultValue = DateTime.Now;
int rowCounter = 0;
foreach (DataRow row in table.Rows)
{
    dataTableWithCorrectColumnTypes.ImportRow(row);
}

If anyone has a solution on how to bulk insert the rows faster or too force the column types before putting the data into the datatable I would be very appreciative. Thanks

Answer 1

Avoid using ImportRow when dealing with large amounts of data. The logic of converting empty cells to null can be handled directly by checking and converting each cell before adding the row of data to the new DataTable.. Below is the modified sample code.

using (var reader = ExcelReaderFactory.CreateReader(stream))
{
    var result = reader.AsDataSet(new ExcelDataSetConfiguration()
    {
        ConfigureDataTable = _ => new ExcelDataTableConfiguration()
        {
            UseHeaderRow = true
        }
    });

    var table = result.Tables.Cast<DataTable>().First();
    DataTable dataTableWithCorrectColumnTypes = table.Clone();

    dataTableWithCorrectColumnTypes.Columns[0].DataType = typeof(float);
    dataTableWithCorrectColumnTypes.Columns[1].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[2].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[3].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[4].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[5].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[6].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[7].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[8].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[9].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[10].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[11].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[12].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[13].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[14].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[15].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[16].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[17].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[18].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[19].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[20].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[21].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[22].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[23].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[24].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[25].DataType = typeof(string);
    dataTableWithCorrectColumnTypes.Columns[26].DataType = typeof(DateTime);
    dataTableWithCorrectColumnTypes.Columns[27].DataType = typeof(DateTime);

    dataTableWithCorrectColumnTypes.Columns.Add("DateCreated", typeof(DateTime));
    foreach (DataColumn column in dataTableWithCorrectColumnTypes.Columns)
    {
        if (column.DataType == typeof(string))
        {
            column.AllowDBNull = true;
        }
    }

    foreach (DataRow originalRow in table.Rows)
    {
        DataRow newRow = dataTableWithCorrectColumnTypes.NewRow();

        for (int i = 0; i < table.Columns.Count; i++)
        {

            newRow[i] = originalRow[i] == DBNull.Value ? DBNull.Value : originalRow[i];
        }

        newRow["DateCreated"] = DateTime.Now;

        dataTableWithCorrectColumnTypes.Rows.Add(newRow);
    }
    
}