csvkit: query csv files using SQL from the command line

csv is the most common file format that you will use when you deal with data; all major spreadsheets like Excel or Libre Office Calc support saving / exporting sheets as csv; also, csv is used by default in most databases like MySQL and SQL server when perform exports, would be nice if you could perform SQL commands on a csv file without the need to re-import the csv to a Database or use a script language like Python? You can do all this using csvkit!.

Install csvkit

csvkit is a set Python tools / library and can be installed with pippip3 install csvkit

Convert an excel file to csv

Download the following Excel file in order to practice with csvkitcurl -L -O https://raw.githubusercontent.com/wireservice/csvkit/master/examples/realdata/ne_1033_data.xlsx

To convert the file to csv enterin2csv ne_1033_data.xlsx > data.csv

View the columns of a csv file$ csvcut -n data.csv
 1: state
 2: county
 3: fips
 4: nsn
 5: item_name
 6: quantity
 7: ui
 8: acquisition_cost
 9: total_cost
10: ship_date
11: federal_supply_category
12: federal_supply_category_name
13: federal_supply_class
14: federal_supply_class_name

Export only specific columnscsvcut -c 2,5,6 data.csv

or using column namescsvcut -c county,item_name,quantity data.csv

How to query a csv file using SQLcsvsql --query "select * from data where county='ADAMS'" data.csv

How to insert a csv file to an SQLite databasecsvsql --db sqlite:///example.db --table example --insert data.csv

Conclusion

There are many options to explore, like quote char, delimiter etc, this article does not cover all those but i think it gives you a good start on how to work with.