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

excel
activities
macro

#1

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?


#2

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.


#3

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


#4


#5

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.


#6

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


#7

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


#8

@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:


#9

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