Excel Header Reading

Help Studio
, ,
1

Hello,

I want to read excel header but the position is not constant.

MicrosoftTeams-image (2)

Here header is in 3rd row i used “Lookup range” activity to get the headers

but the problem is that iam using a write cell activity. how would i get the index corresponds to that header.

MicrosoftTeams-image (3)

2

@Gokul_Murali

Can you be more clear…are you trying to read or write?

if read then first you can use find/replace activity to get the required row…you can extract the row number from the returned value using regex Regex.Match(cellval,"\d+").value

if you want to only replace then in find/replace you can directlt give the replace value as well

cheers

3

@Gokul_Murali
Hi,

  1. Use the “Excel Application Scope” activity to open the Excel file.
  2. Inside the scope, use the “Read Range” activity to read the data from the Excel sheet. Specify the range that includes the header row.
  3. Store the output of the “Read Range” activity in a variable of type DataTable.
  4. Use the “Assign” activity to assign the header row to a separate variable. You can use the following expression: headerRow = yourDataTable.Rows(2).ItemArray.Select(Function(x) x.ToString).ToArray() This assumes that the header row is in the third row (index 2) of the DataTable.
  5. Use the “Assign” activity to find the index corresponding to the desired header. For example, if you have a variable named “desiredHeader” containing the header you want to find the index for, you can use the following expression: headerIndex = Array.IndexOf(headerRow, desiredHeader) The “headerIndex” variable will now contain the column index corresponding to the desired header.
  6. You can then use the “Write Cell” activity, specifying the column index obtained in the previous step, to write data to that column.

Hope this will work

4

Hi @Gokul_Murali

Try like this

image
image721×676 23.6 KB

I hope it helps!!

5

@Anil_G

I want to read an excel with header but the excel position is not constant so i used “Lookup Range activity to get the header” and is working.

NEXT:

After reading the input iam adding 3 columns with headers like below using “Write cell activity”.

MicrosoftTeams-image (3)

So how would i get the header index inorder to add my column

6

@Gokul_Murali

If you know the header names then follow above procedure it give index of headers.

7

@Gokul_Murali

Then after looks up first use read range and then dt.Columns.Count will give you the count of columns which can be used as your column value

UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(dt.Columns.Count+1)

this gives the last column excel name to use in your write cell

chees

8

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.