DT command line program

Overview

DT is a Windows command line program that runs DTBuild.   There are two command formats :

dt  <dxd file name>

dt  <data source name>  <task table name>

In the first, 1-argument format the specified definition (DXD) file controls DT.   The definition must contain a list of input files, emails or URLs.   If multiple input lists are specified one will be chosen; see the Run topic for more information.   The transformation defined in the DXD file will be run on the chosen input list.

In the second, 2-argument format a task table in a database controls DT.   The first argument is the ODBC Data Source Name (DSN) of the database.   The second argument is the name of a task table in that database.   The remainder of this document describes how to configure and run DT under the control of a database task table.

DT database control

Under database control DT is driven by 3 interrelated database table types :

• Task tables
• Source tables
• SQL tables

The table names are user-defined, but each table type has a specific format.   The fields indicated in the table descriptions below must be present, but the user can add fields to the tables as needed.

The 2 command line arguments identify a task table within a database :

dt  <DSN>  <task table>

1. the ODBC Data Source Name (DSN),
2. the name of a task table in that database.

A database can contain more than one task table, but a DT run is controlled by a single task table.

For example, this command runs the census table scraper sample :

"C:\Program Files\DTUtilities\DTBuild\DT"  DTUtilities-Census-01  Tasks

"DTUtilities-Census-01" is the DSN, "Tasks" is the task table name.   See the Running DT topic for more information.

The task table in turn refers to source and SQL tables, as described below.

The task table

A single task table controls the process, instructing DT to :

• Scan URLs
• Execute SQL

DT scans the task table executing the user-defined actions, which can be :

The Scan and ExecSQL tasks can be run in any order.   For example, you might want to execute SQL before and after a URL scan.  

Example

The following task table :

1. runs the SQL in table PreSQL,
2. scans the URL list in table WebSources,
3. runs the SQL in table PostSQL.

The Exec field allows the user to switch tasks on and off without having to add / remove them from the table.

This design is flexible enough to allow multiple passes, where, for example, a list of URLs generated by one step is scanned by the next step.   Data from multi-level websites can be extracted using this feature.

Source tables

The URLs and associated data are specified in "source" database tables containing the following information :

DT scans Source tables from beginning to end.   Each URL is parsed per the associated DTBuild parser (.DXD file).

Example

The following Source table instructs DT to fetch and parse data from 3 URLs :

All three URLs are parsed with the Quotes01.DXD parser.   The Options, UserName and Password fields, which are not typically used, are not shown in this example.

SQL tables

The task table can trigger the execution of SQL (database Structured Query Language) statements in tables containing the following information :

SQL tables can be used to prepare data in the Source tables, for example, or to clear an output table prior to a scan.

Examples

The following SQL table instructs DT to execute an SQL statement to clear the Output table prior to a scan :

The following SQL table instructs DT to execute 2 SQL statements to prepare the ContributionsSources table for a URL scan :

In this case the ContributionsSources table has an extra field, "zip".   Fields can be added to the DT tables if necessary to facilitate SQL manipulations.

Once again, an Exec field allows the user to switch tasks on and off without having to add / remove them from the table.

The Command field must contain a valid SQL statement.

Running DT

DT is run from the command line with 1 or 2 arguments :

dt  <dxd file name>

dt  <DSN>  <task table>

In the first, single-argument format, DT runs under the control of the specified definition (DXD) file.

In the second, 2-argument format, DT runs under database control using :

1. the database identified by the ODBC Data Source Name (DSN),
2. the specified task table within that database.

Enter these command lines at the Windows command prompt, or other places where command lines are used, such as :

• Windows shortcuts
• batch files
• the Windows task scheduler

For example, to run the census table scraper sample at the command prompt, enter :

"C:\Program Files\DTUtilities\DTBuild\DT"  DTUtilities-Census-01  Tasks

DT is installed with DTBuild.

DSN configuration

DT uses ODBC, "Open Database Connectivity", to interact with user-defined databases.   Under ODBC databases are assigned labels, or Data Source Names (DSNs).   The DSNs for the sample databases are automatically configured during installation.

To use another database the user must configure a DSN for the database.   DSN configuration means, essentially, applying a system-wide label to an existing database.   Use the "Configure data sources" shortcut created during installation to start the ODBC Data Source Administrator, then :

