topic Re: How to find row and column (cell) that matches a value in Discussions

How to find row and column (cell) that matches a value

SamKing — Sat, 10 Jun 2023 23:14:31 GMT

I have tables that are generated and the format is different from time to time and it breaks my script. The tables are massive and so the way i handle my loops is that i import the first 100 rows and 5 columns, for speed. Then i search a particular column for a keyword to help me know where which row the real data starts. I close the file then reopen with that variable identifying the column header and data start.

However, as mentioned above about 1% of the files are dorked up and have extra columns at the beginning and sometimes a few hundred random rows so it trips up my script. I was thinking that if I can search the whole abreviated table for my keyword then i can get around it but i'm unsure how to search multiple columns at once, especially when the column names change.

Here is the method that I currently use to identify my row, looking in a particular column. How do i open this up to search columns 1-10 or even the whole table?

//nr1 becomes the column headers and nr2 becomes the first row of data when i reopen the full table.
nr1 = dt << get rows where( :Col1 == "customparameter" );
nr2 = nr1 + 1;

Re: How to find row and column (cell) that matches a value

txnelson — Tue, 09 Jun 2020 00:36:24 GMT

You can refer to columns in a data table by number

dt << select where( column(dt, 3 ) == "Top" );

or you can easily retrieve the column names in a data table, and refer to those names

colNames = dt << get column names;
dt << select where( column( dt, colNames[3] == "Top" );

so once you have extracted you sample, you can find the names of the columns or just search by column number.

Re: How to find row and column (cell) that matches a value

SamKing — Tue, 09 Jun 2020 14:11:24 GMT

@txnelson, thanks for the response. The column names are not indicative of the content, they are really screwy tables. That is why i have to locate where the actual data starts, close the table then re-import the full table with the correct row identifying the columns.

Your comment though made me realize that i can grab all the column names then do a loop through each column and if it finds the matching cell value then assign the row value to my variable and exit the loop.