tabular data tools

with woodworking there’s a lot of different ways to put steel to wood and the tools you choose informs the work you do and how enjoyable it is. same thing applies to working with tabular data.

pretty print records in postgres

sometimes you select * from some_table limit 5 in postgres and there’s more rows than you have in your terminal. run \x or \x auto in psql to turn on ‘expanded display’ and now instead of a wide horizontal table, you’ll get a list of your records with one row per column. the postgres docs mention this but don’t really explain it much, but it rocks.

another good formatting option psql supports is csv

\pset format csv

after that’s set, you’re working with the king of tabular data formats.

while looking into this, i also discovered you can setup a ~/.psqlrc file to configure stuff like this automatically.

xsv

xsv is my go to way of looking at tabular data. doesn’t matter if you’re looking at kbs or gbs of data, you can easily find out the answers you’re looking for.

convert between json and csv with dasel

dasel provides a solid interface to go between json to csv.dasel also supports yaml, toml, xml combine this with xsv and you have a pretty human friendly way of checking out records in plain text.

render tsv as elastic tabstops with tabwriter

tabwriter is mostly a rust library but it ships with a binary that you can pipe tab separated data into, and it’ll align it all. when i work with tabs (e.g. pasting in stuff from a spreadsheet or html table) it comes in handy.

make your data more easily pasted

99% of the time plaintext is what i want, and xsv’s table command gives me it

# assuming you are on a mac and have some csv in your clipboard...
pbpaste | xsv table

but, you can make your csv data a bit more paste-able into some programs (e.g. Linear) by copying it as rich-text.

pbpaste | csv2html | textutil -stdin -format html -convert rtf -stdout | pbcopy

i saved this as a function to make it easy.

© 2024 peter schilling