For excel automation, I can have header on any row. I need to delete all rows apart from header. I am not able to find best option. Please suggest.
In that case, you must specify range in Read Range and check add headers in properties.
Are trying to capture only the header of the excel? and omit the data that are under those headers? And the header can be available in any row right?
In that case, you can use a Read Range activity to read the excel. And you can uncheck the Add Headers property to add the header as a row in your datatable.
Then use a For Each Row activity and in the first iteration get the column names you want, and do a Break activity to exit the loop.
Is this what you need?
No no I don’t want to omit the data those are under header. I just want to delete all rows in a excel except the header. but the problem is header can be at any row.
For that you can use a Read range activity. In the activity, provide “” for the range property because we do not know at which point the excel has data. In the same activity uncheck the add headers property to read the headers as a row.
Now the read range will add the data along with the header row to a datatable.
Now use a For each row activity to loop through the datatable.
Have a counter within the loop to increment for each iteration
If counter = 0 do nothing
Else - use a delete row activity to delete the remaining rows.
This will keep only the header row.in your data table.
Now do a write range to write the header row back into the excel
1 . Use read range and store it in data table.
2. Use for each loop.
3. Use If condition inside for each loop, condition is string.nullorempty(row(0).tostring)
4. When the condition is true you will not be doing anything
5. When it skips and finds the value at specific row take that index, you will be getting the index from the for each output.
6. Now you have the index from where your data and headers start.
7. Now use read range and pass the index.