IDtExcelProvider.Query

Fore syntax

Query: String;

Fore.NET syntax

Query: String;

Description

The Query property determines Excel sheets query. Imported data is selected by means of the query.

Comments

As a value of the property the following values can be selected:

SQL query to be used to select data. Example: Select * From [Sheet1$].
Sheet names. Example: Sheet1.

NOTE. The sheet name must not contain the characters "[","]" and "*".

Fore example

Executing the example requires an ETL task with the Etl identifier in repository and Excel file named Data.xls in the disk C root directory.

Sub UserProc;
Var
    MB: IMetabase;
    MObj: IMetabaseObject;
    EtlTask: IEtlTask;
    EtlProvider: IEtlPlainDataProvider;
    ExcelProvider: IDtExcelProvider;
    WxProvider: IWxRectangle;
    WxETLProvider: IWxETLObject;
Begin
    MB := MetabaseClass.Active;
    //Searching ETL task. Repository object with the ETL identifier
    MObj := MB.ItemById("Etl").Edit;
    EtlTask := MObj As IEtlTask;
    //Start of provider creating
    //Creating the object "Import from Excel"
    EtlProvider := EtlTask.Create(EtlObjectType.PlainDataExcelProvider) As IEtlPlainDataProvider;
    EtlProvider := EtlProvider.Edit;
    EtlProvider.Id := "Excel_Provider";
    EtlProvider.Name := "Import from Excel";
    EtlProvider.Description := "Import from Excel";
    //Configuring data provider
    ExcelProvider := EtlProvider.Provider As IDtExcelProvider;
    ExcelProvider.DriverVersion := "Excel 8.0";
    ExcelProvider.File := "c:\Data.xls";
    ExcelProvider.HasHeader := True;
    ExcelProvider.ImexMode := DtExcelImexMode.Import;
    ExcelProvider.Query := "Select * From [Sheet1$]";
    EtlProvider.FillDefault;
    //Saving provider
    EtlProvider.Save;
    //End of provider creating
    //Start of visual object creating
    WxProvider := EtlTask.Workspace.CreateRectangle;
    WxETLProvider := New WxETLObject.Create;
    WxETLProvider.ETLObject := EtlProvider;
    WxProvider.Style.TextPosition := WxTextPosition.Bottom;
    WxProvider.Style.PictureMarginTop := -10;
    WxProvider.PinPosition := New GxPointF.Create(50, 50);
    WxProvider.Extension := WxETLProvider As IWxShapeExtension;
    //End of visual object creating
    //Saving ETL task
    MObj.Save;
End Sub UserProc;

After executing the example the Import From Excel object is created in the ETL task . This source imports all data from the Data.xls Excel file. Sheet name is Sheet1. The Excel 8.0 drivers are used for connection. Field names are imported from the first file row. Fields that contain data of different types are imported entirely.

Fore.NET Example

Executing the example requires an ETL task with the Etl identifier in repository and Excel file named Data.xls in the disk C root directory.

Imports Prognoz.Platform.Interop.Dt;
Imports Prognoz.Platform.Interop.Etl;
Imports Prognoz.Platform.Interop.Metabase;
Imports Prognoz.Platform.Interop.Andy;
Imports Prognoz.Platform.Interop.Drawing;

Public Sub Main(Params: StartParams);
Var
    MB: IMetabase;
    MObj: IMetabaseObject;
    EtlTask: IEtlTask;
    EtlProvider: IEtlPlainDataProvider;
    ExcelProvider: IDtExcelProvider;
    WxProvider: IWxRectangle;
    WxETLProvider: WxETLObject = New WxETLObjectClass();
    PinPos: GxPointF = New GxPointFClass();
Begin
    MB := Params.Metabase;
    //Search ETL task. Repository object with the ETL identifier
    MObj := MB.ItemById["ETL"].Edit();
    EtlTask := MObj As IEtlTask;
    //Start of provider creating
    //Creating the object "Import from Excel"
    EtlProvider := EtlTask.Create(EtlObjectType.eotPlainDataExcelProvider) As IEtlPlainDataProvider;
    EtlProvider := EtlProvider.Edit();
    EtlProvider.Id := "Excel_Provider1";
    EtlProvider.Name := "Import from Excel";
    EtlProvider.Description := "Import from Excel";
    //Data source setting
    ExcelProvider := EtlProvider.Provider As IDtExcelProvider;
    ExcelProvider.DriverVersion := "Excel 8.0";
    ExcelProvider.File := "c:\Data.xls";
    ExcelProvider.HasHeader := True;
    ExcelProvider.ImexMode := DtExcelImexMode.eimImport;
    ExcelProvider.Query := "Select * From [Sheet1$]";
    EtlProvider.FillDefault();
    //Save source
    EtlProvider.Save();
    //End of source creating
    //Start of visual object creating
    WxProvider := EtlTask.Workspace.CreateRectangle();
    WxETLProvider.EtlObject := EtlProvider;
    WxProvider.Style.TextPosition := WxTextPosition.wtpBottom;
    WxProvider.Style.PictureMarginTop := -10;
    PinPos.Create(50, 50);
    WxProvider.Extension := WxETLProvider As IWxShapeExtension;
    //End of visual object creating
    //Saving ETL task
    MObj.Save();
End Sub;