How to get all columns (example A,B,C ...) in excel and return to array

Help Studio
, ,
1

I have an excel file and columns will be like this

image
image1357×109 3.53 KB

I try to get all of those columns and return as array.

if someone don’t clear what is my purpose i explain here …

for now i have to create them all target columns are like this
image

image

imagine if we have too much longer … might be the last will be KA, KB, KC, …
that mean not convenient for dev and not dynamically concept that we learning for.

Thank you very much for any solution and big thanks for and example coding to easy to understand.
Rugpong

2

Hey @lovepong66

Read the excel then in assign activity

ArrayVariable = yourdatatablename.AsEnumerable().Select(Function (a) a.Field(of string)(“yourcolumnname”).ToString).ToArray()

Hope this is helpful
Cheers.

3

HI,

How about the following expression? (Case for 100 columns)

Enumerable.Range(1,100).Select(Function(i) ExcelUtilities.ConvertColumnIndexToColumnLetter(i)).ToArray

image
image1097×667 37.8 KB

Note: please add UiPath.Excel.Helper namespace in advance.

Sample20230324-5L.zip (2.4 KB)

Regards,

2 Likes
4

Hi @lovepong66

You can follow this approach:

  1. Read the excel file without headers, so that the column names will become the first row of the data table.
  2. Assign:

columnsArray = dt.Rows(0).ItemArray.Cast(Of String).ToArray()

Look at this -
Input Sheet:

image

Output Array:

image

Hope this helps,
Best Regards.

1 Like
5

Actually in my excel data don’t have columns name. that why i cannot specific anything
for example the fist one excel has a A - IF columns maybe another one file it might be has A - AK …
that mean you never know how many columns inside the file that why i tries to find the way to read columns in dynamically.

image
image1274×112 10.8 KB

6

good idea but how can i deal with dynamic columns example you set range to 1,100 but actually in the file we have 105 columns or another file is 107 columns. Thanks

7

in my excel doesn’t have a column name.

8

@lovepong66

You can use this as the dynamic methos

Use this in assign …assumption is you read the data from excel into a datatable dt

Enumerable.Range(1,dt.Columns.Count).Select(function(x) UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(x)).ToArray

This will count the columns and create an array of column names in excel automatically using the column number

Cheers

1 Like
9

HI,
First Read table using ReadRange (let’s sat dt as datatable).
Then dt.Columns.Count will return number of Columns.

Regards,

1 Like
10

oh i got it. big thank for this

1 Like
11

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.