How to get data table with selected columns from excel sheet

excel
activities

#1

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?


How to read multiple excel columns in DataTable
#2

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


How to remove multiple columns from an excel datatable?
#3

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:


#4

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.


#5

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


#6

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:


#7

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.


#8

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


Remove multiple columns in EXCEL SHEET
How to remove multiple columns from an excel datatable?
#9

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?


#10

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


#11

if column name is not fixed then how do you do this ?


#12

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


#13

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 ?


#14

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


#15

hi,

please follow the tutorial ,

thanks,
Karthik.


#16

Hi ,

I want to read every folder , inside the folder(many excel files are there), Read each file and Merge it into a single file. and also i have to remove blanks in the excel file .and finally every folder must contains single excel file .
Please help me to solve this.


#17

Hi @aksh1yadav,

How can I keep the column name? Thanks

Regards,
Lavina


#18

Hey @lavint

Did not get you mate, :slight_smile:

Regards…!!
Aksh


#19

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


#20

Check out this video to get selective columns from excel!