UiPath is not keeping date formatting when transferring data between two workbooks

Hi,

I currently have the following to read data from one worksheet:

image

And then an assign as follows

dtClean = dtAll.AsEnumerable.Where(Function(row) row(“Term Reason”).ToString.Contains(“Vol”)).CopyToDataTable

The purpose is to extract those rows, and write them into another file. It is doing this, however the date formats are being changed and as we work US and UK this is a huge issue as it is reading 11/01 as 01/11 - but not all the time, every line seems to make up its own mind which way round it goes, roughly a 50/50 split.

Is there a reason the dates are being changed, as in the original file they’re all US style dd/mm, and in the final file they’re also currently all dd/mm, they’re just meant to be added underneath.

Any help would be appreciated, thanks!

Alex

1 Like

If it’s possible to create a filter for the data then filter the required column, then read and include UseFilter then that could work, but I’m unable to filter the column despite being able to create a filter on the header row.
Thanks

1 Like

Hi.

So my understanding is that you are storing dates as “MM/dd” but it’s coming in as “dd/MM”.
One thing to know is that Excel can have different formatting, like you could format it as “dd/MM” in Excel but the actual value as a datetime will be “MM/dd”. So, when reading it in .net as a datetime type it will show as “MM/dd” even though Excel has it formatted differently.

So, first thing is I would suggest checking the column to make sure it’s not formatted specifically to “dd/MM” and confusing you when actual date is in “MM/dd”.

Second thing is sometimes date is impacted by your Windows region format. You can check this by going to Region and Language in Control Panel.
image

If you provide sample files where this issue is reproduced, we can probably identify this easier. I was also a little confused of what the issue was.

As for your filter question… it looks like you did it right using .Where(). If you say why or what error you got while trying to filter, we can identify the problem better. I’m not sure this will impact how your dates are read as though.

Regards.

1 Like

I’ve figured what’s causing the split, essentially if one value is over 12 it will assume DD, otherwise it will switch dd/mm to mm/dd. I’m based in the UK, working of files produced by a US system, so when this bot is run it will only be in the US - so this could be ok - but I’d rather make it more robust for sure.

I have double checked the formatting in excel for both files and they’re the same, so I can only assume it’s being done by .net when reading my Windows region as you suggest. This is an admin setting for me, so don’t think I change it unfortunately though.

To clarify my filtering issue, the assign works just fine - what I meant was sending hotkeys to enable a filter in row 1 but I was unable (don’t know how and couldn’t find it anywhere (maybe I’m blind?)) to actually get the drop down up to try and select just the rows I want.

Thanks as always for your help!

2 Likes

How are you using the dates? Like for example, if you do CDate(row(“Date”)) that could return different than DateTime.ParseExact(row(“Date”), “MM/dd”, System.Globalization.CultureInfo.InvariantCulture) — so, maybe CDate() or Convert.ToDate() is converting it incorrectly. But, if you are simply just Reading the data and Writing it back, then I am unsure as to why it’s doing this.

Also, I don’t get why your initial data would have the dates flipped, because regardless of region it should keep the date values as they should be unless the dates in the initial data has wrong (backwards) dates… I believe Excel will automatically try to convert the date to your Region’s format. I might test this if I have time and let you know if I notice anything weird.

I would normally suggest using .net with the entire datatable if your plan is to process specific dataset — you can also use .ToArray to go through only certain rows while maintaining entire dataset.

Additionally, you can use a .vbs file and Invoke VBA to call the function, but I don’t have a script handy to give you for that option.

However, if you would like to access the Filter feature in Excel using UiPath, this will require that you use “Clear All” or “Clear Formats” beforehand, because the Filter is a Toggle. Then, Select first row and turn on Filter. Then, you need to Select the cell with the arrow and do Alt+Down, followed by the string of Alt keystrokes to enter the menu options of that cell for filtering.

Select "1:1"
"[k(lalt)]hef" // Clear Formats
"[k(lalt)]at" // Toggle Filter
Select cellRange
"[d(lalt)][k(down)][u(lalt)]fe" // Enter Filter Menu, then select Filters and Equals

Hope that helps.

Regards.

1 Like

So I’ve just the run the process unchanged in my VM which is sat in the US - all dates work 100% accurately, using the same files (both VM and myself accessing a shared drive) so I can only assume my machine was trying to enforce its date convention on the excel files? To your point, I was simply reading data, and writing it, nothing I did involved the dates other than the obvious copying and pasting of them.

I think as it works for the bot I will be okay for now, but I will definitely monitor this going forward to make sure our developments are more robust, and will look into the VBA.

Thanks for the command lines too, it was the final one which had me stumped, I’d got up to there but didn’t know left alt and down produced the menu I was after, thanks!

2 Likes