Data Scraping --> Write to Excel Range or CSV Question [SOLVED]

Sorry to bother you again but I ran into 2 issues that I’ve been trying to fix with this.

  1. The start from the next blank cell (when it fails) doesn’t appear to be working. If there is any data in any Q4 row, the process will instantly fail. It fails at the “Grab Only Items that haven’t been filled out previously” with the failure

Source: Grab only items that have not been filled out previously
Message: Cannot perform ‘=’ operation on System.Double and System.String.
Exception Type: System.Data.EvaluateException

  1. I ran into an issue where a fund ticker isn’t on Morningstar. I was trying to fix this by using find element and selecting the Not Found message on the website. However I’m having trouble figuring out how to skip the rest of the process and either leave the cells blank, add in 0s or “Not Found” and then go onto the next ticker.

Any help would be greatly appreciated

The first part is failing because the SELECT statement is expecting a string comparison, but the field type is double. Didn’t think about that when writing it, sorry!.

Easiest option is to just loop through the datatable after reading it from excel and change the datatype to string. In order to do that, I’d create a temp column of type string. Do a for each loop on the excel datatable you read. In the loop, assign activity row.item(“tempcolumn”) = row.item(“Q4”).ToString. Then outside the for each loop, delete the Q4 column and use an assign activity inputsTable.Columns(“temptable”).ColumnName = “Q4”

The second part can be solved with an if statement as well. Use Element Exists instead of find element. Then use an if activity with the output of the Element Exists activity in the Condition. Assuming element exists mean that the ticker is on morning star, transfer all of your activities into the true portion of the if statement. In the false portion, just put 4 assign activities to change the value for Q1-Q4 = 0.

Dont forget that Q4 is now of type string! If you want to make things easier, you can probably just convert Q1-Q4 all to string so they’re the same datatypes, easier to remember when you’re working with them

Thanks, I was able to get the 2) working.

I still don’t think I’m understanding 1) correctly.

What do you mean create a column called tempcolumn? Is this a column in the actual spreadsheet? I tried creating a string variable with this name.

Here’s what my process looks like for that part.

row.item(“tempcolumn”) is on the left side of for each and row.item(“Q4”).ToString is on the right


image

I attached the .xaml showing what I mean as well as a picture. Let me know if it’s still unclear

Note that I put did it looping over an array of strings so you can change more than one column to string if you’d like. Of course it’ll still work fine if that array only contains the 1 column Q4 - this just gives options :slight_smile:
Main.xaml (21.9 KB)

1 Like

This works great and really helps with my understanding, thanks!

There’s just one last question that I’ve been trying to solve after running this many times.

The last bug that I’ve found and think there is, is when the data table on the website is blank. Strangely when I just ran it to see what the exact error was, the data was now on the website. However I have a feeling this is going to still randomly occur sometimes.

I tried using element exists on the table since tables that were blank also had a line for 2019 while filled in tables only had 2018. This didn’t work as it always returned false.

I then tried using image exists but it always found it to be true…even when there was no 2019 on other tables.

Is there anyway to do an IFERROR when trying to grab the data table where if an error returns it just skips straight to adding 0s to the excel sheet? I know this is similar to the original question 2 but there doesn’t seem to be any way to identify it specifically.

This is the error message: There is no row at position 0.please start the process again to complete processing of the file

Ya my answer to question 2 was based on you saying you identified the element with “find element”.

If you know which activity/activites is failing, you can surround only that activity (or activities) with a try-catch. In the catch block, just assign the values to 0. Try-catch is very similar to the IFERROR construct within excel. It runs what you put in the try block, then on error it runs what you put in the catch block.

Awesome I’ll add that in, thank you!

I have another question that I’ve been trying to figure out for a while now regarding the Catch section of my process. I wanted the catch to add 0s to the line and continue onto the next ticker if there is an error.
I am able to do this without issue in different IF statements in the Try section. However the minute I add this add 0 sequence to the catch area, I receive tons of error.

The error says "Compiler Errors encountered processing expression. "transaction. Item “Q3”. ‘transaction’ is not declared or it may be inaccessible due to its protection level. This is the same for the Q1,Q2,Q4.

I’m really confused since every variable scope is set to the entire process. Unfortunately I need this to add the 0s and move on or the bot crashes every 30 min due to certain website errors that element exists can’t pick up. Do you have any ideas on how to fix this/why this happens?

Hi @ds56

Is it possible that the transaction variable comes from a For Each loop and you took the sequence outside of that loop?

2 Likes

Thanks, this is right. All fixed

1 Like

So this fixed the errors it was giving me but it looks like the catch gets stuck in a loop due to the For each activity.

Is it possible to specify write 0s for this transaction only and not do any loops?

Workflow can never get stack on the for each loop because there is a finite amount of elements to loop through.

Could you include some logs inside the loop to see what is happening?

Also, if you still have a Click activity in the loop, maybe it is continuously trying to Click an element with each iteration of the loop? Just guessing here, because it should throw an exception when timed out (so is most likely not the case).