hi guys i’m trying to read an excel file and sort it base on the date in a column. i initialized the datatable, it gets error when i set the column type as datetime, maybe i should just set it to string ? how do i achieve what i want to do ?
@RobertoEwaldo Say for example your output data table is Dtoutput, to filter column date date after read file take an assign activity, in expression place keep as Dtoutput. Select(‘’ date = ‘place the date you want to filter’). Copy to data table. Thus expression will filter it with date. Hope this helps, plz let me know if any thing goes wrong
Follow these linK
hi @balu should i initialize the datatable ? if i should, what would be the column’s type ?
Yes first read excel data into datatable then in assign activity
datatable = (From row In filteredSchemaDT Order By DateTime.ParseExact(row.Item(“RECEIPTDATE”).ToString, “dd/MM/yyyy”,
System.Globalization.CultureInfo.InvariantCulture) Descending Select row).CopyToDataTable
column type can be string
may i know a sample date value from that column, actually based on that the datetime format has to be modified…it its like
02/12/2019…it must be dd/MM/yyyy
03/25/2019…it must be MM/dd/yyyy
like datetime format must match exactly with the string format …kindly change the dateformat accordingly in your xaml…else you were going good
Hope this would help you
Main (1).xaml (41.9 KB)
You have given column name as “Database Date” which should be “Database date”
please run and confirm it if works .
still doesn’t work bro, same error string doesnt recognized as datetime
Fine may i know at which activity the error was thrown…,may be a screenshot
this is the date from the excel, i already set the format to dd MMMM yyyy hh:mm:ss but still got the same error
Aah it should be like this @RobertoEwaldo
DateTime.ParseExact(Yourstringvariable,“dd MMMM yyyy HH:mm:ss”.System.Globalization.CultureInfo.InvariantCulture)
This is the difference
DateTime.ParseExact(Yourstringvariable,“dd MMMM yyyy hh:mm:ss”.System.Globalization.CultureInfo.InvariantCulture)
your datatable column type is date time can you change it to string and try.
changed it to
(From row In dtFilter Order By DateTime.ParseExact(row.Item(“Database date”).ToString, “dd MMMM yyyy HH:mm:ss”,System.Globalization.CultureInfo.InvariantCulture) Descending Select row).CopyToDataTable
still got the same error
it’s already a string
i changed it to string since i got error like this for using datetime type
A suggest you to have a look to the Microsoft documentation about datetime format here :
So you’ll can find the format that fit to your dateTime
Try to write to console the value of DateTime.Now.ToSting(“dd MMMM yyyy HH:mm:ss”) does it produce the same output as : 06 Mei blablabla ?
it should Be like this @RobertoEwaldo
DateTime.ParseExact(Yourstringvariable,“dd MMMM yyyy H:mm:ss ”.System.Globalization.CultureInfo.InvariantCulture)
And use single H instead of double HH
This would work
For more reference look at this url )
Hope these works
datatable = (From row In Datatable Order By Convert.todatetime(row.Item(“date”).tostring).tostring(“yyyymmdd”) Select row).CopyToDataTable
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.