When using write range, I check "exclude headers" and it gets rid of "Column 1", but keeps my actual header. How do I fix this so it removes my actual header?

I am using the write range activity to write a range of data from an excel file into the project notebook scratchpad. The range I am copying from the excel file is saved as a table in excel and has headers is checked off in excel. I do not want to copy over the headers into the project notebook so I choose “exclude headers” on the write range activity. My headers are still copied over. When I uncheck the exclude header box on the activity for testing, it copies over both my headers as well as another row of headers called “Column 1” Column 2", etc. How do I get UiPath to recognize my headers?

Hi @bot82

Before writing the datatable to excel using write range , use delete row activity with following paramters

image

Datatable : DataTable Varible

RowIndex= 0

Try this way and let me know if it works

Regards
Nived N
Happy Automation

@bot82 - Can you please show us the screen print of the excel file?

Is your headers located in column A1 B1 C1 etc etc ??

Update: I too tried and it is not working…

Input

image

WF:

Output

@studioX - Can you please look at it??

i’m not able to reproduce:

input:

image

output:

image

WF:

the source do you select it as i did “Table” or by indicating the range?

Hi @studioX - I do not want to hijack this post…Yes if we give table as a range it is working, but you if see my initial screen shot I did selected the whole range A:A…but it did not excluded the header…

So it will exclude the header only if you choose the table ?? Because it didnt work when i selected the range…Could you please try this and educate me what I am missing here?

No worries. Let me investigate a little and will come back
I would say that selecting A:A your header is A/Column1 not Project Name, but let me check

1 Like

I think I am facing the same issue. I am choosing the entire table or A:A for the range instead of indicating a specific set of cells as the range. The number of cells in the range varies in each iteration which is why I choose A:A.

Hi, I think I did what you are suggesting by deleting the first row shown in this WF. This is actually giving me the correct results, but I feel as if this is just a work around and something must still be incorrect since I have both the exclude headers checked off and a delete row activity. If functioning properly, I think I would only need one of these.

Indeed exclude headers is available if the source is a table and only in that case. Can you confirm that using a table as source will work fine for you?

to see that this “exclude headers” is not intended to work over a range you can try this to see what headers studiox sees for a simple range:

-for ech row on A:A
-write line-current row- Column name

he sees Column1 as column name even if my range is a column 1, 2,3, 4,…

I am using a table as the source and it is not working. The table is called “Complaints”.

image

is the table named same as the sheet, Complaints?

can you show me a picture like this one, as when you select the table from the + menu? … i think we might have a bug here

Hello, I realize after looking at your screenshot that although the data was a table, I think I was choosing the entire sheet for the data source rather than the table. When I switch to choosing the table, I think this will work however before I can fully test it, I get a different error message that no cells were found. This is because a few of my iterations should be blank (no results left after filtering). I thought checking “ignore empty source” would solve the issue, but I get the no cells found error message even when that is checked.

I would like to either skip the write range when the data table is empty or write blank cells over to the new destination, is there a way to do this?

image

exactly as i suspected, you were choosing the sheet :slight_smile: but thank you for helping me find another bug: if the table name is the same as the sheet name, the table is not properly displayed on the + menu. It was raised and a fix will be soon available.

can you describe a little more the new problem you encounter?
the error message and the flow?
are you using 20.10 studiox or lower?

I am using Studio X 2020.4.3

Error message:

WF:
image

Problem:
This WF is inside an excel for each row. For each “Center Name”, the complaints sheet is filtered by that Center Name. The resulting range is then written into the scratchpad. On the first few iterations it is working. On one of the iterations when there are no results after filtering I get the error message. I would like to leave the scratchpad blank and move onto the next iteration instead of the error message. The later activities count the number of rows in the scratchpad, so ideally the count for that iteration = 0.

Next steps in the WF, bot counts rows in scratchpad. Error occurs at the write range activity:
image

  1. can you post also the filtering condition? (i-m not able to replicate your error)
  2. after you filter the table/sheet Complaints, doesn;t remain filtered? wouldn’t it better to use a filter activity in the end of each iteration and check Clear any existing filter? just as you do with clear sheet
  3. wouldn’t help easier to use Copy range activity instead of write range ?
  1. This is the filtering condition:

  2. I don’t know how I could use the filter activity at the end of each iteration. The filter needs to happen before the write range so only the filtered results are written. Sorry I do not understand this suggestion.

  3. There is no reason I chose write range over copy range, but I don’t see why copy range would be easier. I can try to use copy range instead. My only thought is that with write range I can ideally choose “exclude headers” and write only the values and with copy range there is no exclude headers option so I would have to have that activity and a delete row activity.

you have in for each row 3 steps, just add a 4th step to clear the filter so when it goes to next iteration of the for each will apply the next filter

Hi, I added the extra filter activity in to clear the filter at the end of the iteration, but this does not help. The bot does not get up to this activity as the error occurs at the write range activity.

I would like to do something like: If results of the filter are empty (nothing matches the filter criteria), then write nothing into the scratchpad, so the Find First/Last Data Row returns 0. The problem is I can’t have an if statement after the write range activity because that is the activity that results in the error. I don’t know how to use an if statement before the write range activity and after the initial filter, because I don’t know how to check if the filter returns 0 results without using the scratchpad. When using the scratchpad I check if cell A1 is empty and if it is then there are 0 results, but when filtering an excel sheet, if I check A1 on the excel sheet itself and not the scratchpad, A1 will not be empty whether or not it meets the filter criteria. In other words, even if I filter a table with 100 rows and none of the rows match the filter criteria, Find First/Last Data Row will still return 100, it does not return the filtered amount.