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.

What does it do instead of working? Is there an exception or a compiler error? If so, what's the message? Does it give incorrect results? If so, what are the incorrect results? — phoog, Sep 28 '12 at 17:01

Francis P · Accepted Answer · 2012-09-30T06:27:08.940

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!

I wasn't aware the datatype of the column was based on the 1st 10 rows. Thx @Francis for tip. I've changed my program accordingly. — inquisitive_one, Oct 18 '12 at 14:58
No prob... I've struggled so bad on this in the past, glad I could help! — Francis P, Oct 18 '12 at 15:13

score 0 · Answer 2 · answered Sep 28 '12 at 14:49

0

In you first sample you should change this:

d => !d["Rating"].Equals("NR")

to this:

d => d["Rating"] != "NR"

It could also be written in a cleaner way:

var masterList = 
    from d in masterDataXL.Worksheet("data_all")
    where d["Rating"] != "NR"
    select d;

answered Sep 28 '12 at 14:49

Wasp

3,395
19
37

Thx Wasp. I tried that and I get the error: Data type mismatch in criteria expression. I even tried: d => d["Rating"].ToString() != "NR". I get the same error. – inquisitive_one Sep 28 '12 at 15:21
That's weird, I tested that code in Linqpad replicating an Excel file with the same layout as yours, and it works fine. I downloaded the binaries of LinqToExcel [here](http://code.google.com/p/linqtoexcel/downloads/detail?name=LinqToExcel_1.6.3.zip), is it the version same you are using? – Wasp Sep 28 '12 at 15:24
Yup. I'm using the same version. But i'm still getting an error. Can I send you the excel file for a real test? – inquisitive_one Sep 28 '12 at 15:28
One more thing, I actually need to work option 2 not 1. so, i'm using your suggestion on that option. – inquisitive_one Sep 28 '12 at 15:37
Will not be able to have further look in the next few days, I hope you'll solve it anyway :) – Wasp Sep 28 '12 at 16:24
@inquisitive_one if the first occurrence of "NR" is below the 10th row (or maybe the limit is some number other than 10, I don't remember exactly), the driver is probably inferring a numeric data type for the column. This would explain the data type mismatch error. I don't remember all the details, but there's probably a way to force it to treat the column as text. Or, you can hack the system by inserting a dummy row with a dummy text value, and then filtering that row out of all of your queries. – phoog Sep 28 '12 at 17:01

LinqToExcel: How do I exclude certain rows?

2 Answers2