Write Data to Excel File

Hi all,
I have an Excel file that I’ve converted to Datatable and I want to add data to a certain column - according to a specific date (rows) and specific company code (columns).

Date (row) = Yesterday’s date
Once we verify this condition, it needs to paste the data from a variableA to the column related with the argument that gives us the company code.

[Check the screenshot above]:
So, for example, if in_argument is “1000” it needs to paste the data on 06/01/2022 (yesterday’s date) and then paste on column “1000 Mail”. If the in_argument was “2200” it would need to paste on “2200 Mail” and so on…

In the end, it needs to save the Excel file (so I believe an “assign” won’t solve this problem, but maybe a “write cell” does?)

Can anyone help with a solution for this?

Thank you so much in advance,

Hello @Tiago_Dias,

if you don’t specificaly need to modify the input Excel file where you got the data from:
Use the datatable. Add the correct value into the correct column and row (with an assign) and then create a new Excel file and use the write range activity.

If you must modify this input Excel than you have to find out in which cell you have to insert the value and use the write cell activity to do so.

BR,
Mike

Thank you @Mike_Goldes , but can you explain in more detail?

I was using a “For Each Row” and then an “IF” with the condition for the date = yesterday’s date but then how can I use an activity (in this case, write cell) to place the data from the variableA in the right column, according to the company code inputted as an in_argument?

I need to modify the initial Excel, converted to datatable, fill in with the data and then save the Excel again. Then, on the next day, the robot would run again, pick up this Excel file again (but now with the info filled in from the previous day) and do the same process.

Thank you in advance.

Here’s what I would do:

  1. Get the rowindex of the correct column (yesterdays date) with the Lookup Data Table activity (https://docs.uipath.com/activities/docs/lookup-data-table). This activity gives you the option to get the row index where the entry is found. Just enter the correct column name (“Date”) in the Lookup Column property. And use the correctly formatted yesterdays date (Datetime.Now.ToString("dd/MM/yyyy")) as the LookupValue.
    The output argument RowIndex will then give you the correct rowindex of your column.
  2. Then you need to find the correct column index. If your column names will not change, I would use a switch activity to retrieve the correct column index. E.g.
 switch (in_argument)
    {
        case == 1000:
            out_str_columnindex = "C";

        case == 2200:
            out_str_columnindex = "G";

        case == 4600:
            out_str_columnindex = "K";
    }
  1. Finally, use the Write Cell activity (Write Cell) to write the data into the cell out_str_columnindex + (int_rowIndex + X).ToString (X will compensate for the offset of your datatable in Excel - if it doesn’t start at “A1”).

Result e.g. “C7”.

@Mike_Goldes thanks again!!

Just to be clear, can you show me the swith using an activity instead of code?

image

How should I fill it?

The Lookup Data Table should be like this?:

image

Thank you in advance,

Ok,

just enter in_argument into the Expression field and type 1000, 2200, etc. into the field that appears when you click on Add new case. Mind that you can’t use variables inside the Add new case fields - only text.

The Lookup Datatable activity looks good.

BR,
Mike

Great @Mike_Goldes ! Just one more thing. The row that I’m obtaining is always -1 , and for example today, it should be row 13.

Might it be because there are more than one sheet in the excel file converted to datatable?

Please check my activities:

Read Range - I’ve set C2 as the range, because that’s the starting position of the date on my Excel (check image above)

image

Lookup Data Table:

image

Switch + Write Cell:

image

Thanks so much in advance for your help,

Well. First of all. The activities look fine, but I think it is supposed to be row 12, since today is the 11th.

Second. Please check the datatype of your “Date” column.
If it’s String - check your Lookup Value (Today.AddDays(-1).ToString(?)).
If it’s Date/Datetime you will have to ommit the ToString method in the LookupValue argument.

Thanks @Mike_Goldes . I’ve checked and the dates on the column “Date” from my Excel are in Date format, but if I put it like this, I get an error:

image

How should I do it?

Thanks,

@Tiago_Dias - can you please upload an example Excel file here. I will then check how to best implement the lookup.

Tracking KPI’s Test.xlsx (859.3 KB)

Here it is @Mike_Goldes . Thanks a lot for your help!

Hey @Tiago_Dias,

it looks like the Lookup Value activity doesn’t work with Datetime values (something I learend today :grinning_face_with_smiling_eyes:).
You will have to use a for-each-row activity to get the correct rowindex.

Just make sure to set the Output Index parameter in the for-each-row activity:

grafik

That should work now. :wink:

BR,
Mike

@Mike_Goldes I tried that but it finds the wrong row.

It should be row 12 but it fills row 9. Do you know why is this happening or how can I find out?

We are almost there!
Thank you,

Since your table starts at row 3, you need to caculate int_row + 3.
So: int_excelRow = int_row + 3 = 9 +3 = 12