Week 4 - Solve Industry Use Cases - Reconciliation of Two Reports

Maybe you can also use “Delay” function.

Reconcile 2 reports - Good exercise and demonstrates skills/corrections needed from previous training.

1 Like

Hello!!!

I had the same problem in Spanish. I solved the problem, adding the language to the formula.

=TEXT(A1;"[$-en-GB]mmmm")

I hope it helps you!!!

2 Likes

I would like to ask which way is better to pass from one application (web) to another (Excel) right currency (number) formating, because in one case decimal simbol could be comma in other it is point?

1 Like

The way “Download File” works is it monitors the folder you specify for downloads to complete, it isn’t able to tell the target application where to place the downloaded file. Meaning when you click “Download” on a website, if the browser automatically downloads the file to “Downloads” the “Download File” activity has no way to tell the browser to place it into a different directory,. What you are doing is telling the “Download File” activity to monitor whatever folder you indicated for a downloaded file to appear and finish downloading before continuing execution. So if you choose a different folder than the browser is placing the file into the “Download File” activity is watching a folder that no file is ever downloaded to, and eventually gives up (the default is after 300 seconds). So the problem “Download File” is solving is “wait until the file I’m downloading into this folder finishes downloading so I don’t have to guess how long of a delay to use”. It isn’t solving “tell the target application to download a file into this directory”.

3 Likes

See this for the way to work with PDFs currently in StudioX How do I read a PDF file in StudioX and extract the data to an Excel file

1 Like

This is for anyone trying to use different local language for use in English website with Months:

In practice, you will most likely be dealing with entire dates (not individual months). This means you will have a format of either MM/dd/yyyy or dd/MM/yyyy (not MMMM). Also, you typically don’t want to convert values using by writing an Excel formula just so you can select it on a website in that format. However, I will show both approaches, just in case.

First approach is “let’s just convert it as we use it”
However, if you plan to use this conversion more than once, then you might need to store the converted value in the file or somewhere in memory during runtime.

Here is how to convert the month using simple .net concepts in the Advanced Editor:


This converts the Month value to your month in your language, then converts it go English

Convert.ToDateTime(CurrentRow.ByField("Month").ToString+" 1").ToString("MMMM",new System.Globalization.CultureInfo("en-US"))

I needed to add " 1" next to the month to make a valid date. However, if you have an entire date, then you don’t need to do this.

For the second approach “using Excel formula”, I suggest taking a look at a slight change to the formula as presented by @ialvarez
Week 4 - Solve Industry Use Cases - Reconciliation of Two Reports
The language can be changed from en-GB to en-US or de_DE, etc, as desired.

Let us know if this helps solve it for you guys.

Regards.
@HumCZa @VPetrini @Asher_Lake


Sorry about EDIT: I realized later, you only need to convert it once to a date, then use the cultureinfo:

Convert.ToDateTime(CurrentRow.ByField("Month").ToString+" 1").ToString("MMMM",new System.Globalization.CultureInfo("en-US"))

ClaytonM

2 Likes

Uff Finally completed!!!
Made couple of mistakes which helped me to investigate and learn.
After reviewing the solution from UIPath, I realized that my solution was not efficient because I was having code to handle the not necessary setting in the browser for asking for the folder where to save the file. Then turned OFF the setting, because for automation execution this is time consuming, and created a new project modifying the code accordingly. Lot of learning with this practice. Thank you.

1 Like

I was able to use a different folder than the Downlaods one; but for this I had to turn on the browser setting to ask before downloading the file. But this solution require additonal Click activity to response the new Window that is displayed. This add time and complexity to the code, so I decided the default Downloads folder.

1 Like

Thanks alot Andrew! I finally finished all the cases in Week 4. Abit more challenging. Along the way, I realised we can actually tweak our own solutions but still get the same results as in the solutions provided.It is really a good learning point. Makes me think how can I best define this Robotpath map and translate it into the workspace. And also identify & eliminate certain steps which may not be necessary and would only the delay the whole processing.

1 Like

I did Delay 2 Seconds as ur recommendation. It works! Thanks yea!

1 Like

Thanks @ClaytonM I will try your suggestions

