I have an excel sheet I use read range and create a DataTable.
I want to iterate through each column (number is unknown) and the column has no header. The column I want to start gathering data from is G. The row I want to gather data from is 14, ie G14.
If column G is null then the rest of the columns will be, too, so no more checks are needed.
Otherwise, if it is NOT null, I want to read column G, row 14 and then check the next column, example H, row 14. Once the values are null again, I want it to stop.
I have tried For Each Row: For Each row in DataTable
Then I don’t know how to specify the row and columns so it always looks on row 14 and starts at column G and iterates through the unknown number of columns.
You could use the column index instead of letter. The index starts from zero, so column A has index 0, B has index 1 and G has index 6. (This is under the assumption that column A to F are not empty.)
First use an If activity to check if there are more than 6 columns. If not, there’s no G column.
If DT.Columns.Count > 6
Loop through each column with a For Each activity starting from column G, index 6.
ForEach columnIndex in Enumerable.Range(6, DT.Columns.Count-6)
In the for loop you can get the cell value like this
value = DT.Rows(13)(columnIndex).ToString
Note that the row index also starts from 0 so DT.Rows(13) gives you row 14.
Receiving an error in the For Each.
I have For Each columnIndex in Enumerable.Range(6, DT.Columns.Count-6) and in the body Assign Value= DT.Rows(13)(columnIndex).tostring
I changed the Type Argument for the For Each loop from Object to String. I get the Error: For Each: Unable to cast object of type ‘System.Int32’ to type ‘System.String’
You can loop through every column in the datatable using the for each (change the item to System.Data.DataColumn), store the index number and use an If to do something only if the column index is greater than 6 (column G).
Or you can set an int variable with the column number (6 for the G column) and use a ‘Do While’ until the index is greater or equal to ‘dt.Columns.Count - 1’ (don’t forget to increment the index inside the while).
columnIndex is a number so change the Type Argument to Int32 for the For Each activity.
The Enumerable.Range(startIndex, count) generates an array of Int32. Let’s say the columns count is 9, then the code will generate the array {6, 7, 8} which let you loop through column G to I.
Thank you for taking the time to respond!
Still new to this.
When you say store the index number do you mean make ‘indexNumber’ variable? I assume it would be Int? What do I assign the ‘indexNumber’ variable? I changed the type argument in the For Each ccto System.Data.DataColumn and it created errors for the IF statement in the For Each body columnIndex > 6
2nd option
I set an int variable ‘counter’ = 6 and how would my ‘Do While’ look? When I put the condition in my Do While as dt.columns.count-1 it gives me the error string on disallows implicit conversions from Integer to Boolean.
I changed the Type Argument in my For Each to int32. Maybe it’s my value assignment that is causing my issue? I have value variable as:
value= dt.rows(13)(columnIndex).tostring
It was string and I tested it as myValue and running into same problem.
The read range activity range I used to create the dt was “”
The output panel shows a lot of empty lines >100
The result I should be getting is the value in G14 and empty for the rest.
Hello @Jessica_Moseley
I think you are looking for something like this:
read excel to datatable say dt
for each item in dt.row(13).ToItemArray
'here save counter in variable colCntr. refer attached xaml
If colCntr >= 6 then 'this condition will get true only from column G in excel
If Not String.IsNullorEmpty(item.ToString)
perform your action
Else
Exit For [break activity in UiPath. refer xaml]
End If
End If
Next item
Hello @uKisuke
I guess you miss the point where OP wanted loop on row 14 only [according to original question]. But in your code it is looping on all the rows.
If you want only the 14th row, all you need to do is remove the ‘For Each Row’, create a variable with the name row (as System.Data.DataRow) or replace where is referencing to row for dt.Rows(NumberOfYourRow) (leave the rest of the reference to the column and check the number of the row from your datatable).
But the logic to loop through the columns will be the same.
Edit: Forgot to mention, if you create a variable to the row, you will need to pass the datatable row to it.