StudioX: Find the index of a particular column

I need to find the column index of a particular column from an excel file?
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.

@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.

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?

@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

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

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:

1 Like