Database Connection

The database connection filter connects to an OLE DB/ODBC database and retrieves data. It assumes that its input text is a sequence of SQL statements that can be run against the specified database. The data (if any) returned after executing each statement is formatted according to the output style. The filter assumes that the input text is a series of semi-colon (;) terminated SQL statements. Semi-colons that are inside single or double quotes are ignored.

The data output can then be manipulated by subsequent filters to

1. generate a database extract (which can be converted, modified or reformatted)
2. update data from the original table after performing name and address cleansing (requires a subsequent database connection filter)
3. insert data into a new table (requires a subsequent database connection filter)
4. Sample extracting from a database

Here's a quick example of how to use a database filter to extract data from a database:

Add a Special Menu\Database filter

Configure it to point to your database using the Build button e.g. to the Northwind database that comes with MS Access.

Set the database filter output options, e.g. for Delimited Output

In the Trial Run area, type

select * from customers

or use

select * from <your table name>

For an Excel spreadsheet (first setup an ODBC connection to point to the Excel file, then point the TextPipe database filter at the ODBC connection), use

SELECT * FROM [Sheet1$]

1. Click the [Trial Run] button, to see data extracted and populated in the Trial Run Output area. You can also set the Input Filter to use the Trial Run area by default, and then just click the [Go] button. To save this data to a file, set the Single File Output option of the Output Filter.
2. Database connection string
3. The database connection string gives TextPipe the name of the database and tells it how to connect to it. We recommend using the Build button unless you are familiar with setting the connection string manually.

Build

The build button allows the connection information to be constructed. A username and password can be specified in the database connection string if required.

Command Timeout

The command timeout (in seconds), allows you to set the time after which a query will be automatically failed. A setting of 30 will abort a query with an error message if it runs for longer than 30 seconds (the default). The command timeout setting is not implemented by all database drivers. The maximum value is 2147483647.

Output Style

The output style determines how the data will be output:

Output header row/DTD

When enabled, an XML document type definition (DTD) is output for XML files, or a header line with column titles is output for Delimited or Fixed width output formats. TextPipe forces all column heading to be unique, and to conform to XML naming conventions without any special characters. If TextPipe doesn't automatically generate the header you need to can easily add your own using an Add Header filter.