Datatables by date

Help
,
1

I have a big datatable from Excel that i want to sort into smaler datatables by month. but the date are all in format “dd/MM/yy”, how do i take this big datatable and put in in different tables by month when the colum date is “dd/MM/yy” ?

2

Hi @marius_johansen,

using this code we can sort .
(From p In dtDummy Order By DateTime.ParseExact(p.Item("datestring").ToString, "dd/MM/yy", System.Globalization.CultureInfo.InvariantCulture) Ascending Select p).CopyToDataTable

Refer this xaml file for sorting the date string from datatable.
sortDataTable.xaml (9.5 KB)

Regards,
Arivu

2 Likes
3

thanks, but how do i do this when i want to put data from January in a datatable or a Excel file for januar ?

4

Hi @marius_johansen,
Do you want to split each month in a different datatable from the main datatable???

Year wise also???

5

yes, that that is correct!
can you help me ?

6

Hi @marius_johansen,

ya sure.
Refer the below xaml file to how to get the each month data splitup.
sortDataTable.xaml (16.4 KB)

Regards,
Arivu

7

hi @arivu96
almost, i can’t post example program because i’m New to the forum, so i have to try to explain better.
i have example 100rows and 20 coulums in my DT.
the 100 rows is for one hole year.
i want to split these 100 rows into 12 DT’s by wich month it is and then put it back in 12 different Excel files.
one file each month With all the 20 coulums.

8

Hi @marius_johansen,

Yes you can get the 12 different datatable based on your input, only one thing you need to do you need write it into the excel file using write range activity.

Regards,
Arivu

9

Hi @arivu96
Like the same thing . i need to sort the table based on dates . I am reading data from excel which contains the date and am using your for sorting but unfortunately am getting error stating that “string is not a valid date” . I am not sure whether my approach is right, can u help me out with this problem?
sample.xaml (7.0 KB) test.xlsx (8.6 KB)
After sorting the dates in descending my output should get the nearest date (ie) 20/03/2018

ss
ss894×431 81.1 KB

10

HI @Uthraa,

Try below code.

(From p In dataTable Order By DateTime.ParseExact(p.Item("Date").ToString.SubString(0,10), "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture) Descending Select p).CopyToDataTable

Regards,
Arivu

1 Like
11

@arivu96 Thanks for the reply. The code works well. but i need get the future most date (ie). 20/03/2018
but this codes sorts in the following order

ff
ff896×434 72.8 KB

12

sample.xaml (10.4 KB)

Regards,
Arivu

13

Thanks @arivu96 its working fine ! but can you clarify me why have u added that subString(0,10)?
Thanks :slight_smile:

14

dd/MM/YYYY has a total length 10 so starting from 0 taking 10 characters. Hence subString(0,10)

1 Like