Any simple way to search value in table?

[Aronax]

I need Zenno to search for a specific value in a specific column of a 100k rows excel table.

The only way to do this seems to be a a manual mode where you ask zenno to take each cell on the specific column and compare the value of that cell with the value you need to find. If the values are not the same, Zenno needs to move on to the next cell in that column and continue the loop. This is very time consuming and I need it do it faster.

I've found these C macros:
http://zennolab.com/discussion/threads/search-in-list-and-tables-via-c-macro.10733/

...but they are not very helpful in my situation because they only 'confirm' if the specified value has been found in the table (and they indeed work much much faster).

What I need after the specified value is found in the specified column of the excel table, is to take its row number and use it in order to take another element on that specific row.

Thanks for your thoughts.

 

[Tobbe]

You refer to this?

// take text for search from a variable
var textContains = project.Variables["tableSearchTextContains"].Value;
// get table for search
var sourceTable = project.Tables["SourceTable"];
// search in each row
lock(SyncObjects.TableSyncer)
{
    for(int i=0; i < sourceTable.RowCount; i++)
    {
        // read a row (array of cells)
        var cells = sourceTable.GetRow(i).ToArray();
        // loop through all cells
        for (int j=0; j < cells.Length; j++)
        {
            // check if cell contains specified text, if there are matches return "yes"
            if (cells[j].Contains(textContains))
                return "yes";
        }
    }
}
// if nothing found return "no"
return "no";

There's already a counter keeping track of the rows the script process in that loop. So instead of returning yes/no, you can tell it to return the current count of the variable instead.

// take text for search from a variable
var textContains = project.Variables["tableSearchTextContains"].Value;
// get table for search
var sourceTable = project.Tables["SourceTable"];
// search in each row
lock(SyncObjects.TableSyncer)
{
    for(int i=0; i < sourceTable.RowCount; i++)
    {
        // read a row (array of cells)
        var cells = sourceTable.GetRow(i).ToArray();
        // loop through all cells
        for (int j=0; j < cells.Length; j++)
        {
            // check if cell contains specified text
            if (cells[j].Contains(textContains))
                // If there's a match then return the current line #(zero based)
                return i;
        }
    }
}
// if nothing found return "no"
return "no";

Notice the " return i " (variable) instead of " return 'yes' " (a string/plain text). Wasn't able to highlight it within CODE tags

 

[Aronax]

Good idea!

 

[PHaRTnONu]

Maby im just a moron but i can not get this to work for me
i dont even get a return of no in my varible

var textContains = project.Variables["{-Variable.TEXTPROCESSING-}"].Value;
// get table for search
var sourceTable = project.Tables["Table1"];
// search in each row
lock(SyncObjects.TableSyncer)
{
    for(int i=0; i < sourceTable.RowCount; i++)
    {
        // read a row (array of cells)
        var cells = sourceTable.GetRow(i).ToArray();
        // loop through all cells
        for (int j=0; j < cells.Length; j++)
        {
            // check if cell contains specified text
            if (cells[j].Contains(textContains))
                // If there's a match then return the current line #(zero based)
                return i;
        }
    }
}
// if nothing found return "no"
return "no";

 

[Tobbe]

Spot the difference.

// take text for search from a variable
var textContains = project.Variables["tableSearchTextContains"].Value;

var textContains = project.Variables["{-Variable.TEXTPROCESSING-}"].Value;

idk if there's more to it, just what I noticed at a quick glance at it.

 

[PHaRTnONu]

/slap own face
sorry
working fine now just left var name didn't copy over the macro copy var. working great