Reading Excel file using C# and cleaning up the data

Question

This is the first time im doing this so please any guidance on best practices is welcome.

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.

After some googling, ive managed to get this code which is ok up to the bit where it fills the adapter.

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.

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.

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 => x.Field<string>("Store POS Order Ref") != string.Empty).Select(x => new Order
        {
            CPMOrderNo = x.Field<string>("Store POS Order Ref"),
            StoreName = x.Field<string>("Store Name"),
            Street = x.Field<string>("Street"),
            Town = x.Field<string>("Town"),
            County = x.Field<string>("County"),
            PostCode = x.Field<string>("Post Code"),
            POSOrder = x.Field<string>("POS Order: Order Name"),
            OrderItemCode = x.Field<string>("Order Item: POS Code"),
            Quantity = x.Field<int>("Quantity"),
            AuthInStoreBy = x.Field<string>("Authorised In Store By")

        });
    }
}

The order class is as follows:

    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; }

}

I'm not very sure about the LINQ statement syntax! Still learning.

This is the error showing when i step into the var query section in the debugger. How could i get this to work please?

I'm a C# n00b so could be well off - but it looks like you're assigning a type Field to a string - wouldn't you need to get the .value or similar from the field instead? or cast to a string like Field<string>("Street").value.ToString()? — SierraOscar
– SierraOscar, Commented May 23, 2016 at 14:54

poppertech · Accepted Answer · 2016-05-23 18:19:51Z

The invalid cast exception comes from the following line:

Quantity = x.Field<int>("Quantity"),

The following change fixed the problem:

(int)x.Field<double>("Quantity")

To arrive at my answer, I attempted to mimic your Excel file as shown in this image: ReplicatedExcelFile . I then modified your code as follows:

static void Main(string[] args)
{
    var fileName = @"C:/Users/Popper/Desktop/Stackoverflow/StackoverflowQuestion1.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 [Sheet1$]", connectionString);
    var ds = new DataSet();

    adapter.Fill(ds, "anyNameHere");

    var data = ds.Tables["anyNameHere"].AsEnumerable();

    List<Order> orderList = new List<Order>();
    Order pickedOrder;
    foreach (var x in data)
    {
        if (x.Field<string>("Store POS Order Ref") != string.Empty && x.Field<string>("Store POS Order Ref") != null)
        {
            pickedOrder = new Order();

            pickedOrder.CPMOrderNo = x.Field<string>("Store POS Order Ref");
            pickedOrder.StoreName = x.Field<string>("Store Name");
            pickedOrder.Street = x.Field<string>("Street");
            pickedOrder.Town = x.Field<string>("Town");
            pickedOrder.County = x.Field<string>("County");
            pickedOrder.PostCode = x.Field<string>("Post Code");
            pickedOrder.POSOrder = x.Field<string>("POS Order: Order Name");
            pickedOrder.OrderItemCode = x.Field<string>("Order Item: POS Code");
            pickedOrder.Quantity = (int)x.Field<double>("Quantity");
            pickedOrder.AuthInStoreBy = x.Field<string>("Authorised In Store By");

            orderList.Add(pickedOrder);
        }

    }
}

Thanks @bwyn ! that worked. How would i remove any special characters from the imported data? The spreadsheet has cells with tabs, linebreaks and extra spaces. And Would you know how to group the rows that had the same CPMOrderNo fields? Thanks

Collectives™ on Stack Overflow

Reading Excel file using C# and cleaning up the data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related