LinqToExcel: How do I exclude certain rows?

Question

I've been struggling with this for a few days now and I'm stumped. I'm hoping that someone can provide an alternate suggestion. Basically, I'm reading data from excel using LinqToExcel. But I want to exclude all rows with a "Rating" of "NR". Here's a sample of my data:

CompanyName Rating  SalesMan
Apple       2       Steve
Google      NR      Steve
Microsoft   3       John
Dell        1       Steve
Pepsi       3       John

I just want to find all companies that belong to Steve but doesn't have a rating of "NR". My final list should be:

CompanyName SalesMan
Apple       Steve
Dell        Steve

I've tried the following code but it doesn't work:

1)

var masterList = masterDataXL.Worksheet("data_all").Where(d => !d["Rating"].Equals("NR"));

2)

var masterList = masterDataXL.Worksheet("data_all")
    .Where(m =>
        !m["Rating"].Equals("NR")
        &&
        m["SalesMan"].ToString().Contains(resAnLastName)) // check for last name
    .Select(m => new ResAnTicksDataClass
    {
        Company = m["CompanyName"],
        Rating = m["Rating"],
        Seller = m["SalesMan"]
    }).AsEnumerable();

3) Created a property for Rating and did the following:

var masterList = masterDataXL.Worksheet("data_all")
.Where(m =>
    m["Analyst"].ToString().Contains(resAnLastName)) // check for last name
.Select(m => new ResAnTicksDataClass
{
    Company = m["CompanyName"],
    Rating = m["Rating"],
    Seller = m["SalesMan"]
}).AsEnumerable();
var dataList = (from m in masterList
where m.Rating != "NR"
select new ResAnTicksDataClass
{
    ResAnName = m.ResAnName,
    DescrTick = m.DescrTick
}).AsEnumerable();

I'm open to any other suggestions that you might have because I'm completely stumped. Thank you so much in advance.

Answer 1

I suggest you select the 'Rating' column in your Excel file and do a search & replace on the selection (CHange 'NR' to '0') and then filter. Should help using a single data type.

As phoog said, converting Excel files into a table, that table will need to specify each column's type. To do so, it'll look only the 10 first rows of your Excel file. So if your file doesn't have a 'NR' value in the first 10 rows, it will set the column type to INT, and therefore fail to convert the value 'NR'. A simple trick to fix this is to add a row to your Excel file, just before your first data row, with the data using the datatype you want to use.

As an example, if a column is using text values and sometimes the text is using over 255 caracters, make sure the first 10 rows have at least 1 text value using 256 caracters. Else, once it creates the table, the column will be set to VARCHAR(255) instead of VARCHAR(MAX) and then crash while converting texts longer than 255 caracters.

Conclusion: always make sure the first 10 rows are using the right type and size to fit all the rows of your Excel file!