StudioX: Find the index of a particular column

[deepakkapri88]

I need to find the column index of a particular column from an excel file?
e.g.
Loop:
read csv file
dump csv data to Notebook’s scratchpad
find index of the column (A e.g.) → This can be varying on each iteration

Column A can vary on its position in each iteration.
Iteration1: it may be the 1st column
iteration2: it may be the 12th column
iteration3: it may be the 100th column
and so on.

[prasath17 (Guru)]

@deepakkapri88 …I only see for each excel row activity but I don’t see anything for column based iteration…and that too dynamically.

I will research about it. In the meantime I am tagging @studioX for any ideas.

[deepakkapri88]

@prasath17
I have a solution in mind but that is adding more time in processing.

1. Use Copy Paste Range activity and copy Row1 from scratchpad
2. Insert a temp sheet in project notebook
3. Paste Row1 from 1st step in Column A of this temp sheet
4. Use Excel For Each Row to process row by row
   Check if the row[0] == A
   Fetch current row’s index
   use write cell to dump this value in scratchpad’s notebook

I have tested this and it works. But it puts delay with below 2 more actions.

1. Copying the whole row 1 (as I don’t know the end point)
2. Inserting another sheet called temp

@studioX any better approach?

[prasath17 (Guru)]

@deepakkapri88 - I have recently did almost similar one in studio, where i found the Cell address by rowindex and column index…Please check this solution

Fill excel cell color according to condition

@sereganator - Here you go…SetRangeColor_ColumnIndex.zip (44.1 KB) Run this project…you will see the cell value with the word ‘update’ updated with Background color Orange. If this helps, please mark my post as solution.

If I can convert this into studioX solution, that will most probably solve your case.

[flavius (Flavius Manea)]

There are no activities offering direct column based processing at this time, since we’ve considered this as a less common case. Could you please give us more details on what you are trying to achieve? cc: @AndrewHall

Possible solutions:

1. Copy paste range with transpose in a temporary sheet & for-each row, as already suggested:
   

   

2. If you have programming knowledge, an advanced shortcut would be:

Array.IndexOf(CType (Notes.Sheet(“Sheet1”).Range(“1:1”), DataTable).Rows(0).ItemArray, “myColumnName”)

Paste this into the Number dialog in the Save For Later activity: