Trimming all cells in DataTable

Question

I am using the below code to trim all cells in my DataTable.

The problem is, that I am doing it through a loop, and depending on what I fill the DataTable with, if it has 1500 rows and 20 columns, the loop takes a really, really long time.

DataColumn[] stringColumns = dtDataTable.Columns.Cast<DataColumn>().Where(c => c.DataType == typeof(string)).ToArray();
foreach (DataRow row in dtDataTable.Rows)
{
    foreach (DataColumn col in stringColumns)
    {
        if (row[col] != DBNull.Value)
        {
            row.SetField<string>(col, row.Field<string>(col).Trim());
        }
    }
}

And here is how I am importing my Excel sheet to the DataTable:

using (OpenFileDialog ofd = new OpenFileDialog() { Title = "Select File", Filter = "Excel WorkBook|*.xlsx|Excel WorkBook 97-2003|*.xls|All Files(*.*)|*.*", Multiselect = false, ValidateNames = true })
{               
    if (ofd.ShowDialog() == DialogResult.OK)
    {
        String PathName = ofd.FileName;
        FileName = System.IO.Path.GetFileNameWithoutExtension(ofd.FileName);
            
        strConn = string.Empty;

        FileInfo file = new FileInfo(PathName);
        if (!file.Exists) { throw new Exception("Error, file doesn't exists!"); }
        string extension = file.Extension;
        switch (extension)
        {
            case ".xls":
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            case ".xlsx":
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + PathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
            default:
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + PathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
        }
    }
    else
    {
        return;
    }
}
    
