Read columns names issue with reader.GetName and OLEDB Excel provider

Question

I have an issue to retrieve the columns names in an Excel sheet.

I have an Excel sheet with only 3 cells in the first row with these 3 values:

• in A1: A

• in B1: B

• in C1: A.B.C

When I try to execute my method the label shows:

• A,B,A#B#C

And not:

• A,B,A.B.C

My Code:

    protected void btnExecute_Click(object sender, EventArgs e)
    {
        string fullFileName = @"C:\TEST.xls";
        List<string> columns = new List<string>();

        string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;", fullFileName);

        using (OleDbConnection conn = new OleDbConnection(connectionString))
        {
            conn.Open();

            // Retrieves the first sheet
            DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string firstSheet = dt.Rows[0]["TABLE_NAME"].ToString();

            // Retrieves the list column name
            string query = string.Format("SELECT TOP 1 * FROM [{0}]", firstSheet);
            OleDbCommand cmd = new OleDbCommand(query, conn);
            OleDbDataReader reader = cmd.ExecuteReader();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                columns.Add(reader.GetName(i));
            }
        }

        lblCols.Text = string.Join(",", columns.ToArray());
    }

Do you have an idea to fix this issue??

Thanks in advance.

Daniel

Answer 1

Try this OleDBAdapter Excel QA I posted via stack overflow.

I just tested this and it picked up "A.B.C" from a sample .xls you described.
i.e. add the following to the bottom for a quick test:

Object o = ds.Tables["xlsImport"].Rows[0]["LocationID"];
Object oa = ds.Tables["xlsImport"].Rows[0]["PartID"];
Object row0Col3 = ds.Tables["xlsImport"].Rows[0][2];

string valLocationID = o.ToString();
string valPartID = oa.ToString();
string rowZeroColumn3 = row0Col3.ToString();