Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

Select From Model

Posted on 2013-05-20
13
Medium Priority
?
431 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
13 Comments
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 39183327
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
ID: 39183380
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
ID: 39183399
Hi FernandoSoto,

Would it be possible to put the results in another instance of Model1? Thanks!
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 39184350
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
ID: 39185240
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 64

Expert Comment

by:Fernando Soto
ID: 39185301
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
 
LVL 5

Author Comment

by:allanau20
ID: 39185457
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 64

Expert Comment

by:Fernando Soto
ID: 39185497
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
ID: 39185595
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 64

Expert Comment

by:Fernando Soto
ID: 39185648
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
ID: 39185690
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
ID: 39185691
awesome!
0
 
LVL 5

Author Comment

by:allanau20
ID: 39185721
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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

649 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