Creating a data extraction file

Read(132) Label: directions, dataextraction,

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 ininstallation 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 databasefrom 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 Connectbutton 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 clickOKto 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 OKand 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 clickOKto 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:

ClickOKto 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:

ClickOK 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: