Cannot figure out how to do a for each with a date converted to a string

Hello,

So here is my situation. I need to collect data from a spreadsheet. This spreadsheet of data has basically 2 columns that I am interested in. One column has the work day number (1-28) and the second column has the relevant value for that day.

For other parts of the operation the robot already knows the relevant work days (example. first complete week of february would be between 2/4 and 2/8) The robot already knows this range and actually that range is decided by the processor ahead of time. Since february has that first day on friday, the processor may choose 2/1 - 2/8 and the robot will know this is the “first week”.

Now I have an activity to basically break down the date value and just pull out the day value, i.e. 01 and 08. Now I want to run a for each on the datatable from the data spreadsheet and basically I want it under the condition if row.item(0) >= startofweekday AND row.item(0) <= endofweekday then get row item of the value (row.item(1)), write into cell. I have all of the if statement done, i think. but I cannot get past this initial condition. Since it was originally a date I tried changing the format of the startofweekday and endofweekday to strings, doesn’t work. I tried converting to int32 but i run into an error doing that.

This was just my idea on how to solve this problem. if there is an easier way to compare these two numbers than someone please help!

1 Like

You could also use Convert.Double(row.Item(0)) to convert it into a number to be used in the comparison statement you have. Just throwing some ideas out there. Hope it’s helping.

1 Like

Update: So it would appear that I thought it would originally be 0 indexed but after debugging I was getting a DBNull error. So I changed things to (1) and so it looks like we’re getting some progress. Now I just keep getting an issue of input is not in a correct format! I tried changing my convert int to a convert double, but that didn’t seem to help

convert.ToInt32(row.Item(1)) >= StartofWeekDay AND convert.ToInt32(row.Item(1)) <= EndofWeekDay

is basically the if condition I was working with.

I tried swapping the convert to a double, i tried removing it entirely (results in validation errors). I tried removing the string conversion, I tried converting the weekday to an integer (validation errors).

I think I NEED the AND if I don’t want to have to have to nest a second if inside this activity. Basically I need to select it if it is between these numbers, so I can’t use an OR i don’t think.

1 Like

What kind of data variable is being stored in row.Item(1)? Does it have a string in there? Date? What data type is it.
Also I would double check and make sure you can retrieve the data to see what it has in there, before the comparison.

I double checked the AND and it looks like it’s working the way It should. I’ve had problems with it in the past Acting like an OR. So strange…

2 Likes

So the data is probably just a number value. I am not totally sure. It is reading from an excel spreadsheet which held numbers. My start / end date variables were dates at one point. It was basically 02/01/2019, but I removed that data to only have the days. I then converted that to a string but I think I need to convert the date to something else. I tried doing the convert to int and double but it didn’t seem to work. Going to bang my head on the wall a little more today to see if I can see why it is not working.

Basically the goal here is using the date as “dd” and comparing it to my number range. So I probably need to convert that date to something that can be compared to numbers. then basically doing the logic i shared earlier and basically if >= start AND <= end then get row item (2) which is the dollar value for that corresponding day. I will then use that data to paste to another spreadsheet. Basically this robot is consolidating data from multiple sources. I am ALMOST done but this is the very last step and it is just not working!

1 Like

You may want to try to change the date you are scraping into a DateTime Object. There are lots of different ways to instantiate them. Depends on what you’re working with. Then you can more easily compare the dates.

Here there a lots of examples of how they are using it. Let me know if you have any questions.

1 Like

Well just to Update.

SO i finally got past the step. I suppose maybe the convert to int // double had originally worked. But, the first value of my DT had a null value. I thought that the UIPath would have easily worked through this and just skipped this row. I took your suggestion of using a message box to see what was being analyzed and realized it was getting stuck at the blank cell. I thought it was getting stuck at my if condition. So I put everything into another if and basically just did an if row.item(0) = “” then don’t do anything else do the actual operation. This allowed me to skip that first null value and then the rest of the logic worked.

1 Like