How to write cell in specific column name?

Help Studio
1

Hello,

I need to find a solution to write values in an Excel Sheet. In my automation project I built a Data Table with Name and Value columns. The data table is filled during processing. In the end I want to write the values into that Excel Sheet.

I want to loop through the DT and find the matching column name in Excel to write the value in that specific column.

How can I do this?
I attached a picture where you can see on the right side the data table and on the left side the Excel table/sheet. I want to write the values from DT to Excel.

Thanks.

The image shows two Excel tables with the same data arranged differently; the table on the left has data in a horizontal layout, while the table on the right has the data transposed into a vertical layout with corresponding headers, indicated with a red arrow. (Captioned by AI)
The image shows two Excel tables with the same data arranged differently; the table on the left has data in a horizontal layout, while the table on the right has the data transposed into a vertical layout with corresponding headers, indicated with a red arrow. (Captioned by AI)1238×189 8.3 KB

2

You can easily achieve using transpose Datatable activity and paste it in the required range in excel. Include headers as well

use Transpose Data table activity first
Then Write Range to excel

For using transpose activity in datatable - download the below package

image
image1886×857 137 KB

image
image1168×713 79.7 KB

1 Like
3

Hi @SenorChang

→ Write the datatable in excel sheet.
→ Then use the Copy\Paste range activity to copy the input sheet and paste it in other sheet, check the Transpose option to change the output as you like.

This transpose option will change columns as rows and rows as columns.

Hope it helps!!

1 Like
4

Thank you. I thought of this idea too but the problem is that the columns in Excel table will be extended in the future and if I just transpose the data from data table I fear that some values will not end up in the correct column. Therefore, I want to search the matching column and then write the value so that I am sure that I hit the correct column.

5

Thanks for sharing this very interesting package. I will try to use the transpose function and see how I will come along with that solution.

1 Like
6

@SenorChang

  1. read the excel to datatable with headers option checked
  2. now use add datarow on the datatable and give {} in arrayrow so that empty row is added at end
  3. use for loop on the datatable you have with transposed data
  4. inside loop use assign with datatable from step 1 dt.Rows(dt.RowCount-1)(currentRow(0).ToString) = currentRow(1).ToString
  5. write the updated dt back to excel

this ensures based on column name in datatable value is updated to actual datatable

cheers

7

Hello,

Thank you for this proposal. I would love to try that out but it’s hard to follow from that description. Could you provide a short example file to understand the logic? Would be really appreciated.
Thanks!