Excel Automation with StudioX - Books Sales Report

Excel is a widely used Microsoft software. For example, it is used for billing, data management, analysis, inventory, finance, business tasks, complex calculations, just to name a few as the list can surely go on.

Are you eager to get rid of those strenuous and time-consuming operations involving data manipulation in Excel? What tasks will you automate and in what situations will data extraction become handy?

For any technical question , please create a new topic in the StudioX category here .

Problem here getting Excel issue or format problem

From the start this is the exercise where I had the most difficulty. First step of data recovery ?? then formula problem, in France the decimal separator is the comma and in other countries it is the point. I understood that the decimal separator in UiPath is the point. After other worries, I treated only the first 100 pounds, suddenly the formula for calculating the total of the solution does not work. We must add an IF before to see if we have recovered the price. Now everything works, but not easy.

On the other hand, I have problems with Excel formulas which are not interpreted in Excel, I have to do F2 then Enter for them to be taken into account. Any idea on this problem ??

Jean-Marc

When I am extracting the data, only 100 lines are generated - does anyone know why? I tried a few times but it stops at 100 books. Thanks!

1 Like

Thanks! I figured it out eventually :slight_smile:

data extraction error. How can I solve this error

?

Seems like you need to install the Chrome Extension to let UiPath navigate Google Chrome.

You should be able to do that by going to Home -> Tools.

If that doesn’t fix it try checking the solution here:

I encountered several entries in the solution file that had an error when StudioX prompted the vlookup function since those books where not in the Data Extraction list that came with the file.

Or you can change it afterwards in the properties panel

Is there a way to do selective click on websites? For the booksales project, we need only 4-5 categories but the website has lot more categories on the left side of home page. Is there a way to configure robot so it first clicks the required category and filters the books before proceeding to extract / scrape book titles and prices from website?

In this exercise, there were a few items left out in RoboPath doc and on the web. First, VLookUp doesn’t work if the Extraction Data is not in ascending order. Second, the extracted price values needed to be modified in order to calculate the Total. There are some typos in the RoboPath doc.
This was a great challenge.

This was a great exercise. First, it failed! :smile: The reason it failed was due to the missing prices to calculate the total cost. To get around this issue, I used an IF statement to bypass any empty Price cells, which worked. However, I then figured out that the reason I was not getting all the prices of the books was because of my Data Extraction. I was only grabbing the default amount of rows of 100. After I changed it to 1000, it then went to each web page and grabbed all the prices of the 1000 books listed on the website. It then matched the prices to the books accordingly and then finished successfully.

The VLookUp worked fine for me. I did not have to do any sorting.

image

Great exercise…if you don’t extract all the prices the robot will fail so you need to enter an if condition to check the price and then calculate
But I have a question regarding the first example with qoutes and authors…I noticed that the url part is not extracted correctly…i mean you have in url only /author/Steve Martin to give you an example…the http://quotes.toscrape.com part is missing so the url is not complete…any ideea?

cheers
G

I tried to extract data such as the book name, however there is an error “Please indicate fields that are correlated with the fields you indicated to define the first column”… but when i go back, the genre has “Books” inside also… what should I do to be able to correctly extract the book name and prices?

Where do you get this Property panel? I can’t find it and when I was doing the exercise I had to start from scratch the data extraction when I forgot to amend the number of results.

Had same problem, in my layout Properties is in “hidden” mode and you can find it on the right side of the StudioX interface. Probably in your case it could be on the left side. Hope it was helpful for you!

Thanks for all of the advice above. The “100” limit got me as well as many above wish i knew about the properties panel fix. Although I remapped everything for every genre that was necessary (Plus realizing that Books tab didn’t have a genre added so i added a new scrape) and then realized that changing “100” max to “0” was the solution that would’ve saved me alot of time. I did the same solution of skipping null prices when it was set to 100 so that was great that someone else found that same answer.

It marks me a data type erro, as I can leave the number format by removing the euro symbol

1 Like