How to get data table with selected columns from excel sheet

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?

4 Likes

well you can use excql as a data source and use queries but i have one more way to do this :slight_smile: here for example purpose it will show only two columns data i.e. Date and Close Column :slight_smile:

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

9 Likes

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

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.

2 Likes

Yep, it worked. :smile: Thanks, guys!

1 Like

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

2 Likes

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

1 Like

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

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

Datatable Results_of_two_cols = Yourdatatable.Select().CopyToDataTable().DefaultView.ToTable(False, Your_DataTable.Columns(1).ToString, “Close”)

Regards…!!
AKsh

hi,

please follow the tutorial ,

thanks,
Karthik.

1 Like

Hi @aksh1yadav,

How can I keep the column name? Thanks

Regards,
Lavina

Hey @lavint

Did not get you mate, :slight_smile:

Regards…!!
Aksh

I don’t get myself as well - Forgot my initial confusion. But thanks for the reply anyway…:blush:

Check out this video to get selective columns from excel!