Thank you, your .net code is working but I would never guess it.

So in Excel french syntax the parameter separator is “;” which is refused by UiPath that only want “,” when you write in cell:

“…si vos données représentent une formule, veillez à utiliser une virgule comme séparateur de paramètre.[…]”
"…if your data represents a formula, be sure to use a comma as a parameter separator.[…]"

When I looked if I can change this paramater in Excel… In fact I have to change Windows parameter to change this.
There is no way to make UiPath accept the semicolon as a parameter separator?
For exampe in Google Sheet when you import a CSV file they ask you if the separator is something else than a comma for such case as the french Excel…

1 Like

I have another issue to finish this activity:

For some reason my calculation “SUM(E:E)” doesn’t work during the automation run. It show #NAME? in the cell instead,

UiPath-name
and the calculus in the export_total file show “-2146826259”

UiPath-moins-moins

I have to manually click on the H2 cell and press Enter to make it work.

1 Like

If I understand you right, you are trying to open a CSV file with the Excel activity, and the file isn’t separating the columns. You can use a Read CSV, select the delimiter property, then write it to excel from that data.
image

For info on how to get Excel to automatically use that delimiter, you can check out this link on how to do that in the Region settings: https://www.koskila.net/how-to-change-the-delimiter-when-exporting-csv-from-excel/

Sorry if I misunderstood.


As for your other issues:

:NAME?
When you write the formula, are you using the Write Cell? Maybe it has to do with how you are writing the formula to the file. If you could show me a screenshot or .xaml of your code, I can take a look at it.

exponential format
The number will change to exponential format automatically by Excel if the number is too large. To get around this, you will need to either change the size of the column big enough prior to the number being inputted into the cell OR changing the column format to “Number”. This formatting can be automated too, however I’m not 100% sure on how to do that with StudioX… maybe using Send Shortcuts or Type Into (where you use Alt+keycombinations, which let’s you execute Ribbon options).

It’s really just a User-experience problem cause the value is still the real value in the cell.

Regards.

1 Like

Hello,

Thank you for your reply.
The CSV was part of the problem.
But I’m more concerned about the Excel functions parameters separator. For example:

=TEXT (A1*29,”mmmm”)

is in french :

=TEXTE(A1*29;“mmmm”)

And it’s refused by UiPath during the run time because of the “;”


About #Name?, yes I’m using write cell

1 Like

I’m getting this error too, even while setting the Region separator to “;”
So there are few workarounds possibly. You can change your Region separator to use “,”, and Excel will then use the comma.

Or, you can type the formula in using a TypeInto, something similar to this:

although, I would try to avoid using TypeIntos for cell inputs.

@kj.fenton The Write Cell is not following the Region separator with formulas, so when something other than comma is used, it fails with error.
image

Thanks.

1 Like

I tested this using a SUM formula and had no issues. I’m wondering if something with the data on the spreadsheet is causing this. But, I’m not really sure what the problem is. If I come across an answer, I’ll let you know.

1 Like

From my experience, I had the opportunity to participate in a robotization project for credit card reconciliation processes for cooperative clients; in this the clients asked to reconcile the catgos generated in their credit cards Vs the invoicing that the client generated. The challenge was considerable, since on the one hand all the clinetes handled different credit card franchises and the statements or account statements were generated in different formats (PDF, image, consultation by virtual bank branch, etc); and additionally, the amount of administrative resources dedicated to the work demanded a high cycle time that was reflected in the high operational costs that reduced the profit margin of the service offered.
In the end, we managed to reduce the resources dedicated to this process by 70%, implementing RPA solutions that would take on that operational burden.

I have solved the issue regarding the language setting and the writing of the months that do not match the English dropdown menu in the application, in this way:

  1. I deleted from the workflow the activity:
    located above the activity <Select Item ‘Month’>

  2. In the file Project_Notebook.xlsx, sheet : I used the following formulas:
    image

The idea is to convert general number from the cell A1 first into a date with the corresponding month in the cell C1; then, in B1 convert in text (in English language, using the code[$-409]) the month from the date located in C1.

  1. Instead of the activity that I deleted, I put the activity 5 seconds. I’m not sure if this activity is necessary.

2 Likes