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.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. Put the format used for date fields on the fifth line; use "M" as a placeholder for month, "D" for day, and "Y" for year. Specify the correct separator (usually "/" or "-") between the placeholders. For example, use "M/D/Y" for dates such as 10/26/2019 or Y-M-D for dates such as 2019-10-26. If the field contains time as well as date, specify that using "h" for hours, "m" for minutes, "s" for seconds, and "p" for the AM/PM symbol (note that the case of the placeholder is important, so don't use "H" for hour). For example, for dates such as 10/26/2019 1:15:27 PM, use "M/D/Y h:m:s p".

• Create fields in each table in the order they appear in text file with the correct data type and size.

• 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