Get a column in Excel that contains the word “Ticket”
Get each value under that column
For each value under that column I want to open a website and insert that value
I have an idea on how to approach steps 2 and 3 which would be a Read Range + For Each Row and continue with the rest of the automation I want to build. My problem is in Step 1. The column will not always be in a specific index or consistent naming convention, but it will always contain the word “Ticket” in the cell value. I’ve tried Screen Scraping the Excel work book, but I have trouble instructing the system to look for a pattern containing “Ticket” and getting the values underneath the header.
no worries, do this
1- ReadRange get out put as outDataTable
2- for each row inside this loop use type into and pass the value like this row(“yourcolumnName”).toString
I’m doing that, but the problem here is that I won’t always have the “ColumnName” exactly the same or it being a column. It could be underneath another column itself.
The only consistent information is that it will contain the word “Ticket” inside a cell. Thus, why I was inclining more towards Screen Scraping rather than Read Range to be more dynamic.
I even tried renaming column to “Ticket” and see if it finds and it doesn’t giving out this error:
"RemoteException wrapping System.ArgumentException: Column ‘Ticket’ does not belong to table DataTable. "
Fine
Hope these steps would help you resolve this
—use a excel application scope and pass the file path as input
—and inside the loop use a read range activity and get the output with a variable of type datatable named dt1
—first let’s check along the header level with a simple assign activity like this int_colindex = IF(NOT dt.Columns.IndexOf(“Tickets”).Equals(-1),dt.Columns.IndexOf(“Tickets”),0)
Where int_colundex is a variable of type int32
—now use a FOR EACH ROW activity and pass the above variable dt as input
—inside the loop use a assign activity like this int_colindex = Array.IndexOf(row.ItemArray,”Ticket”)
—then use a IF condition like this NOT String.IsNullOrEmpty(int_colindex.ToString)
—if true it goes to then part where use a BREAK activity
As we have found the column index which has ticket value in it
—now next to this FOR EACH ROW loop use another FOR EACH ROW activity and pass the variable dt as input and proceed with your next sequence of activities
Thanks @Pradeep_Shiv and @Palaniyappan it gave me a pretty good idea for the flow. Now I used a “get Row Item” activity to grab the values under “Ticket” column and it worked like a charm.