Excel data Help

I have a Datatable which has many columns.
The 1st column is the main column which has the keys. The rest of the columns have the data for these Keys ( in the first column)
My process should be able to loop through taking the key from 1st column and the corresponding value in 2nd column.
The process should continue till all the keys of the 1st column have the corresponding values from 2nd column.
When it is just 2 columns it’s easy to use for each row but the requirement is once all the keys have values from col2 , only then it should go get the values from Col3 and so on.
Can anyone suggest how to achieve this?

Can you please provide some screenshots for better clarity?

Please provide input and output sheet.

The input sheet screenshot is attached.
The expectation is it should first loop through Key and Values1 columns. after this is done Keys and Values2 column and so on.
The combination is
Keys-Values1
Keys-Values2
Keys-Values3 and so on depending on the number of columns in datatable.

The loop should work in this way.

As a output do you want to store this data?

if yes then in which format you want to store this data?

I don’t want any output . I have to use the data in the excels to search the Key and put its value in web application.
The requirement is Keys column remain same only values column will should be looped as I mentioned earlier

@Abc_Xyz1

Follow the steps…

  1. Read the data into datatable…
  2. Use for each tow in datatable…
  3. Inside that use for each activitiy and give input as dt.Columns and change type argument to datacolumn.
  4. Inside the second loop currentrow(curremtitem.ColumnName) will give you each value one after the other…

So the loop of rows and columns both is done

Cheers

Thank you for the suggestion.
I did try this and seems like it is taking the values from all the columns for the first col.
I would want to read first col “Keys” and “Values1” combination first. then col Keys and Values2 for the 2nd iteration.

Is it possible?

@Abc_Xyz1

Try keeping the column loop outside and then for eqch row loop inside

Cheers

currentrow(currentitem.columnname) is throwing error. Option Strict on disallows late binding.

@Abc_Xyz1

After changing did you change the type argument in for loop…is it changed to datacolumn?

Cheers

I tried this but the first iteration is all blank values and it seems to work properly after the first 1st iteration.
Can you suggest on what could be the issue here

Any update for this ?

@Abc_Xyz1

You can add an if condition to check if any record is blank and of not only then proceed ahead

IsNothing(CurrentRow(currentitem.ColumnName)) OrElse String.IsNullOrEmpty(CurrentRow(currentitem.ColumnName).ToString.Trim)

Cheers

My bad… It’s actually not blank values instead it is taking the first column which is Keys as the input in the first iteration and from second iteration it is taking Keys and Values columns.
Is there a way that the iteration starts from the 2nd column and continues to iterate over each key and type the corresponding value for it ?

Example : first iteration it’s typing keys1,keys2 ,keys3 and so on … From second iteration for Key1 the corresponding Value1 is being typed which is correct.

@Abc_Xyz1

Okay so you are saying key column is not needed

Then use if condition with currentitem.ColumnName.equals(“key”) and on else side use your activities

Other way is to change the seconf for loop to Enumerable.Range(1,Dt.Columns.Count-1).ToArray

And change type argument to integer and then instead of currentitem.columnname use currentitem

Cheers

Can you elaborate .
Also, currently it is working like mentioned below :
Example : first iteration it’s typing keys1,keys2 ,keys3 and so on … From second iteration for Key1 the corresponding Value1 is being typed which is correct.

@Abc_Xyz1

In the first for loop use a if xondition as stated above … on the else side use for each row in datatable

Cheers

You mean the first loop, item in dt.columns I need to add if condition
If item.columnname(“columnname”)
Then do nothing ?
In else use for each row in dt ?

@Abc_Xyz1

Exactly …columnname is the key column name you have to give so that in the loop it skips that particular column

cheers

Thank you… It worked

2 Likes