title: Reading Excel file using C# and cleaning up the data
body: <p>This is the first time im doing this so please any guidance on best practices is welcome.</p>
  
  <p>Im trying to get data from an excel file, and need to work with a few columns from the data i get. I need to ensure no linebreaks and other special characters exist in the cells and that certain columns are not blank.</p>
  
  <p>After some googling, ive managed to get this code which is ok up to the bit where it fills the adapter.</p>
  
  <p>In the LINQ section im trying to filter out just the columns i need before i process the data further, but this is where im stuck.</p>
  
  <p>For each row im trying to create a new order. I have a class called Order that just has properties for now that im trying to populate.</p>
  
  <pre><code>class Program
  {
      static void Main(string[] args)
      {
          var fileName = @"C:\Users\metesting\Bulkuploader\POS ORDERS2016.xlsx";
          var connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";";
  
          var adapter = new OleDbDataAdapter("SELECT * FROM [report$]", connectionString);
          var ds = new DataSet();
  
          adapter.Fill(ds, "anyNameHere");
  
          var data = ds.Tables["anyNameHere"].AsEnumerable();
  
          var query = data.Where(x =&gt; x.Field&lt;string&gt;("Store POS Order Ref") != string.Empty).Select(x =&gt; new Order
          {
              CPMOrderNo = x.Field&lt;string&gt;("Store POS Order Ref"),
              StoreName = x.Field&lt;string&gt;("Store Name"),
              Street = x.Field&lt;string&gt;("Street"),
              Town = x.Field&lt;string&gt;("Town"),
              County = x.Field&lt;string&gt;("County"),
              PostCode = x.Field&lt;string&gt;("Post Code"),
              POSOrder = x.Field&lt;string&gt;("POS Order: Order Name"),
              OrderItemCode = x.Field&lt;string&gt;("Order Item: POS Code"),
              Quantity = x.Field&lt;int&gt;("Quantity"),
              AuthInStoreBy = x.Field&lt;string&gt;("Authorised In Store By")
  
          });
      }
  }
  </code></pre>
  
  <p>The order class is as follows:</p>
  
  <pre><code>    public class Order
  {
      public string CPMOrderNo { get; set; }
      public string StoreName { get; set; }
      public string Street { get; set; }
      public string Town { get; set; }
      public string County { get; set; }
      public string PostCode { get; set; }
      public string POSOrder { get; set; }
      public string OrderItemCode { get; set; }
      public int Quantity { get; set; }
      public string AuthInStoreBy { get; set; }
  
  }
  </code></pre>
  
  <p>I'm not very sure about the LINQ statement syntax! Still learning.</p>
  
  <p>This is the error showing when i step into the var query section in the debugger. How could i get this to work please?</p>
  
  <p><a href="https://i.stack.imgur.com/sPYgu.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/sPYgu.jpg" alt="enter image description here"></a></p>