I have an excel having 67 Columns. I simply need to add more 4 columns(age,date,status,salary) at different positions. How can I do that.
Hi @Gagan_Chaudhari , We could perform this by first preparing a Dictionary with Column Name as Key, and the Position of the Column (Integer) as the Value.
We can loop through the Dictionary and use the method mentioned in the post below :
We would first require to use
Add Data Column to add the columns and then perform the above method.
You can use the Add data column activity.
Can’t find the activity.
If I use Insert/Delete Columns then How Can I Name the Column.
Thank you for this.
Since I have to Add more 4 Columns at different positions then Do I have to use this code for 4 times. Is there any way of looping.
Because in future instead of 4, I may have to add 8-9 columns.
I am sharing a simple example. I hope it works for you. You can then create an array list or datatable that will expand
AddDataColumn.xaml (7.6 KB)
This workflow will help you to add as much column you need .
Sequence.xaml (10.1 KB)
Thanks for the help But the Activity is missing after Build Data.
This is Great !.
Can I rearrange the Sequence of the Columns.
Date Age Salary Name
Age Salary Date Name
You can use invoke method. You can either put it inside the loop or you can sort it outside.
When using in a loop , tagertObject = dt.Columns(item.ToString)
If outside the loop tagertObject = dt.Columns(“ColumnName”)
In the parameter section, you specify where you want it to be positioned. If you give 0, it adds it to the beginning.
@Gagan_Chaudhari you can rearrange the columns you just need to pass the column name in the target field of the invoke method activity and the pass the column position in the argument.set the method name as SetOrdinal and target field as dataatablename.columns(columnname).
You can also use this inside the loop for multiple rearrangement of columns.
Thanks a lot for the help.
I have Master Format file(67 columns) which have the Final Format of the Column.
I have to keep that Format for my Target File.
is there any way to Keep the ‘Master Format’ and Bot will follow that format only while rearranging ?
@Gagan_Chaudhari So your requirement is that you have a master file that have 67 columns and you need that column format in your target file.
Is that correct?
@Gagan_Chaudhari You can rearrange your target file columns of the same format like your master file.
First you need to create a system.data.datacolumncollection variable(arrColumnList) and assign value masterdatatable.columns.Then create a Integer variable and assign the masterdatatable.columnscount value.
create a counter variable(intLoopCount) also and assign it to zero.
Use a while loop and the loop should run upto the count of integer variable created.
Inside that loop use the invoke method and set the method name as SetOrdinal and target field as arrColumnList(intLoopCount).Set the parameter also as intLoopCount.
increment the counter variable also.
Try this method.