using (OleDbConnection cnnxls = new OleDbConnection(strConn))
{
    using (OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", "Sheet1"), cnnxls))
    {
        oda.Fill(dtDataTableInitial);
    }
}


//Clone dtDataTableInitial so that I can have the new DataTable in String Type
dtDataTable = dtDataImportInitial.Clone();
foreach (DataColumn col in dtDataTable.Columns)
{
    col.DataType = typeof(string);
}
foreach (DataRow row in dtDataImportInitial.Rows)
{
    dtDataTable.ImportRow(row);
}

Is there a more efficient way of accomplishing this?





EDIT: As per JQSOFT's suggestion, I am using OleDbDataReader now, but am still running two issues:

One: SELECT RTRIM(LTRIM(*)) FROM [Sheet1$] doesn't seem to work.

I know that it is possible to select each column one by one, but the number of and header of the columns in the excel sheet is random, and I am not sure how to adjust my SELECT string to account for this.

Two: A column whose rows are mostly populated with numbers, but have a few rows with letters seem to have those rows with letters omitted. For example:
Col1
1
2
3
4
5
6
a
b

Becomes:
Col1
1
2
3
4
5
6

However, I have discovered that if I manually go into the excel sheet and convert the entire table cell format to "Text", this issue is resolved. However, doing this converts any dates in that excel sheet into unrecognizable strings of numbers, so I want to avoid doing this if at all possible.
For example: 7/2/2020 becomes 44014 if converted to "Text".



Here is my new code:

private void Something()
{
    if (ofd.ShowDialog() == DialogResult.OK)
    {
        PathName = ofd.FileName;
        FileName = System.IO.Path.GetFileNameWithoutExtension(ofd.FileName);

        strConn = string.Empty;

        FileInfo file = new FileInfo(PathName);
        if (!file.Exists) { throw new Exception("Error, file doesn't exists!"); }
    }

    using (OleDbConnection cn = new OleDbConnection { ConnectionString = ConnectionString(PathName, "No") })
    {
        using (OleDbCommand cmd = new OleDbCommand { CommandText = query, Connection = cn })
        {
            cn.Open();
            OleDbDataReader dr = cmd.ExecuteReader();
            dtDataTable.Load(dr);
        }
    }

    dataGridView1.DataSource = dtDataTable;
}               

public string ConnectionString(string FileName, string Header)
{
    OleDbConnectionStringBuilder Builder = new OleDbConnectionStringBuilder();
    if (Path.GetExtension(FileName).ToUpper() == ".XLS")
    {
        Builder.Provider = "Microsoft.Jet.OLEDB.4.0";
        Builder.Add("Extended Properties", string.Format("Excel 8.0;IMEX=1;HDR=Yes;", Header));
    }
    else
    {
        Builder.Provider = "Microsoft.ACE.OLEDB.12.0";
        Builder.Add("Extended Properties", string.Format("Excel 12.0;IMEX=1;HDR=Yes;", Header));
    }

    Builder.DataSource = FileName;

    return Builder.ConnectionString;
}

Answer 1

OleDb Objects

---

Actually what I meant is, to get formatted/trimmed string values from the Excel Sheet and create a DataTable with DataColumn objects of string type only, use the forward-only OleDbDataReader to create both, DataColumn and DataRow objects as it reads. Doing so, the data will be modified and filled in one step hence no need to call another routine to loop again and waste some more time. Also, consider using asynchronous calls to speed up the process and avoid freezing the UI while executing the lengthy task.

Something might help you to go:

private async void TheCaller()
{
    using (var ofd = new OpenFileDialog
    {
        Title = "Select File",
        Filter = "Excel WorkBook|*.xlsx|Excel WorkBook 97 - 2003|*.xls|All Files(*.*)|*.*",
        AutoUpgradeEnabled = true,
    })
    {
        if (ofd.ShowDialog() != DialogResult.OK) return;

        var conString = string.Empty;
        var msg = "Loading... Please wait.";

        try
        {
            switch (ofd.FilterIndex)
            {
                case 1: //xlsx
                    conString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={ofd.FileName};Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";                            
                    break;
                case 2: //xls
                    conString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={ofd.FileName};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
                    break;
                default:
                    throw new FileFormatException();
            }

            var sheetName = "sheet1";
            var dt = new DataTable();

            //Optional: a label to show the current status
            //or maybe show a ProgressBar with ProgressBarStyle = Marquee
            lblStatus.Text = msg;

            await Task.Run(() =>
            {
                using (var con = new OleDbConnection(conString))
                using (var cmd = new OleDbCommand($"SELECT * From [{sheetName}$]", con))
                {
                    con.Open();

                    using (var r = cmd.ExecuteReader())
                        while (r.Read())
                        {
                            if (dt.Columns.Count == 0)
                                for (var i = 0; i < r.FieldCount; i++)
                                    dt.Columns.Add(r.GetName(i).Trim(), typeof(string));

                            object[] values = new object[r.FieldCount];

                            r.GetValues(values);
                            dt.Rows.Add(values.Select(x => x?.ToString().Trim()).ToArray());
                        }
                }
            });

            //If you want...
            dataGridView1.DataSource = null;
            dataGridView1.DataSource = dt;

            msg = "Loading Completed";
        }
        catch (FileFormatException)
        {
            msg = "Unknown Excel file!";
        }
        catch (Exception ex)
        {
            msg = ex.Message;
        }
        finally
        {
            lblStatus.Text = msg;
        }
    }
}

Here's a demo, reading sheets with 8 columns and 5000 rows from both xls and xlsx files:

Less than a second. Not bad.

However, this will not work correctly if the Sheet has mixed-types columns like your case where the third column has string and int values in different rows. That because the data type of a column is guessed in Excel by examining the first 8 rows by default. Changing this behavior requires changing the registry value of TypeGuessRows in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\x.0\Engines\Excel from 8 to 0 to force checking all the rows instead of just the first 8. This action will dramatically slow down the performance.

Office Interop Objects

---

Alternatively, you could use the Microsoft.Office.Interop.Excel objects to read the Excel Sheet, get and format the values of the cells regardless of their types.

using Excel = Microsoft.Office.Interop.Excel;
//...

private async void TheCaller()
{
    using (var ofd = new OpenFileDialog
    {
        Title = "Select File",
        Filter = "Excel WorkBook|*.xlsx|Excel WorkBook 97 - 2003|*.xls|All Files(*.*)|*.*",
        AutoUpgradeEnabled = true,
    })
    {
        if (ofd.ShowDialog() != DialogResult.OK) return;

        var msg = "Loading... Please wait.";
        Excel.Application xlApp = null;
        Excel.Workbook xlWorkBook = null;

        try
        {
            var dt = new DataTable();

            lblStatus.Text = msg;

            await Task.Run(() =>
            {
                xlApp = new Excel.Application();
                xlWorkBook = xlApp.Workbooks.Open(ofd.FileName, Type.Missing, true);

                var xlSheet = xlWorkBook.Sheets[1] as Excel.Worksheet;
                var xlRange = xlSheet.UsedRange;

                dt.Columns.AddRange((xlRange.Rows[xlRange.Row] as Excel.Range)
                .Cells.Cast<Excel.Range>()
                .Where(h => h.Value2 != null)
                .Select(h => new DataColumn(h.Value2.ToString()
                .Trim(), typeof(string))).ToArray());

                foreach (var r in xlRange.Rows.Cast<Excel.Range>().Skip(1))
                    dt.Rows.Add(r.Cells.Cast<Excel.Range>()
                        .Take(dt.Columns.Count)
                        .Select(v => v.Value2 is null
                        ? string.Empty
                        : v.Value2.ToString().Trim()).ToArray());
            });

            (dataGridView1.DataSource as DataTable)?.Dispose();
            dataGridView1.DataSource = null;
            dataGridView1.DataSource = dt;

            msg = "Loading Completed";
        }
        catch (FileFormatException)
        {
            msg = "Unknown Excel file!";
        }
        catch (Exception ex)
        {
            msg = ex.Message;
        }
        finally
        {
            xlWorkBook?.Close(false);
            xlApp?.Quit();

            Marshal.FinalReleaseComObject(xlWorkBook);
            Marshal.FinalReleaseComObject(xlApp);

            xlWorkBook = null;
            xlApp = null;

            GC.Collect();
            GC.WaitForPendingFinalizers();

            lblStatus.Text = msg;
        }
    }
}

^{Note: You need to add reference to the mentioned library.}

Not fast especially with a big number of cells but it gets the desired output.