Find in Excel is a multifunctional utility used to search for data from a spreadsheet. It will retrieve a list of specified columns based on an entered query.

Flow Location

C:\VIPTDM\DataFindExcel\FindInExcel.vip

Submit through Executor Form

Yes

Use as part of models and TDA

Yes

Run Standalone

Yes

Operation

Parameter

Description

Default

Internal

parRC

Y

parReturn

Y

parReturnData

Y

parSheet

Choose the tab of your spreadsheet in which you want to search and copy its name into the box.

Y

parColumns

Choose the column(s) you want to output and copy them into the box. If you choose to use more than one column, separate them with a comma without including a space.

Y

parMaxRows

Set this value to the maximum number of rows you wish to retrieve.

Y

parFilterQueries

Insert a query for what you would like to find in the Excel. For example [Column1] LIKE ‘{par1}’ and [Column2] LIKE ‘{par2}’ and [Column3] LIKE ‘{par3}’ will search for rows where value Column1 = par1, and value Column2 = par2, and value Column3 = par3 from your spreadsheet.

Y

par1 – parN

You will need to set the values of the parameters you are using in your Query. So, for example if you want to search for outputs that have value “Test” in Column1, then you would set par1 = “Test”.

N

parAPIKey

APIKey

Y

parHostURL

Y

parMachine

Y

parJobEngineWorkFolder

Job Engine Work Folder

Y

parRemoteExcel

See figure below

Y

parExcel

Paste the spreadsheet location into this box.

N

When  using Test Data Criteria of Excel, the Excel file will be uploaded into modeller.  The parameter parRemoteExcel will point to the location key, a GUID automatically.

Output Data

The output will display the matching rows based off your query and maximum number required.