Excel scraping to Website

This is what I’m trying to do:

  1. Get a column in Excel that contains the word “Ticket”
  2. Get each value under that column
  3. 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.

1 Like

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. "

This is the workflow:

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

Cheers @eplr3198


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.

1 Like

Kindly close the topic with right comment marked as solution that would help others looking for ideas under your topic

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