Excel column name containing '.' converted to '#'

Question

I am importing an excel file and trying to get the same into a DataSet. The problem is that if the Column Name contains . for example if the column name is Sr. No. after import the column name shown in the DataSet is Sr# No#.

The following is the code:

public DataSet ConnectExcel(string filetable)
{
    DataSet ds = new DataSet(TABLE_NAME);
    string excelConnectionString;
    string extension = Path.GetExtension(filetable);

    if (extension == ".xls")
    {
        excelConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", strCSVFilePath);
    }
    else
    {
         excelConnectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 xml;HDR=YES;\"", strCSVFilePath);
    }

    System.Data.OleDb.OleDbConnection oleDBCon = new System.Data.OleDb.OleDbConnection(excelConnectionString);

    try
    {
        if (oleDBCon.State == ConnectionState.Open)
        {
            //lblResult.Text = "file is in open state. Please close the file.";
        }
        else
        {
            oleDBCon.Open();
            DataTable Sheets = oleDBCon.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
            foreach (DataRow dr in Sheets.Rows)
            {
                string sht = dr[2].ToString().Replace("'", "");
                System.Data.OleDb.OleDbDataAdapter dataAdapter = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sht + "]", oleDBCon);

                try
                {
                    dataAdapter.Fill(ds);
                    ds.Tables[0].TableName = TABLE_NAME;
                    ds.AcceptChanges();
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
                finally
                {
                    dataAdapter.Dispose();
                }
            }
        }
    }
    catch (Exception e) //Error
    {
        MessageBox.Show(e.Message);
    }
    finally
    {
        if (oleDBCon.State == ConnectionState.Open)
            oleDBCon.Close();
        oleDBCon.Dispose();
    }
    return ds;
}

Answer 1

You have a few options here.

In your original data you can put a ' before any values you don't want converted.

You can also click on each column in the excel spreadsheet and change its data type from General to whatever format you want.

Your third option is to use IMEX=1 in your connection string.

if (extension == ".xls")
{
    excelConnectionString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"", strCSVFilePath);
}
else
{
     excelConnectionString = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 xml;HDR=YES;IMEX=1;\"", strCSVFilePath);
}

If you still have problems after changing the connection string you are going to have to change HDR to NO and treat your header column as strings as well. In this case you can replace the Column Names with the data in the first row (The actual Column Names) and then remove the first row from your data table using ds.Tables[0].Rows.RemoveAt(0);