Sort the data by latest date

[Amol_Golhar]

Dear Team

I have a excel file wich contain data from first date of the year to the current date,
I would like to sort it from Current date to oldest date, Adding a excel file.
Autoreport Status 2024.xlsx (11.2 KB)

Thanks,
Amol Golhar

[Parvathy (PS Parvathy)]

Hi @Amol_Golhar

Please specify which column to sort.

Regards

[Parvathy (PS Parvathy)]

Hi @Amol_Golhar

Check out the below zip file. I have sorted based on EmailTime VRL column. Hope this meets your requirement.

sortedDt = (From row In dt_Input.AsEnumerable()
            Order By DateTime.Parse(row("EmailTime VRL").ToString()) Descending
            Select row).CopyToDataTable()

sortedDt is of DataType System.Data.DataTable

Excel Forum.zip (164.4 KB)

Hope it helps!!

[pikorpa (Piotr Kołakowski)]

Hey @Amol_Golhar
Depends on which column you want to sort, you can use this:

sortedDt = dt.AsEnumerable().OrderByDescending(Function(row) DateTime.ParseExact(row("YourDateColumnName").ToString, "YourDateFormat", System.Globalization.CultureInfo.InvariantCulture)).CopyToDataTable()

eg.
sortedDt = dt.AsEnumerable().OrderByDescending(Function(row) DateTime.ParseExact(row("EmailTime VRL").ToString, "dd-MMM-yyyy HH:mm", System.Globalization.CultureInfo.InvariantCulture)).CopyToDataTable()

[Amol_Golhar]

Dear Parvathy,

I tried the same work flow on my machine, not working, Is it working on your machine.
I have checked attched excel file by you, it is also not sorted.
FYI, Requirement is we want latest data on top from 10-Feb-2024 to 01-Jan-2024.
Please confirm.

Thanks,
Amol Golhar

[Parvathy (PS Parvathy)]

Hi @Amol_Golhar

FYI, Output sheet is the expected output. Please check that. Please do mention which is the column you want to sort.

Regards

[ppr (Peter Preuss)]

@Amol_Golhar

I tried the same work flow on my machine, not working

Date Formats visually offered in Excel are not mandatory the same within the resulting DataTable, when we read in the data e.g. with read range.

In the below firstAid a few analyis steps are described

[FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues

This FirstAid Tutorial will describe how DateTime parsing issues can be analyzed and handled. Click spoiler text when in a hurry: Example Assignment Following Excel data will be processed and the corresponding Quarter is to calculate for the particular task date Input data: [input_raw] Quarter Calculation Quarter = CInt(Math.Ceiling(YourDateTimeVar.Month / 3 )) Initial Implementation [initial_flow_raw] tmpTaskDate = DateTime.ParseExact(row("Date").toString.Trim, "dd.MM.yyyy", CultureInf…

Once these details are cleared, then maybe a few modifications are done on the above provided suggestions (e.g. format definition, null / empty value handling).

But in general we would sort the data with a LINQ the Order Clause or the ORderByDecending Operator

[HowTo] LINQ (VB.Net) Learning Catalogue

This topic references the different Learning HowTos related to LINQ. It will be updated on new published HowTos as well LINQ Learning: Topic LINK First Start with LINQ [HowTo] - First Start with LINQ (VB.Net) Exploring the LINQ universe [HowTo] - Exploring the LINQ Universe (VB.Net) Operator [HowTo] Overview Topic LINK Where [HowTo] LINQ - First Start # Where Operator Select work in progress Any [HowTo] LINQ – First Start # Any Operator All [HowTo] LINQ – First Start…

[Anil_G (Anil Gorthi)]

@Amol_Golhar

This should do the job without any reading and writing

cheers