Excel sheet names to output Excel

[heidi.michelsson1 (Heidi Michelsson)]

I have built an automation that compares two separate Excel files that have multiple sheets. Firstly it reads the sheet names and then reads through sheets/rows and if there are differencies, it copies that row to datatable and creates a separate output Excel of indifferent rows. That all works fine, but I’d need to add the sheet names of indifferent rows there as well, which I haven’t figured out how. I have the current sheet name in string variable, but how can I add that to datatable or Excel file as it doesn’t accept string values?

[sudster]

Hi @heidi.michelsson1 ,
After you extract an Excel sheet to a datatable, add a data column to the DT with the name of the sheet as the default value of the new column.

DataColumn.DefaultValue Property (System.Data)

I want to add new column to a table with default value based on another columns

I have a table called table1 as shown below [image] I would like to use the “Add data column” activity to add a new column whose default value should be “PO_”+ Column2 value. I would like to achieve that using the Default value option of the “Add data column” activity and without for loop. Thanks in advance

Data Table New column with Default value

@ankur7185, Are you getting any error ? Could you please share your work and will check and update you.

[heidi.michelsson1 (Heidi Michelsson)]

Thanks for these @sudster, but since I’m still newbie with UiPath, I’d need some elaboration… I actually managed to save the sheet name to collection by Add data row, but then when I added the sheet content by assign activity, it replaced the sheet name. I also tried using Append items to Collection for content input, but that didn’t write anything to datatable.

[jast1631 (Jagravi Trivedi)]

Can you share screen shot of the code for reference
And exactly what is your query for sheet name @heidi.michelsson1 if h can elaborate query
It will be helpful to answer

Thanks

[heidi.michelsson1 (Heidi Michelsson)]

Thank you @jast1631 for your time Here’s that part that’s basically working, but since it’s an assign action, it replaces the previous Add data row. I tried to use Append items to Collection there as well, but that didn’t do anything, so obviously my approaches are not correct. (For clarification, dtSheet1 and dtSheet2 are the contents of the Excel sheets and currentItem is the sheet name)

[SorenB]

Hello @heidi.michelsson1

You could use a Build Data Table activity for your results/output of the rows you want to extract.
Make sure this datatable consists of the columns you want to populate - eg. rowvalue, name of sheet and such.

Then when iterating the rows and extracting the ones that meet your criteria, you create a new row in this “result/output” datatable by writing:

Assign dtr_NewResultRow = dt_Results.NewRow()

(dtr_NewResultRow is variable of Type ‘Datarow’)
And populate the columns with the desired data:

Assign dtr_NewResultRow("Row Value") = "<data extracted>"
Assign dtr_NewResultRow("Column Name") = Excel.Sheet.ToString

Remember to use Add Data Row activity to your results-datatable after populating the row.

Regards
Soren

[Anil_G (Anil Gorthi)]

@heidi.michelsson1

if you need to add the sheet name against each row…then when you are getting th iddiferent rows and adding to datatable…at the same time add the sheetname as well in a different column

alternately if you need to add sheetname into separate sheet

then just loop on array and use add datarow inside to add the sheetnames to datatable and then use write range

cheers

[heidi.michelsson1 (Heidi Michelsson)]

Thank you everyone for your time and effort. I managed to resolve this by reading the content into separate datatable and merging it with the original (containing the sheet name).