Hi
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”)
Regards…!!
Aksh
Hi @aksh1yadav
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
Hi @ovi,
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
Hi @aksh1yadav
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.
For your Reference @abskulkarni - sample_for_kulkarni.zip (3.3 KB)
This will write the new datatable to an output.xlsx file in the same directory.
Regards…!!
Aksh
Hi @aksh1yadav
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.
Regards…!!
Aksh
if column name is not fixed then how do you do this ?
Hey @mudit
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
Regards…!!
AKsh
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 ?
Hey @mudit
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”)
Regards…!!
AKsh
I don’t get myself as well - Forgot my initial confusion. But thanks for the reply anyway…