Reading Excel file using C# and cleaning up the data
<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>