Solved

Select From Model

Posted on 2013-05-20
13
414 Views
Last Modified: 2013-05-21
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
0
Comment
Question by:allanau20
  • 8
  • 5
13 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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?
0
 
LVL 5

Author Comment

by:allanau20
Comment Utility
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!!!
0
 
LVL 5

Author Comment

by:allanau20
Comment Utility
Hi FernandoSoto,

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

Accepted Solution

by:
Fernando Soto earned 500 total points
Comment Utility
Hi allanau20;

To set the STARTDATE and ENDDATE to a date format in your Excel sheet open the document in EXCEL. Place your cursor on the column marked B and click on it, it will select the complete column. In the tool bar section depending on your version of Excel you will find a ComboBox called Number Format, click on its down arrow to get to the options as shown in the imaage below and select Short Date. Then do the same to column C for ENDDATE. You will need to correct the values in the cells to be in the format of yyyy/mm/dd.

Setting Date Format in Excel

Using the following code it should give you what you need. Please note that there are two queries because Linq to Excel does NOT support all Linq methods and therefore has to be done in Linq to Object way, outside of Linq to Excel.

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

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

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

var prods = (from p in per
             group p by p.PRODUCT into pGroup
             from pg in pGroup
             group pg by pg.PRODUCT into pgGroup
             select pgGroup.OrderBy( d => d.STARTDATE)
                           .ThenByDescending(d => d.ENDDATE)
                           .FirstOrDefault())
                           .ToList();

public class Model1
{
    public string PRODUCT { get; set; }
    public DateTime STARTDATE { get; set; }
    public DateTime ENDDATE { get; set; }
}

Open in new window


Code Results
0
 
LVL 5

Author Comment

by:allanau20
Comment Utility
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!
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Author Comment

by:allanau20
Comment Utility
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!
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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.
0
 
LVL 5

Author Comment

by:allanau20
Comment Utility
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!!
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
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.
0
 
LVL 5

Author Comment

by:allanau20
Comment Utility
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.
0
 
LVL 5

Author Closing Comment

by:allanau20
Comment Utility
awesome!
0
 
LVL 5

Author Comment

by:allanau20
Comment Utility
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!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now