Quick Start with TMQL (TextMaster Query Language) TMQL™ is a SQL like query language which combines the simplicity and power of SQL with the unique characteristics of a text file and flexibility of regular expressions into one powerful language designed to manipulate fixed or delimited files. For a query to be executed, Text Master first requires a connection to be created for a specific file and then a query to be built. More »

Start with SELECT statement using sample data

1. Open TextMaster Data Editor (if you do not have TM Data Editor installed on your computer - download now)
2. Click or select File >> Query from main menu
3. Click Help>>Load Sample Data
4. Select C:\TMSampleData folder .

Screen should be similar to this one:

5. Copy any of the queries from the TMQL Sample Query list below, execute it and observe results. I.e. First query (select * from C:\TMSampleData\SampleData.csv will look like this:

 

Note: "No." filed is auto generated and is not part of the c:\TMSampleData\SampleData.csv text file

top

TMQL Sample Query List:

• SELECT * from C:\TMSampleData\SampleData.tmc
  Will retrieve all lines from C:\TMSampleData\SampleData.csv file and each line will have all fields displayed.

• SELECT Top(5) * from C:\TMSampleData\SampleData.tmc
  Will retrieve the first 5 lines from C:\TMSampleData\SampleData.csv file and each line will have all fields displayed.

• SELECT Offset(2) * from C:\TMSampleData\SampleData.tmc
  Will retrieve all lines from C:\TMSampleData\SampleData.csv, starting from second line. All lines from the file will have all fields displayed.

• SELECT top(5) offset(2) Name,Phone,Company from C:\TMSampleData\SampleData.tmc
  Will retrieve the first 5 rows (lines) starting from the second line (row). Result will contain only Name, Phone and Company fields (columns).

• SELECT * from C:\TMSampleData\SampleData.tmc where Country == "Canada"
  Will retrieve all lines from C:\TMSampleData\SampleData.csv where the Country field is equal to “Canada” (values like CANADA, Canada etc. will be omitted).

• SELECT * from C:\TMSampleData\SampleData.tmc where Country.ToUpper() == "CANADA"
  Will retrieve all lines from C:\TMSampleData\SampleData.csv where the Country field is equal to “Canada” regardless of the letter case (Canada,CANADA, Canada etc - all values will be retrieved)

• SELECT * from C:\TMSampleData\SampleData.tmc where Country.ToUpper() ! "CANADA"
  Will retrieve all lines from C:\TMSampleData\SampleData.csv where the Country field is not equal to “Canada” regardless of the letter case (Canada,CANADA, Canada etc).

• SELECT * from C:\TMSampleData\SampleData.tmc where Name.Contains("1") 
  Will retrieve all lines from C:\TMSampleData\SampleData.csv where Name contains number “1”

• SELECT * from C:\TMSampleData\SampleData.tmc where Name.StartsWith("John1")  && Country == "Canada"
  Will retrieve all lines from C:\TMSampleData\SampleData.csv where the Country field is equal to “Canada” and Name starts with “John1”. (“John” or “John2” will not be retrieved)

• SELECT * from C:\TMSampleData\SampleData.tmc where Name.StartsWith("John1")  || Name.StartsWith("John2")
  Will retrieve all lines from C:\TMSampleData\SampleData.csv where Name starts with “John2“ or “John1”. (“John” will not be retrieved).

• SELECT Name.Replace("1","5")  from C:\TMSampleData\SampleData.tmc where Name.Contains("1")
  Will retrieve lines were Name field contains “1” and replace only letter “1” with “5”. Only one field per line will be retrieved.

• select PostalCode.Substring(0,3),Email from C:\TMSampleData\SampleData.tmc where PostalCode.Trim() != "
  Will retrieve two fields per line. The first field will be a substring (first three characters ) of the Postal Code and the second field will contain the e-mail. Only lines with non empty Postal Codes will be retrieved.

 NOTES:

If query was without 'where' clause query execution will report an error for the lines with empty PostalCode;

The first column name is labeled as FIELD0.  Use “as” close to change field name for the fields where function is used. To assign PC as column (field) name to the first field run the following query:
SELECT PostalCode.Substring(0,3) as PC,Email from C:\TMSampleData\SampleData.tmc where PostalCode.Trim() != ""

Next Page   TMQL String Functions