It is fairly simple but excel date sorting not working as expected, so it made me to seek help from you experts.
There are 3 web pages with tabular data having a due date column in DD/MM/YYYY 12:00:00 AM format (time part is always fixed), now bot to extract all 3 web pages data and create separate sheets in one excel, merge all 3 sheets into one sheet and sort the data on due date ascending order, the issue is when bot extract the data and create an excel sheet, this date value is not consistent, for few items it removes time and for few other time value stays.
I think it is excel issue as bot extract data correctly, is there any way I can keep this date value consistent throughout the process?
I hope i made the issue clear, please do let me know if you need additional details, besides following screenshots are to support
Due date on webpage 1
Now when above data is copied in excel
Same thins is happening with other webpages data too and as a result final sorted data is not in accurate order.
Any input/thoughts will be helpful.
Thank you @Karuna for your response, appreciate, i tried this before but didnt work, tried it again but unfortunately didnt work too. problem is while creating data in excel, date time converts to MM/DD/YYYY. please refer following-
Data extracted from webpage
Data saved in excel
Any other thoughts please?
check below for your reference
also for difference
Hope this may help you
You need to set the Date and Time format in excel
Date Column – Right Click – Format Cell and refer the screen shot
Then try with attached sample workflow it will work.
Thank you @nameless, using discrete write range did the trick.
Never imagined different behavior of same activity when used standalone and inside excel scope.
I managed to solve this.
The issue was with excel, date and time in excel is quite funny to work with. As I mentioned in problem statement, date values extracted from webpage are not consistent, for some of it comes with dd/MM/yyyy and for other MM/dd/yyyy, for some items time is truncated.
So the challenge was to keep the data consistent. Please let me know if someone need to know the solution.
Hello, i have exactly the same issue. Can you explain how did you do to resolve it ?
thank you in advance
@DjamBen, I assume you have data in multiple excel worksheets and you want to merge all the sheets data into one final sheet, please follow below steps
Extract data from source, let’s say web page, add this data in excel using Write Range activity (dont add write range inside excel scope)
Use Excel Application Scope and read all sheets created from step 1 by using Get Workbook Sheets activity, this would give you output of type list, this means you have all your worksheets into this list variable.
Read all sheets data using for-each loop and use Read Range activity and use Merge Data Table to have all sheets data into a datatable
Here you would get data in the data table obtained from step 3, let’s say dt_outputDataTable.
Now format DateTime value of above data table to read only date value, use for each to read each and every row. Remember, date time is string here and you need to convert it into date, refer following screenshot-
Use Sort Data Table activity to sort data table obtained from step 3, this will have your data sorted on date column, and finally use Write Range activity to create final sheet.
Properties ofSort Data Table
Hope this helps you. Let me know how it goes.
thank you @ramvashista85 for explaining in detail, it works perfectly.
I also found another solution, which is to execute a VBA macro (Invoke VBA) which will set the data column format, and sort the data after that. herewith the vba lines>>
Selection.TextToColumns Destination:=Range("A:A"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
Range("A:F").Sort Key1:=[A1], Order1:=xlDescending, _
Glad it worked @DjamBen