In this post, I will demonstrate how to run SQL queries on CSV, TSV files. Here I will mention a free command line tool, Csvq that you can use to query data from CSV, TSV files by running SQL commands. You can easily run SELECT, UPDATE, INSERT, DELETE like commands on CSV files to manage the data. This tool treats the specified CSV file as a data table and you can easily manipulate data inside it. Also, not only CSV or TSV but if you have any other delimiter in your CSV file, then you can specify that. The tool is very simple and performs the operations on the CSV file efficiently.
Csvq is an open source tool that you can run on multiple platforms and manage CSV data like pro. If you have a strong command over SQL, then you can try this tool. It organizes the CSV data as a table on the terminal or command prompt. And you can use the name of the CSV file as the table name in SQL commands. And not just basic SQL queries, but you can run some other complex SQL statement too like COUNT, SUM, AVG, Wildcards, joins, etc.
How to Run SQL Queries on CSV, TSV files?
I have already mentioned that Csvq is a cross platforms tool that you can use. While using it from the command line, you will get the feeling of using MySQL or Oracle database. You can easily run basic as well some complex SQL queries with it on any CSV file. Also, if you want, then you can opt to get the output in other format as well. It allows you to produce output in CSV, TSV, Text, and JSON. You will just have to change some switches to get this done.
Here are some steps to use Csvq in Windows to run SQL Queries on CSV, TSV files.
Step 1: Get the latest binary of Csvq for Windows from the releases page of Csvq. After that, extract the downloaded archive and copy-paste the “csvq.exe” file to “C:\Windows” folder. Now, it will be accessible from any location on your PC.
Step 2: Open a command prompt in the folder where the target CSV file is and that you want to manipulate. After that, you can start running SQL queries. And do note that, you can use the CSV file name as the table name for SQL commands. For example, if the CSV file has a name “test.csv” then in command, you can just use “test”. There is no need to include the file extension. See these examples.
Select command: csvq “select * from test”
Update command: csvq “update test set Name=’Silas’ where RollNo=17”
Insert command: csvq “INSERT INTO test Values(‘Silas’,17,’11COM’,’286/300′,’xc008′)”
Delete command: csvq “DELETE FROM test where RollNo=17”
Sum, Count, and Wildcards like this:
These screenshots will be enough to make you understand how this tool works. You can run SQL queries like this and manage data like a pro. If you are in a database related field where you use SQL queries more often, then I am sure that you will like this unique tool.
Related: How to run SQL Queries in Excel?
Final thoughts
I really like this unique tool, Csvq to run SQL queries on CSV, TSV files. If you have some CSV files and want to manipulate the data inside of them in a unique way, then you can give a try to this tool.
