tssql is a part of the txt-sushi toolset. The tssql utility allows you to use a subset of the SQL SELECT grammar on your flat files. tssql treats the first row of a CSV file as a table header and the other rows are treated as table data. Here is a summary of what's supported in the current version:

If you type

tssql -help
you will see the following usage documentation:
Usage: tssql [-help  ...] [-external-sort] [-table table_name CSV_file_name]
I will only explain the -external-sort option since -table option is demonstrated in the example below. You only need to consider using the external sort option if you have a very large table that you are performing an "[INNER] JOIN", "GROUP BY" or "ORDER BY" operation. All of these operation require a row sort which can cause tssql to run out of memory if the table is very big. If this occurs you can use the external sort option to tell tssql to sort the rows on disk which is much slower but also requires less memory.


Here are a couple of selected example scripts. To see all of the examples you can go to the examples directory and even the test directory in the darcs repository. The first example comes from test1.2.bash:


# Example adapted from http://en.wikipedia.org/wiki/Join_(SQL)#Self-join

tssql 'SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM `employees.csv` AS F JOIN `employees.csv` AS S
ON F.Country = S.Country
WHERE F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID' | csvtopretty -
If you don't use any command line options then tssql's default behavior is to assume that table names will match the file names of the comma-separated files that they refer to. So for this example the employees.csv file needs to be available in the working directory. You can also see from this example that tssql accepts standard identifiers like F.EmployeeID without any special syntax, but if you have an identifier that includes spaces or special characters you can quote it with backticks as in `employees.csv`. Here is the output you get from this example:
EmployeeID|LastName|EmployeeID|LastName |Country
123       |Rafferty|124       |Jones    |Australia
123       |Rafferty|145       |Steinberg|Australia
124       |Jones   |145       |Steinberg|Australia
305       |Smith   |306       |Jasper   |United Kingdom
Here is one more example to show how you can name tables using the -table option:

# replicating JOIN from
# http://www.itl.nist.gov/div897/ctg/dm/sql_examples.htm

tssql -table STATION station.csv -table STATS stats.csv \
WHERE MONTH = 7 ORDER BY TEMP_F' | csvtopretty -
And the output you should get:
47   |Caribou|65.8
40   |Denver |74.8
33   |Phoenix|91.7