How to remove empty rows and extra columns from datatable

Question

I have a process to import data from excel to DB using ExcelReaderFactory. But when there is empty rows/column, we are facing issue. Below is my original code:

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileContent);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();                    
DataTable dataTable = result.Tables[0].Rows

It creates 2 issues:

If there is empty rows in end, they will be there in datatable.
If there is empty colmns in the end, they will be there in datatable.

Is there any way to remove both empty rows and column. I can remove empty rows from datatable using below code

IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(fileContent);
excelReader.IsFirstRowAsColumnNames = true;
DataSet result = excelReader.AsDataSet();

DataTable dataTable = result.Tables[0].Rows
                    .Cast<DataRow>()
                    .Where(row => !row.ItemArray.All(field => field is DBNull ||
                                                    string.IsNullOrWhiteSpace(field as string ?? field.ToString())))
                    .CopyToDataTable();

return dataTable;

But it will not remove empty columns. Is there a better way to do it?

How to remove also empty columns?

Please find below image for reference.

why _color, option and _extension are not considered empty columns? — Sunil, May 18 '18 at 11:44
@Sunil, From column name you can see, these column is user defined and left blank. But Column6, Column7, Column8 are due to empty column. — user1926138, May 18 '18 at 11:52
Try using microsoft.ace.oledb instead of OpenXMl lib. You may not have this issue then — T.S., May 18 '18 at 11:54
@T.S. Yes, that will fix the issue, but can't switch to oledb now — user1926138, May 18 '18 at 11:59
try to delete the unwanted columns and rows in excel before import and save the excel reopen and make sure if you press the CTRL + END in excel should be the last row last column of the table. — Karthick Gunasekaran, May 18 '18 at 12:12
@KarthickGunasekaran, It is best solution, but cannot force end user to do it. — user1926138, May 18 '18 at 12:29

Tim Schmelter · Answer 1 · 2018-05-18T11:58:17.520

You could use this extension:

public static void RemoveEmptyColumns(this DataTable table, int columnStartIndex = 0)
{
    for (int i = table.Columns.Count - 1; i >= columnStartIndex; i--)
    {
        DataColumn col = table.Columns[i];
        if (table.AsEnumerable().All(r => r.IsNull(col) || string.IsNullOrWhiteSpace(r[col].ToString())))
            table.Columns.RemoveAt(i);
    }
}

If you want to start with a given index, pass that to the method.

How to remove empty rows and extra columns from datatable

1 Answers1