Excel Data table Add Headers is inconsistent between local computer and VM

Hello,
I’m currently running into an issue with the add headers functionality when pasting a data table into a newly copied macro enabled excel spreadsheet.

  1. I use the copy file activity to copy an empty excel macro enabled (xlsm) spread sheet that only contains the macro i want to execute
  2. Open an excel file that contains the data i want to past to into a new excel spread sheet
  3. I do a read range of the whole spreadsheet with Add headers checked. I output that data to a data table
  4. I use the write range activity to paste the data table into the copied .xlsm file, add headers is checked.
  5. I execute the macro

When i run locally on my machine the data table paste that data into the .xlsm as expected. The headers are the 1st row in the spread sheet

Now when i publish the project and run through orchestrator on a VM, the data table seams to not realize that the headers are already there and adds Column0, Column1, Column2. . . above the “real” headers.

Now when i run the macro through the VM i get an error because the headers are not what the macro is expecting.

This is strange because it works on my local machine as i expect but gives different results when i run on a VM.
Does anyone have an suggestion on how to tackle this issue?

Hi,

This doesn’t seem right! Can you use Output Data Table and use the string in a WriteLine or Message Box, after you have used Read Range and display what it reads initially? Chances are, the Spreadsheet was blank on one of your runs therefore adding its own Column Headers. I’m not sure though.

Hi @ClaytonM
Thank you for taking the time to look at this! The data table that I create from the excel file is too large for a write line. I did use the Write text file activity to output the data.
On my machine I get the headers as I was expecting:
Number,Opened,State,Customer,Assigned to,Business duration,Resolved,Short description,Category,Subcategory,Detail,Client / Company,Reassignment count,Duration,Opened by,Close notes,Assignment group,Resolved by,Knowledge Article,Short description2

On the VM I get the following:
Column0,Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,Column14,Column15,Column16,Column17,Column18,Column19
Number,Opened,State,Customer,Assigned to,Business duration,Resolved,Short description,Category,Subcategory,Detail,Client / Company,Reassignment count,Duration,Opened by,Close notes,Assignment group,Resolved by,Knowledge Article,Short description2

1 Like

1 Like

So the problem is with the Read Range then, where it’s not recognizing the first row as the header. This is assuming the initial file has the correct first row and that you are running the process from the same version in Studio.

Also, check your packages in UiPath for Excel to see if they have been updated.

As a workaround, you could just uncheck Add Headers and use the column Index number instead of the header string.

I can’t say I’ve seen this problem before, but hopefully you get this resolved.

Regards.

1 Like

@ClaytonM

Thank you for the work around. That did the trick. I unchecked the add headers from my read range and also unchecked add headers from my write range. I also used the column index instead of the column names. The output to my text file is:
Column0,Column1,Column2,Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12,Column13,Column14,Column15,Column16,Column17,Column18,Column19
Number,Opened,State,Customer,Assigned to,Business duration,Resolved,Short description,Category,Subcategory,Detail,Client / Company,Reassignment count,Duration,Opened by,Close notes,Assignment group,Resolved by,Knowledge Article,Short description2

. . . but that is too be expected with my changes.
Hopefully UI path will put an update out soon to fix this.

we are facing the same problem… just need to go and check everytime you deploy to another machine. pain…

@fsilva
It is a major pain! It was due to the robots - (not studio) having a different excel package. I was also getting a DOM error when I would try to run a write cell activity on a VM. There is no clean way to update the packages on the robot.

This link should help if you run into any more issues with the packages on Robot:

We discover that in some versions the AddHeaders are by default empty and in others versions are by default False.

Surprised to read this as this is exactly what’s happening to me. I have a header and 1 row in my spreadsheet and when “Add Header” selected, it doesn’t return any row and when unselected, it returns a row but I therefore lose my column names which isn’t ideal!!

HI.

Can you describe the issue you’re having exactly?
Is this the same thing where it works on one machine but not in another (like VM)?

Make sure you have checked that “Add Headers” is selected and that the data has text in row 2 under the Header row, or it will have no rows. And, if “Add Headers” is not checked in the Write Range, it will have no output with no rows.

Also ensure that your Excel activity package is latest version.

Hi,

Thanks for getting back to me.

When I’m selecting “Add Headers”, it sets my “second” row as the column names and returns 0 rows. When I un-select “Add Headers”, it sets my column names as Column1, Column2, etc… automatically and returns one row but the problem with that is that I’m now relying on the column index rather than the column name in order to extract the relevant value.

The really odd thing is that I’ve been using it with other excel spreadsheets without any problems. Having said that, this is the first spreadsheet I’m dealing with that only has 1 row in it after the column header so I will try to carry out more tests today if time permits and check if it is related to the number of rows available.

Another thing I noticed which I don’t like is that somehow when the “cell” of the row is blank, it seems to set the value the the column name which makes no sense but it might just be in the debugger. I need to check the run-time values later and see exactly what’s up with that.

Note that I can’t see anything “strange” with the spreadsheet. For a second I thought that the app we use to generate these was maybe just sticking the .xlsx extension for a .csv file, so I tried to rename it to .csv just to be sure but it is indeed a binary file.

Thanks

Thierry

@ClaytonM I’ve just re-tested it and it is definitely a bug with the “Workbook Read Range” activity.

  • If it contains a header row and a single data row, it will return the header row but it returns 0 data rows when it should be returning 1.

  • If it contains a header row and 2 data rows, it will return the header row and 2 data rows which is the correct behaviour.

I’ve reported it to UiPath.

Thanks.

Thierry

I haven’t really seen this where it can’t pull the single row, out of tons of .xlsx files over time. So, that’s odd.

One thing you can do though if you have Excel installed, is use the Excel Read Range. This has an option for ‘Read Only’ and ‘Visible’, so you can read the data even if the file is in use.

I know it is odd but it’s definitely a bug :frowning:

As for using Excel, it is not an option. I don’t want to use this for back office processing (nor does our client) but thanks for suggestion.

Thanks.

Thierry