CODEWITHSUNDEEP
c#datasetoledb
user3759926
user3759926

Reputation: 11

DataSet using first value as column header

I have a piece of code where I want to extract values from the A column of an Excel sheet. Right now, here is the code I'm using and having an issue with:

m_connString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source = " + m_source + "; Extended properties = 'Excel 12.0; HDR = NO; IMEX = 1;';";

using (OleDbConnection conn = new OleDbConnection(m_connString)) 
{

  conn.Open();
  DataTable dt = conn.GetOleDbSchemaGuid(Tables, null);
  DataSet ds = new DataSet();
  string defaultSheet = ExcelSheets.Rows[0]["TABLE_NAME"].ToString();
  OleDbCommand comm = new OleDbCommand("SELECT * FROM [" + defaultSheet + "]", conn);
  OleDbDataAdapter adapter = new OleDbDataAdapter(comm);

  // Bug appears here
  adapter.fill(ds)


  // Fill a List<string> with the data found
  for (int r = 0; r < ds.Tables[0].Row.Count; r++)
  {
    m_list.Add(ds.Tables[0].Rows[r][0].ToString();
  }

}

What is happening is that, if I have an Excel file with the following content in the A column:

Row1
Row2
Row3
...
RowX

...What I end up getting is all values except for the first value (Row1). It turns out that Row1 is being used as a column name(?) for the DataSet's table. However, I don't want there to be any column names or headers, and I specifically state this in the connection string.

How can I prevent this behavior so I can have all my data placed in the List? Or, failing that, how can I work around this issue and extract Row1 from that DataSet?

Upvotes: 1

Views: 7270

Answers (3)

Hiral Nayak
Hiral Nayak

Reputation: 1082

Try this way:

    DataTable table = ds.Tables[0];

    foreach (DataColumn column in table.Columns)
    {
        string cName = table.Rows[0][column.ColumnName].ToString();
        if (!table.Columns.Contains(cName) && cName != "")
        {
            column.ColumnName = cName;
        }
    }

Upvotes: 1

Hassan
Hassan

Reputation: 5430

Check Microsoft Reference to understand how Extended Properties of the connection HDR=NO works:

Column headings: By default, it is assumed that the first row of your Excel data source contains columns headings that can be used as field names. If this is not the case, you must turn this setting off, or your first row of data "disappears" to be used as field names. This is done by adding the optional HDR= setting to the Extended Properties of the connection string. The default, which does not need to be specified, is HDR=Yes. If you do not have column headings, you need to specify HDR=No; the provider names your fields F1, F2, etc.

Here is example:

Excel File Data (test.xlsx):

enter image description here

Code:

string m_source = "test.xlsx";    
string m_connString = @"Provider = Microsoft.ACE.OLEDB.12.0; 
                        Data Source = " + m_source + @"; 
                        Extended properties = 'Excel 12.0; 
                        HDR= NO; 
                        IMEX = 1;';";

using (OleDbConnection conn = new OleDbConnection(m_connString)) 
{
     conn.Open();         
     string squery = "SELECT f1, f2, f3 FROM [Sheet1$]"; 
     OleDbCommand comm = new OleDbCommand(squery, conn);
     OleDbDataAdapter adapter = new OleDbDataAdapter(comm);                
     DataSet ds = new DataSet();
     adapter.Fill(ds);        
}

DataSet Visualizer:

enter image description here

Upvotes: 2

Jed I
Jed I

Reputation: 1038

change the sql to something along the lines of this

"SELECT * FROM [" + defaultSheet + "] Except  Select Top(1)"

Upvotes: 0

Related Questions