Read multiple columns from Excel and save to one variable

[nmjvk]

Dear UiPathers

Suppose I have an Excel document which has 26 columns ranging from A to Z.

Is there a way to take, let’s say, column A, C, D, F, I, K, V, W and Z and have it all in one variable?

I have a task where I need to collect some information about public transportation from one Excel document and paste it in another Excel document, and there are well over 100 columns, however, I don’t need every column. It would be a burden and not so nice to have 80 DataTable variables. I know UiPath gives the option to assign a variable to the “Array of [T]” type, which is very much what I am looking for. I want all my chosen columns to be in some type of array which I then in return can loop through and write to my other Excel document.

I hope I provided enough information, else, reply back to me if you need additional information. Hope to hear from one of you. Have a great day.

[Anil_G (Anil Gorthi)]

Hi @nmjvk

Read all the columns and delete in a loop all columns what you dont need …Ideally from the info I can see you dont need even columns…so delete all of them

Or you have read column which read whole column as array of objects

cheers

[nmjvk]

If I can just add a little note. I can’t be guranteed that is not “even columns”. We get new Excel documents everyday. The only thing I am guranteed is the name of the columns I am looking for. One day it could be A,B,C,D I am looking for, the other day it could be A, I, Z, J. I hope that help to clarify.

And regarding your suggestion about reading all columns and deleting, how would the procedure be for that? I guess once I have read all 100 columns I have one DataTable variable. Is there a way to say “If column name DOES NOT START WITH xxx THEN DELETE THAT COLUMN”?

[Anil_G (Anil Gorthi)]

Hi @nmjvk

Yes thats the approach

so this is how it works

Initiate a counter = 0

1.Read all the data into datatable (dt)
2. Use for each loop with in Argument as Enumerable.Range(0,dt.Column.Count).ToArray()
3. Inside the loop use a if condition dt.Columns(counter).Columnname.contains(“xxxxx”)
4. Inside then use Remove Data Column and counter as column index
5. In the else condition use a assign counter = counter + 1

If you want to check starting only then use Startswith instead of contains

cheers

[nmjvk]

@Anil_G

Enumerable.Range(0,dt.Column.Count).ToArray()

Dear Anil

I very much appreciate your reply, I just have one small question. Is it possible for you to ellaborate on your second step " Use for each loop with in Argument as Enumerable.Range(0,dt.Column.Count).ToArray()"

[Anil_G (Anil Gorthi)]

@nmjvk

with

@nmjvk

Enumerable.Range(0,dt.Column.Count).ToArray() - This will generate an array from 0 to the number of columns in the given datatable(dt)

So You are ideally running the loop to check each column in the dt

I asked you to initialize a counter because once you delete the column the index of the next column will change

cheers

[nmjvk]

@Anil_G

Enumerable.Range(0,dt.Column.Count).ToArray()

Dear Anil

I am bowing down, your solution works very well. Thank you so much for helping me out. Have a fantastic day/ night forward!