Stonefield Query can query against text files as if they were tables in a relational database. Each text file is represented in the Stonefield Query data dictionary as a separate table.

Although there's an ODBC driver for text files (Microsoft Text Driver), Stonefield Query has its own built-in mechanism for querying text files that works better. Specifically, the ODBC driver has issues with SQL statements. For example, you can't use "SELECT Log.txt.Field FROM Log.txt"; you need to use "SELECT *" instead, but Stonefield Query specifically requests only those fields needed for a report in a query.

To support this, you need to do several tasks:

  • Create an empty database in the Stonefield Query data dictionary. Select Add Database from the Objects menu, choose Empty Database, specify a name, and click OK.

  • Create tables in the database. Choose Create Table from the Objects menu, specify a name for the table, and put the name of the text file (without a path) in the first line of Custom Properties for the table. Leave the second line blank or enter "delimited" if the text file contains comma-separated values (a CSV file) or "sdf" if the file contains fixed length data. Put the number of header lines in the file on the third line and the number of footer lines on the fourth line; that many header and footer lines are automatically removed from the result set retrieved for a report.

  • Create fields in each table in the order they appear in text file with the correct data type and size. For Date fields, set the first line of Custom Properties to the date format: MYD, DMY, MDY, etc. Put the separator, if any, on the second line (for example, "-" if the dash is used as the separator); omit this if the date is in a format, such as MM/DD/YYYY or MM/DD/YYYY HH:MM that Stonefield Query can parse directly. Put the width of the field in the text file on the 3rd line (for example, 10 for "YYYY-MM-DD").

  • Create a GetDataSources script for the database. Click the Create "get data sources" script link in the properties page for the database.

  • In the code for the GetDataSources script for the database, specify "Text" as the first parameter to the DataSources AddItem method and set the Database property of the data source object to the path for the text files. Here's an example of a GetDataSources script:

Visual FoxPro

lparameters toApplication as SQApplication, toDatabase as Database
loDataSource = toDatabase.DataSources.AddItem('Text', 'My Sample Datasource')
loDataSource.Database = 'C:\MyData'
return .T.

VBScript

function Main(Application, Database)
dim DataSource
set DataSource = Database.DataSources.AddItem("Text", _
  "My Sample Datasource")
DataSource.Database = "C:\MyData"
Main = True
end func

JavaScript

function Main(SQApplication, Database) {
  var DataSource;
  DataSource = Database.DataSources.AddItem('Text', 'My Sample Datasource');
  DataSource.Database = 'C:\\MyData';
  return true;
}

C#

public static bool mydatabase_GetDataSources(SFQApplication sfqApplication, 
  Database database)
{
  DataSource dataSource;
  dataSource = database.DataSources.AddItem("Text", "My Sample Datasource");
  dataSource.Database = "C:\\MyData";
  return true;
}

VB.NET

public shared function mydatabase_GetDataSources(sfqApplication as SFQApplication, 
  database as Database) as Boolean
  Dim dataSource as DataSource
  dataSource = database.DataSources.AddItem("Text", "My Sample Datasource")
  dataSource.Database = "C:\MyData"
  Return true
End Function