QueryExcel

The QueryExcel component enables the user to extract any Data from an excel file using an SQL command.

Screenshot

Ports

The QueryExcel component exposes the Control In, Control Out, Data In and Data Out ports by default.

Port Description
ControlIn Must be connected to the Control Out port of one or more components.
ControlOut Can be connected to the Control In port of any other component.
Data In The QueryExcel component exposes the following Data In ports by default:
Filepath: specifies the location of the Excel file.
CommandText: specifies the Sql query for extract the data.
Data Out Return the Data count(Int32) from the Excel file.
Data Out Returns table from excel based on Sql query.

Properties

To edit the properties of the QueryExcel component, in the Properties window, change the required property. You can edit the following properties..

Property Description
Search Search for the respective property.
Delay After Execution Specifies the wait time (in seconds) after the action is executed.
Delay Before Execution Specifies the wait time (in seconds) before the action is executed.

Example

Let us consider an example

Screenshot

To read the Excel Sheet Data using Sql Query:

  1. In the Toolbox, expand Utilities and then expand Data Reader.
  2. Drag the QueryExcel component and drop it on the Design surface.
  3. To specify the file location, double-click the Filepath and enter the required file path.
  4. To override the existing data source, right-click Filepath.
  5. Select Override and change the data source.
  6. Double-click on Command text box and write the SQL query to extract a single row data. [The command entered for this example is: [Select * from [Test$] where Name='John']]
  7. User can enter the command by Right clicking on the CommandText input box and clicking Override.

    Note

    To learn more about Overriding the data source of the data port, refer the Override section.

  8. In the Toolbox, expand Utilities and then expand Data Writer.

  9. Drag the WriteToExcel component and drop it on the Design surface.
  10. To specify the file location, double-click Filepath and enter the location of the file.

    Note

    To learn more about overriding the data source of the data port, refer the Override section.

  11. Connect the control ports and the data ports in the activity.

  12. In the toolbar, click Run.
    Intellibot extract the data based on command text and transfer the data to specified destination.