How to use the command 'textql' (with examples)

TextQL is a powerful command-line tool that allows users to execute SQL-like queries on structured text files such as CSV or TSV files. By treating these files as SQL databases, TextQL provides a convenient way to filter, join, and manipulate large datasets directly from the command line. Here we explore several use cases demonstrating the versatility and power of TextQL.

Use case 1: Print the lines in the specified CSV file that match an SQL query to stdout

Code:

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

Motivation:

This use case is incredibly useful when you need a simple way to extract and view all the records from a CSV file. Whether you’re a data analyst sifting through datasets or a developer debugging data issues, quickly querying entire datasets can save time and effort, offering you an immediate glance into the dataset’s structure and data points.

Explanation:

• -sql "SELECT * FROM filename": This argument specifies the SQL query you want to run. "SELECT * FROM filename" is a basic SQL command that selects all columns from the dataset.
• path/to/filename.csv: This is the file path to the CSV file you wish to query. It points TextQL to the specific file location.

Example Output:

id, name, age
1, John Doe, 22
2, Jane Smith, 34
3, Emily Jones, 29

Use case 2: Query a TSV file

Code:

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

Motivation:

TSV files, or tab-separated values files, are common in data storage and exchange. When working with TSV files, the ability to query them directly can streamline data analysis processes. By appropriately setting the delimiter, you enable seamless querying of TSV data, much like CSV.

Explanation:

• -dlm=tab: This argument sets the delimiter to tab, indicating that the fields in the file are separated by tabs instead of commas.
• -sql "SELECT * FROM filename": A selector query to fetch all columns and rows from the file.
• path/to/filename.tsv: Represents the TSV file’s path on the filesystem.

Example Output:

product_id product_name price
101         Widget A     9.99
102         Gadget B     14.99
103         Thingamajig 7.49

Use case 3: Query file with header row

Code:

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

Motivation:

Often, CSV files have a header row that names each column. Recognizing these headers in queries provides clearer and more understandable results, essential for anyone needing to interact with data labels rather than using column indices.

Explanation:

• -dlm=delimiter: Specifies the delimiter in the file. Replace “delimiter” with the actual character used as a separator in your CSV file.
• -header: Instructs TextQL to treat the first row of the file as header information, using it for column names within the SQL query.
• -sql "SELECT * FROM filename": A query that selects all data points in the file.
• path/to/filename.csv: Directs TextQL to the CSV file.

Example Output:

username,email,signup_date
johndoe,johndoe@example.com,2023-01-01
janedoe,janedoe@example.com,2023-02-15
user123,user123@example.com,2022-11-23

Use case 4: Read data from stdin

Code:

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

Motivation:

Piping data into TextQL from standard input (stdin) is particularly handy for integrating SQL querying into shell scripts or workflows where data may come from various sources—not just files. This method allows for a high degree of flexibility.

Explanation:

• cat path/to/file: Command to output the file content to standard input.
• |: A pipe that directs the output of the cat command into TextQL as input.
• -sql "SELECT * FROM stdin": This argument tells TextQL to execute a query on the data coming from stdin, treating it as if it were a complete file.

Example Output:

order_id,customer,total
202,Alpha Co.,1500.50
203,Beta LLC,2780.00
204,Gamma Inc,320.35

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

Code:

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

Motivation:

Joining datasets is a common task in data analysis, particularly when datasets are broken across multiple files. This command showcases how TextQL can be used to easily join two files on a common column, a powerful feature for comparing or enriching datasets.

Explanation:

• -header: Indicates that both input files have headers, enabling header names for use in the SQL query.
• -sql "SELECT * [...] LIMIT 10": A SQL query for joining two files on a specified column, with LIMIT 10 in place to restrain output to ten rows for ease of analysis.
• -output-header: Ensures the resulting output includes a header row.
• path/to/file1.csv path/to/file2.csv: Specifies the two input files to join.

Example Output:

id,name,address,phone
1,Acme Inc.,123 Main St,(555) 123-4567
2,Globex Corp.,456 Elm St,(555) 234-5678

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:

When preparing data for sharing, visualization, or further processing, you might need to reformulate its layout or delimiters. Formatting the output of your query can make subsequent data manipulation easier and more efficient.

Explanation:

• -output-dlm=delimiter: Sets the delimiter for the command’s output. Replace “delimiter” with your desired character, like a comma or tab.
• -output-header: Tells TextQL to include column headers in the output.
• -sql "SELECT column AS alias FROM filename": A query to select a specific column and rename it during output.
• path/to/filename.csv: The input CSV file path.

Example Output:

new_column_name
data1
data2
data3

Conclusion:

TextQL provides a highly adaptable and lightweight solution for querying structured text data directly from the command line. With capabilities that include joining multiple datasets, transforming file formats, and processing input from both files and standard input, TextQL proves to be an indispensable tool for data professionals and enthusiasts alike.