asked on

Select From Model

Hi Experts!

Thanks for reading this..

So, using LinqToExcel to pull data into a Model:

string sFileName = @"C:\Data\Product.xlsx";

var excel = new ExcelQueryFactory(sFileName);
excel.DatabaseEngine = LinqToExcel.Domain.DatabaseEngine.Jet;

var per = from x in excel.Worksheet<Model1>("Product")
            select x;

Open in new window

The content of Product looks like this:

PRODUCT     STARTDATE   ENDDATE
0002-0800   19990201    19991201
0002-0800   20010201   
0002-0800   20020201   
0002-0800   20040201    20040201
0002-0800   20060201    20060201
0002-0800   19970201   
0002-0800   19890201    20340201
0002-0800   19920201    20010201
0002-1407   19510301
0002-1407   19510301    20151231
0002-1975   20101201
0002-3004   20010316
0002-3227   20030110    20051123

Open in new window

What I want to do is pull unique product and if there are dups then get the earliest start date and the latest end date.
Blank in the end date column means indefinite.

So, it would look like this:

PRODUCT     STARTDATE   ENDDATE
0002-0800   19890201
0002-1407   19510301
0002-1975   20101201
0002-3004   20010316
0002-3227   20030110    20051123

Open in new window

TIA!
Product.xlsx

Fernando Soto

Hi allanau20;

Please post you class Model1. Also you have this record, 0002-0800 19890201 20340201, in the Excel work sheet which you have in the output as, 0002-0800 19890201, with no ENDDATE.

The dates in the Excel sheet have a data format as general, can this be changed to Date format such as 02/01/1989?

Allan

ASKER

Hi FernandoSoto,

My Model1 class is:

    public class Model1
    {
        public string Product { get; set; }
        public string STARTDATE { get; set; }
        public string ENDDATE { get; set; }
    }

Open in new window

Thank you for being so through; it's a typo..:
0002-0800 19890201 20340201
So, let's go with it has an end date of 20340201

And yes the Date format can changed from General, but I'll need your kind assistance in showing me how this can be done..

Again, thank you so much!!!

Allan

ASKER

Hi FernandoSoto,

Would it be possible to put the results in another instance of Model1? Thanks!

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Allan

ASKER

Thank you so much FernandoSoto.

Instead of manually changing the Date Format through excel; I was hoping you could show how to change the the Date Format after we have pulled it into per (Model1)? The reason is so that this process can be automated.

In the meantime I will test out what you have.

Thank you again; really appreciate it!

Fernando Soto

Hi allanau20;

OK then, leave the Excel spread sheet as is without modifying it. Make the Model1 class as you had originally had it, with STARTDATE and ENDDATE as strings. Then use the same code as my last post, that should give you the same results.

Allan

ASKER

Thanks FernandoSoto.

Tried the code above and it returned:

Product = NULL
StartDate = 19510301
EndDate = 20151231

Perhaps we should Cast the Start and End dates to DateTime?
How does it know that a blank date is indefinite in the End Date? Should we put in a far off date such as 20781231? That would be a safe bet.

Thanks again for your help!

Fernando Soto

Hi allanau20;

According to your last post it was not a date that was null but the product field. If you are running the same Product.xlsx file as you posted in your question then you should have gotten the same results as I did. Please look at the Excel file to make sure that all PRODUCT have a value.

Allan

ASKER

Hi FernandoSoto.

My bad; you're right. I had "Product" in Model1 instead of "PRODUCT'; that explains why I had NULL.

We're very close. The Start Date is not blank, however some End Dates do have blanks. And when we see a blank we can assume it's indefinite, so we can assume a date as 20781231.
So, for product = 0002-0800
it should have returned the Start Date = 19970201 Instead of Start Date = 19890201

Can we please do this?

Again, thank you so much for your help!!

Fernando Soto

Am I misunderstanding the stated requirement? In your original question you stated the following, "What I want to do is pull unique product and if there are dups then get the earliest start date and the latest end date.", by this requirement the code selected the correct value.

Allan

ASKER

HI FernandoSoto.

That is correct; and I said "Blank in the end date column means indefinite".. my bad; I should have clarified.

Let me award this question and I shall open another to put in a date value if the End Date is blank.

Thank you again for your help.

Allan

ASKER

awesome!

Allan

ASKER

Hi FernandoSoto,

If you have some time today, can you kindly please look at this question about the updating a default value to the blank End Date? Thanks!