Diverse data sources

You can quickly open a data file using the Open icon  on the toolbar; or you can click File -> Special Open to import desired data as needed. The following explains how to use the Special Open functionality through examples.

txt/csv

orders2020.txt: To import data file orders2020.txt storing orders information, you follow these directions:

Click File -> Special Open, select orders2020.txt, and enter Text File window (as shown below):

Result name: The name of to-be-displayed result set after the data file is opened.

Charset: The data file’s character set type; default is the data file’s original character set.

Delimiter: The separator used to keep fields apart; the default delimiter is TAB.

Segment count: The number of segments into which you want to divide the current data file.

Segment NO.: The sequence number of segment to be retrieved.

Import fields: Under which you can select fields of the data file to import, while specifying their types and formats.

Import the first row as field names: Use the first row as field names at import. Use _1，_2,… as field names when the option isn’t selected. The option is by default checked.

Use double quotation marks as escape character: Use the standard Excel escaping rule to transform the two pairs of double quotation marks to one pair while leaving other characters alone.

Keep leading and trailing spaces: Keep white spaces at both sides of each data item; by default, they will be automatically deleted.

Remove quotation marks: Delete quotation marks from both sides of each data item, including items in headers, but leave quotation marks within data item alone.

Retrieve data in multiple threads: Use multithreading to speed up data retrieval.

Report error when column count does not match that at row1: Error will be reported when a row whose number of columns is not the same as that in the first row and the row will be discarded.

Click “OK” to confirm the import of the text file.

xls/xlsx

For example, open the Excel file orders2020.xlsx, where the first row contains column headers and rows after it are detail data, as shown below:

Click File -> Special Open to select and open orders2020.xlsx, as the following interface shows:

Result name: The name of to-be-displayed result set after the data file is opened.

Begin row/End row: For setting the beginning row from which the Excel file retrieval begins and the ending row at which the retrieval stops.

Password: The password for opening the Excel file.

Sheet: Name of the Excel sheet you want to open.

Refresh: When opening a password-protected Excel file, you need to click the Refresh button to display the sheet name after entering the password correctly.

Import the first row as field names: Use the first row as field names at import. Use _1，_2,… as field names when the option isn’t selected. By default, import all fields when this option isn’t checked.

Ignore empty rows: Remove empty rows at both sides of the retrieved Excel content.

Ignore white spaces at both sides: Remove white spaces at both sides of the retrieved string.

After the above configurations are done, click “OK” to confirm the import of the Excel file.

Edit Bin File

You can use Data File Tool to edit an Excel file, a text file and even a bin file (.btx). Find more about in Tutorial - Bin Files.

Let’s look at how to open a bin file using dept.btx.

Click File - > Special Open and select bin file dept.btx:

 

Result name: The name of to-be-displayed result set after the data file is opened.

Segment Count: The number of segments into which you want to partition the current bin file.

Segment NO.: The sequence number of segment to be retrieved.

Import fields: Under which you can select fields of the bin file to import.

After the above configurations are done, click OK to finish the import of bin file.

Database Table

As the name shows, data in a database table comes from the database. You do not need to write SQL statement to retrieve data from this type of data set. You just follow the wizard to select a table and a SQL statement will be automatically generalized for displaying the desired data. The data set is catered to users who are not familiar with SQL.

In our database table import example, we import the EMPLOYEE table in the esProc built-in demo database.

Start the demo database, click Tool -> Datasource connection to connect to the demo data source:

Make sure that the database driver package is available for being loaded by the esProc application before defining or connecting to a data source. The built-in database drivers in the installation package are located in [installation directory]\common\jdbc under the installation directory. Users can choose the desired driver as needed. The built-in database esProc has for its in-house demo data source is HSQL, which uses driver hsqldb-2.7.3-jdk8.jar.

This example uses the built-in demo data source, so we can skip the data source definition.

Click File -> Open DB Table to select a desired table:

Click OK to open the database table.