Updating an excel depending on the previous information

Help
1

Hi all,

I am creating a script that takes a monthly file with account information and updates a master file based on it.

If the account exists in the master file, it should find in which row is that account in the master file and update the info in columns E and F (of the master file) with the info from the columns H and J (of the input file).

If the account does not appear, it should add the account to the first empty row of the master file, taking only the information of the columns A,B,C,E,H,J,N of the input file and setting that info to the columns A,B,C,D,E,F,G of the master file

I have trouble figuring out what to put in the THEN and ELSE conditions after getting the script to compare the first columns and identify if an account exists or not in the master file.

Any help is appreciated, many thanks!

2

First find out if account exists or not.

If accountExists Then
Update the required columns
Else
Find out the next empty row in master file
Add the required columns into a master file.

Regards,
Karthik Byggari

1 Like
3

Thanks Karthik. The basic structure is done, but I am having problems with the details.

I have a For Each Row set to find if the account exists. The For Each Row activity has the value n

I am setting the “Else” section and I have this, but it is not working (It is overwriting the entire sheet with blank cells):

For Each item in DatabaseUsernames (set to value l)
If item.ToString = “” then break, else is empty
Write cell: “Sheet1” | “A”+(l+2).ToString | AccountNameVariable
(Same Write cell format repeated for the other rows)

My guess is the formula “A”+(l+2).ToString is incorrect but I am completely lost on how to fix it. I also tried “A”+(l).ToString but I received an error (Range does not exist).

1 Like
4

I am giving explanation here with a sample example.

Master File:

AccountNumber Data1 Data2
1 A AA
2 B BB
3 C CC
4 D DD
5 E EE

Input file:

AccountNumber Data1 Data2
2 AAA AAAA
5 BBB BBBB
6 CCC CCCC

Now you have to update the master file w.r.t input file.

Follow these steps to achieve the result -

  1. Read input file into a data table using Read Range Activity. The output will be saved into DTInput.
For Each row in DTInput 
{
     accountNumber = row(0).ToString
      //look for this value in the master file
      findRow = Use excel look up range activity to find the row in the master file
      //https://activities.uipath.com/docs/excel-look-up-range 
      // this activity returns the address of found cell. E.g., "A3"
      If findRow <> "" Then
           //account number found in the master file
           rowNumber = Extract only the number from the findRow (using string manipulation or Regex)
           //update the Data1 and Data2 values using Write Cell activity
           Write Cell Activity, Value = row(1).ToString, Address = "B" + rowNumber.ToString
           Write Cell Activity, Value = row(2).ToString, Address = "C" + rowNumber.ToString 
       Else
           //account number not found in the master file
          //Append Range activity will append the next available empty row with the given row
           Append Range Activity, Value = row
}

The above logic will update existing account number details if found else inserts a new account details into a master file.

Regards,
Karthik Byggari

1 Like
5

@KarthikByggari,
i have same case with @Naelin,
if possible, could you give us xaml file for example of this case.

Thank you for your help

6

Hi @zonda_rugmiaga

I will send you the XAML as early as possible.

Regards,
Karthik Byggari

7

Thank you for your help @KarthikByggari
hopefully i can get it soon… :blush: