List values in a column into one variable

Hi, I would like to get all values in column C and store it in a variable without using loop function because I have more than 10k data and it would be time consuming for me to loop through every row.

I tried using multiple ways (example as below) that I found in this forum but all of it doesn’t work.

e.g.

  1. (From row In DT.AsEnumerable() Select Convert.Tostring(row(2))).ToList()
  2. (From r In dt.AsEnumerable Select r(2).ToString.Trim).ToArray
  3. dt1.AsEnumerable.Select(Function® r(2).ToString.Trim).ToArray
  4. (From x in dt.AsEnumerable
    select Convert.ToString(x.Item(“column_name”))).ToList

This is the value that I get when I run it.

image

@jenkim
I would suggest to use: dt1.AsEnumerable.Select(Function( r) r(2).ToString.Trim).ToList
returning list of strings

Your StringListVar. toString is not displaying the values, but the datatype

following can be used:
YourListVar.Count.toString - returning the count of the list
listing all values: String.Join(" | ", YourStringListVar)
but listing 10k row data maybe is not helpfully.

3 Likes

Hi @ppr,

Thank you for your feedback. I tried as your suggestion but it seems like the value are not correctly stored.

For example in my excel column 2 have this value:

1,2,3,4,5,6,7,8,9,10

When I print the variable it becomes:

1,2,2,2,2,2,2,8,8,10

Do you have any idea how this happen?

@jenkim What is the Linq Expression you have used before that ?

I think maybe because the values inside that column contains excel formula. So it returns incorrect value when I try to print it.

I tried again but after remove the excel formula and it works.

Thanks

1 Like

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