How to get selected columns from excel sheet into a data table in Excel activity?
Suppose “Sheet1” of an excel file is having 100 columns, but I need to work with only 10 columns. So how can prepare a data table having only required 10 columns? Just like “Select column1, column2 from Table1” SQL Query.
Is there any way?
well you can use excql as a data source and use queries but i have one more way to do this here for example purpose it will show only two columns data i.e. Date and Close Column
Datatable Results_of_two_cols = Yourdatatable.Select().CopyToDataTable().DefaultView.ToTable(False, “Date”, “Close”)
Note - If you wants rows that have distinct values for all its columns then just set
DefaultView.ToTable(True, “Date”, “Close”)
Is there a particular namespace to be imported for CopyToDataTable() ? I googled it and i have the System.Data, but somehow the CopyToDataTable is not recognized on my side
Actually, it’s not necessary. But as soon as you type it into the activity the error will disappear.
Somehow there are some methods that are not recognized by the UiPath intellisense.
Yep, it worked. Thanks, guys!
Yes @ovi as melo said intellisense sometimes not able tl load some of extention or utility methods so if u know that method exists try with a go that error icon will remove
It is not working. When I tried to write range new datatable with selected columns into a new sheet of same excel file, it printed all columns.
This will write the new datatable to an output.xlsx file in the same directory.
It worked. Only difference was a method calling bracket for CopyToDataTable. It should have been “CopyToDataTable()”.
Actually the problem I am facing is, even if Linq library is there in “imports” but intellisense is not providing me Linq methods in “Invoke Code” Activity.
Any clue about this, why is it happening?
Well methods are not showing?
Give it a try. just write that method even intelligent is not loading or showing it. After writing all i guess if those methods exists then error will disappear.
if column name is not fixed then how do you do this ?
if columns name are not fixed then you should know them or pass them in array and then make a string pattern with string operation and pass in the above code how i have used for fixed columns
what i meant was.The column name is not fixed but i know that column location is fixed.Say for example if there are 100 columns then i want to copy only values of column a,b,d,e,f,v,x,y and so on. Is this possible ?
what you can do if you know the index of your column then you can easily use the column names with index location as well like below
Datatable Results_of_two_cols = Yourdatatable.Select().CopyToDataTable().DefaultView.ToTable(False, Your_DataTable.Columns(1).ToString, “Close”)
please follow the tutorial ,
How can I keep the column name? Thanks
Did not get you mate,
I don’t get myself as well - Forgot my initial confusion. But thanks for the reply anyway…
Check out this video to get selective columns from excel!