csvkit commands

cheat_csvkit.sh

#read xlsx
in2csv a.xlsx

#convert xlsx to csv (also works for xls)
in2csv a.xlsx > data.csv

#pretty print stdout spreadsheet
csvlook data.csv | less -S

#display column names
csvcut -n data.csv

#display only certain columns
csvcut -c 2,5,6 data.csv

#display only certain columns by column name
csvcut -c county,item_name,quantity data.csv | csvlook | head

#get stats on data inside spreadsheet
csvcut -c county,acquisition_cost,ship_date data.csv | csvstat

#display only certain columns by column name where certain column equals a certain value and pretty print output stdout
csvcut -c county,item_name,total_cost data.csv | csvgrep -c county -m LANCASTER | csvlook

#display only certain columns by column name where certain column equals a certain value, sort by certain column name and pretty print output stdout
csvcut -c county,item_name,total_cost data.csv | csvgrep -c county -m LANCASTER | csvsort -c total_cost -r | csvlook

#simply get stats on data inside spreadsheet
csvstat a.csv

#join csv by related column name from 2 spreadsheets
csvjoin -c column_name data.csv b.csv > joined.csv

#get column stats on certain columns only
csvstat -c state,acquisition_cost region.csv

#combine both spreadsheets
csvstack a.csv b.csv > c.csv

#insert csv into sqlite database table 
csvsql --db sqlite:///leso.db --insert a.csv

#run select query on sqlite table added by spreadsheet
sql2csv --db sqlite:///leso.db --query "select * from a"

#run join query on sqlite table added by spreadsheet
sql2csv --db sqlite:///leso.db --query "select * from a where county='DOUGLAS'"

#run query where greater than number
csvsql --query "select county,item_name from a where quantity > 5"

#turn csv into json 
csvcut -c county,item_name data.csv | csvgrep -c county -m "GREELEY" | csvjson --indent 4