C# GetOleDdSchemaTable get columns from excel when sheet name has blank spaces

Question

I'm using OleDb to query data from an Excel spreadsheet. The below code works perfectly for sheets whose names have not blank spaces (e.g.: CustomersFromGermany). However for sheets whose names have blank spaces, the code doesn't return any columns (e.g. Customers From Germany).

 public List<string> GetColumnNames(string filePath, string sheetName) // modify the parameter to be only the file path and the sheetName
    {

        List<string> columns = new List<string>();

        using (OleDbConnection connection = new OleDbConnection((filePath.TrimEnd().ToLower().EndsWith("x")) ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + filePath + "';" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'"
            : "provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + filePath + "';Extended Properties=Excel 8.0;"))
        {
            connection.Open();


            // Attempts described below - below code snipet.


            DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, sheetName, null });

            foreach (DataRow drColumn in dt.Rows)
            {
                string s = Convert.ToString(drColumn["COLUMN_NAME"]);
                columns.Add(s);
            }
            connection.Close();
        }
        return columns;
    }

I have already tried to modify the sheetName string before calling the GetOleDbSchemaTable (below are the two ways how I tried), but none of the solutions worked.

1st attempt - insert two brackets between the name / also with brackets + single quotation marks:

sheetName = String.Format("[{0}$]", sheetName);

sheetName = String.Format("['{0}$']", sheetName);

and the 2nd attempt -

 if (sheetName.Contains(' '))
       sheetName = Regex.Match(sheetName, @"(?<=')(.*?)(?=\$')", RegexOptions.None).Value + "$";

Nothing worked so far.

Below is a screenshot of the my DataSet Visualiser when I select a sheetName whose names has blank spaces:

Note that ACE.OLEDB.12 drivers can handle .xls files perfectly, no need for the conditional in your ConnectionString like that — Parrish Husband
You need additional single quotes sheetName = String.Format("['{0}$']", sheetName);. So your table name should look like ['Customers From Germany$'] — Ulugbek Umirov
I've already tried that as well and forgot to include it in the question intially. I've already updated the question. Thanks — asa
What are the results of getting the TABLE_NAME schema collection? — Parrish Husband

Parrish Husband Parrish Husband · Accepted Answer · 2018-08-24T23:03:31

I have similar code and have never experienced what you're running into. I do the table names corrections like this, assuming my sheet names are passed in as a params array:

var fixedTableNames = tableNames.Select(t => string.Format
                            ("[{0}{1}]", t, t.EndsWith("$")
                                ? ""
                                : "$")
                            ).ToArray();

C# GetOleDdSchemaTable get columns from excel when sheet name has blank spaces

2 Answers