• Select the User DSN tab
• Click the Add button
• Select the appropriate ODBC driver - for the sample Access database it will be "Microsoft Access Driver"
• Click Finish (though you're not quite finished ...)
• In the Data Source Name box enter the label, e.g. "CensusData"
• Enter a description for the database (optional)
• Click Select to locate the database in the file system, e.g. "C:\ProgramData\DTUtilities\DTRun"
• Select the database file/directory and click OK
• Keep clicking OK to get out of the ODBC Data Source Administrator

Visit dtutilities.com/database.html for more information about databases, DSNs and ODBC.

HTML parsing

Web pages are written using HTML, Hypertext Markup Language.   DT parsers are, therefore, HTML parsers, so configuring DT requires some knowledge of HTML.   For example, parsing an HTML table requires an understanding of the TABLE, TR, TH, and TD tags, among others.

In simple cases HTML tags can be ignored.   For more sophisticated parsing the user should be familiar with the basics of HTML:  many good books are available on the subject.

Most web browsers allow the user to view the HTML source of a web page (View / Source in Microsoft Internet Explorer 8, View / Page Source in Mozilla Firefox 3).   The source is displayed with tags and content in different colors, making it easier to see the structure of the web page.   DT supports a "noparse" option that allows saving the URL content to a local file.

DTBuild's design allows creation of reusable parsing components:  node groups.   For example, the following HTML parsing node groups are installed with DT Census table scraper sample :

Node groups greatly simplify top-level .DXD definitions.   See DTBuild Help for more information.

DT options

DT supports several options that can be specified on a per-URL basis, in the Source table "options" field :

¹ The "noparse" option enables you to write the fetched URL's content directly to a local file, without modification.   The output file path is specified in the sources table parser field.   This option provides the unmodified results of the HTTP Get command.   This can be very useful for parser development.

² The maximum number of HTTP redirects defaults to 3.

³ DT uses a Microsoft Foundation Class (MFC) function called "AfxParseURLEx" to parse the URL prior to fetching the data.   Visit http://msdn.microsoft.com for more information on AfxParseURLEx and its options.

Example

The 1st example sets the number of HTTP redirects to zero.

The 2nd example writes the fetched URL's content, unmodified, to file c:\urldump\sample.html.

The 3rd example illustrates the combination of two options, separated by a blank.

Scanning web page sequences with NextURL

DT can scan a sequence of web pages if those web pages contain links to "next" pages.   The DTBuild parser must define a string variable called "NextURL" and set it appropriately.   DT will continue fetching pages as long as "NextURL" contains a valid URL.   The NextURL feature allows an unlimited sequence of web pages to be fetched for a single URL entry in the Sources table.

The parser will typically clear NextURL at first, then set it only if a "next" link is recognized.   This is a powerful feature, but care must be taken to avoid infinite loops!

DT requirements

• Microsoft Windows 32-bit
• 10 megabytes of free disk space
• a database management system (DBMS), if running under database control or outputting to a database
• an Internet connection, if reading input from a URL list

DT is installed with the DTBuild installation package.

DT samples

DT sample databases are available at the DTBuild website :

• Census table scraper

The sample installers automatically configure their ODBC DSNs.   The installers run the DSNConfig program, which use the *-DSN.txt files to configure the DSNs.   The sample uninstallers remove the DSNs created at installation.

DT sample :  Census table scraper

This DT sample extracts data from an HTML table at http://factfinder.census.gov.

It uses 4 database tables :

1. Tasks
2. Sources
3. Output
4. SQL1

Keep in mind that the word "table" is used two ways here :

• database table
• HTML table

Two tasks are configured in the Tasks table :

The SQL1 database table contains a single SQL statement :

delete from Output

This clears the Output table prior to the scan.

The Sources database table contains the URL / parser list :

The Census-01.DXD parser starts by looking for a "TableLocator" (specified as an entry in a DTBuild string set), in order to skip over page content preceding the HTML table to be scraped.   The entries in the TableLocator string set contain keywords that distinguish the desired table from other tables that may precede it in the web page being parsed :

Sample output

Questions, comments?   Contact DTBuild support.