title: Retrieving values of the first column from Excel sheet (file with .xlsm extension) with OleDB
body: <p>I am trying to retrieve values of the first column from the Excel sheet called "SP$".</p>
  
  <p>I have the path:</p>
  
  <pre><code>string path = @"C:\Users\atsurkanu\Desktop" + @"\TemplateClientExtraction_IDEAFIMIT_Conero_QUARTER_20170127.xlsm";
  string connectionString = string.Format(@"provider=Microsoft.ACE.OLEDB.12.0;data source={0};Extended Properties=Excel 12.0;", path);
  string sheetName = "SP$";
  </code></pre>
  
  <p>and some code like this one:</p>
  
  <pre><code>using (OleDbConnection con = new OleDbConnection(connectionString))
          {
              try
              {                   
                  var dataTable = new DataTable();
  
                  con.Open();
                  var tableschema = con.GetSchema("Tables");
                  var firstsheet = tableschema.Rows[0]["SP$"].ToString();
                  string name_query = "SELECT A4 FROM [" + firstsheet + "]";
                  OleDbDataAdapter da = new OleDbDataAdapter(name_query, con);
                  da.Fill(dataTable);
                  con.Close();
  </code></pre>
  
  <p>But it doesn't work. Please, tell me, how I cat retrieve the first column from Excel sheet.</p>
  
  <p>UPDATE:
  I am not sure how it works, but it helps:</p>
  
  <pre><code>var dataTable = new DataTable();
  
                  con.Open();
                  var tableschema = con.GetSchema("Tables");
  
                  // To get the first sheet name you use the first row and the column named TABLE_NAME
                  var firstsheet = tableschema.Rows[0]["TABLE_NAME"].ToString();
  
                  string name_query = "SELECT F1 FROM [" + "SP$" + "] WHERE F1 &lt;&gt; ''";
                  OleDbDataAdapter da = new OleDbDataAdapter(name_query, con);
                  da.Fill(dataTable);
  
                  foreach (DataRow dataRow in dataTable.Rows)
                  {
                      foreach (var item in dataRow.ItemArray)
                      {
                          listWithElementsFromSPfirstColumn.Add((string)item);
                      }
                  }
  </code></pre>