271473_en-US

liliconcom

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

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.<BR />nr1 = dt << get rows where( :Col1 == "customparameter" );
nr2 = nr1 + 1;

Data AccessMass CustomizationRe: How to find row and column (cell) that matches a value

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

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

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.