Here are the directions of creating a simple data extraction file:
Let’s look at an example. ATTENDANCE table and EMPLOYEE table are stored in demo database. We want to perform a SQL join of them, select certain fields of the joining result and output them to a target file.
Step 1: Start the data file tool designer.
After you install esProc, start the data file tool through the desktop shortcut or from the Start menu.
Step 2: Define and connect to a data source
First users need to make sure that the database driver jars are in place. esProc comes with drivers of most of the commonly-used databases. They are stored in【installation directory】\common\jdbc. Drivers of any other database users need to connect to can be put into the directory. esProc has the built-in database HSQL to feed its demo data source; the driver jar is hsqldb.jar.
Define a data source:
The data file tool supports both JDBC-type and ODBC-type databases. Click Tools->Data Source to add a data source by configuring driver, URL, user name, password and other information.
Note: Detailed configuration instructions can be found in Database Configuration.
Connect to a data source:
(1) Start the database
Make sure that the database to be connected is started. Then you can connect to a defined database on the Datasource window.
Now let’s take esProc built-in demo data source to explain how to connect to a data source.
Click 【esProc】-【Start DEMO database】from the Start menu to launch the demo sample database.
It shows that the HSQL database to which the demo data source connects is in started status.
(2) Open Datasource window
Click Tools -> Datasource
To get the following data source window where the default demo data source is displayed:
(3) Connect to the data source
Select the demo data source and click the 【Connect】button on the right. The data source turns pink if it is successfully connected and the status is changed from Not connected to Connected:
Since demo data source comes with the system, users can’t alter its connection parameters.
Step 3: Create a new data extraction file
Click File -> New to generate a new data extraction file with the extension .ept.
A data extraction file consists of two parts – the data sources that store the source data and the views that store the operations performed over the source data and configurations of the result view.
Step 4: Create a new data source
Click the Add button on the data source window to pop up the data source type window:
The data file tool supports four types of data source. They are Text file, Excel file, database table and SQL. Here we use the SQL data source to do the creation. Read Data Source Managment -> Add to learn more about the data source types.
Select SQL data source and click【OK】to jump onto the SQL data source configuration window, as shown below:
Choose demo in the drop-down list under Datasource and click the button to get the SQL editor window. Double-click a desired table name ubder the Available tables to select it. Here ATTENDANCE is selected:
Click 【OK】and a SQL statement is automatically generated and then switch back to the SQL data source configuration window. On the window you click the Refresh fields button to load the fields refrenced by the SQL statement (They are the fields in ATTENDANCE table here), select the right table name and set the primay key, as shown below:
Then we create another SQL data source:
Step 5: Define a view
A view is a virtual table output from one or more data sources. It stores the definition of the relationship rather than the relevant data.
To define a relationship between source ATTENDANCE and source EMPLOYEE, we select the View button on the namesake window and click the add button to pop up the following editor:
Edit the view name and click【OK】to complete the editing.
Drag the fields under the SQL data source EMPLOYEE to the right:
Then those under ATTENDANCE to the right to configure the joining fields on the pop-up relationship editor:
Click【OK】to complete the creating a view.
Note: Read View -> Operations to learn more about defining views.
Step 6: Configure an exported view
An exported view stores the selected fields and the expressions after data sources are joined. You can output both the selected fields and the expression results to a composite table file (CTX), a Raqsoft bin file (BTX), a text file (TXT), a CSV file, and Excel file (XLSX) or a database table.
Now we define an exported view based on a view. Select a View1 under the View node and click the Add button :
To get the following configuration window where we can configure an exported view:
Click【OK】 and you’ll have a tab where you can edit selected columns and their aliases and the computed columns:
Select desired columns and set the alias of EID as id:
Now a data extraction file is created. Click File -> Save to save the file as 1.2.ept.
You can preview a selected exported view to ensure data accuracy before outputting data from it.
Choose exported view Export1 and click the preview button :
To pop up the preview window:
Then you can output data from the exported view to a target text file, say data.txt.
Click the Run button on the tool bar to output data. The following message will appear after data is successfully output: