The ’textql’ command allows users to execute SQL queries against structured text files like CSV or TSV. It provides a convenient way to query and manipulate data in these formats using the familiar SQL syntax.

Use case 1: Print lines in a CSV file that match a SQL query

Code:

textql -sql "SELECT * FROM filename" path/to/filename.csv

Motivation: Sometimes, we have a large CSV file and need to filter and display only specific rows that meet certain conditions. Rather than manually parsing the file, we can use the ’textql’ command to easily write SQL queries to retrieve the desired lines.

Explanation:

• -sql "SELECT * FROM filename": Specifies the SQL query to be executed. In this case, we select all columns (’*’) from the ‘filename’ table (which corresponds to the given CSV file).

Example Output:

• If the CSV file contains data like:

name,age
John,25
Jane,30

• Running the command textql -sql "SELECT * FROM filename" path/to/filename.csv will display:

name,age
John,25
Jane,30

Use case 2: Query a TSV file

Code:

textql -dlm=tab -sql "SELECT * FROM filename" path/to/filename.tsv

Motivation: TSV (Tab-Separated Values) files are commonly used for storing structured data. In this example, we want to query a TSV file and retrieve the required data using SQL.

Explanation:

• -dlm=tab: Specifies that the file to be queried is a TSV file and uses tabs as the delimiter between columns.
• -sql "SELECT * FROM filename": The SQL query to select all columns from the ‘filename’ table (corresponding to the TSV file).

Example Output:

• If the TSV file contains data like:

name	age
John	25
Jane	30

• Running the command textql -dlm=tab -sql "SELECT * FROM filename" path/to/filename.tsv will display:

name	age
John	25
Jane	30

Use case 3: Query a file with a header row

Code:

textql -dlm=delimiter -header -sql "SELECT * FROM filename" path/to/filename.csv

Motivation: Many structured text files have a header row that contains column names. By specifying the -header flag, ’textql’ can correctly handle files with header rows when executing SQL queries.

Explanation:

• -dlm=delimiter: Specifies the delimiter used in the file, which can be any character or string.
• -header: Informs ’textql’ that the file contains a header row, allowing it to handle the file correctly.
• -sql "SELECT * FROM filename": The SQL query to select all columns from the ‘filename’ table (corresponding to the given file).

Example Output:

• If the CSV file contains data like:

name,age
John,25
Jane,30

• Running the command textql -dlm=delimiter -header -sql "SELECT * FROM filename" path/to/filename.csv will display:

name,age
John,25
Jane,30

Use case 4: Read data from stdin

Code:

cat path/to/file | textql -sql "SELECT * FROM stdin"

Motivation: Sometimes, the data we want to query is not stored in a file but rather comes from stdin or another command. By using the ‘|’ operator, we can pass the output of a command as input to ’textql’ for SQL processing.

Explanation:

• cat path/to/file: Reads the content of the file designated by ‘path/to/file’.
• |: The pipe operator, which redirects the output of the ‘cat’ command as input for the ’textql’ command.
• -sql "SELECT * FROM stdin": The SQL query to select all columns from the ‘stdin’ table, which represents the input passed through the pipe.

Example Output:

• Suppose the file at ‘path/to/file’ contains data like:

name,age
John,25
Jane,30

• Running the command cat path/to/file | textql -sql "SELECT * FROM stdin" will display:

name,age
John,25
Jane,30

Use case 5: Join two files on a specified common column

Code:

textql -header -sql "SELECT * FROM file1 JOIN file2 ON file1.c1 = file2.c1 LIMIT 10" -output-header path/to/file1.csv path/to/file2.csv

Motivation: In some cases, we need to combine data from multiple files based on a common column. The ’textql’ command allows us to perform SQL joins on these files, making it easy to retrieve relevant information.

Explanation:

• -header: Specifies that the input files contain header rows, enabling ’textql’ to handle the files correctly during the joining process.
• -sql "SELECT * FROM file1 JOIN file2 ON file1.c1 = file2.c1 LIMIT 10": The SQL query to perform a join operation between the ‘file1’ and ‘file2’ tables (corresponding to the given CSV files) based on the common column ‘c1’. The ‘LIMIT 10’ clause restricts the output to only ten rows.
• -output-header: Indicates that the output table should include a header row.

Example Output:

• Suppose the files ‘path/to/file1.csv’ and ‘path/to/file2.csv’ contain data like: file1.csv:

c1,c2
1,A
2,B

file2.csv:

c1,c3
2,C
3,D

• Running the command textql -header -sql "SELECT * FROM file1 JOIN file2 ON file1.c1 = file2.c1 LIMIT 10" -output-header path/to/file1.csv path/to/file2.csv will display:

c1,c2,c1,c3
2,B,2,C

Use case 6: Format output using an output delimiter with an output header line

Code:

textql -output-dlm=delimiter -output-header -sql "SELECT column AS alias FROM filename" path/to/filename.csv

Motivation: Sometimes, we want to customize the output format when retrieving data using SQL queries. The ’textql’ command offers options to specify the output delimiter and include a header line, allowing us to control the formatting of the data.

Explanation:

• -output-dlm=delimiter: Specifies the delimiter used in the output. This delimiter can be any character or string.
• -output-header: Instructs ’textql’ to include a header line in the output.
• -sql "SELECT column AS alias FROM filename": The SQL query to select a specific column and assign it an alias (‘column AS alias’) from the ‘filename’ table.

Example Output:

• If the CSV file contains data like:

name,age
John,25
Jane,30

• Running the command textql -output-dlm=delimiter -output-header -sql "SELECT name AS Name FROM filename" path/to/filename.csv will display:

Name
John
Jane

Conclusion:

The ’textql’ command is a versatile tool for executing SQL queries against structured text files. It simplifies the process of filtering, joining, and manipulating data stored in formats like CSV and TSV. By using SQL syntax, users can easily retrieve specific data subsets and customize the output format according to their needs.