Solved

Select From Model

Posted on 2013-05-20
13
416 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
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
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 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 62

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 62

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 62

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

920 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