Querying CSVs in Wagon
June 08, 2015 | Andy Granowitz

People use Wagon to query data stored in databases, but sometimes they need to analyze a file that doesn’t (yet) live in a database. Our friend Steve Pike recently showcased this hack in our #bandwagon Slack channel. He used a nifty command line tool called csvsql to automatically create a Postgres table from a CSV file and have it ready for Wagon.

Here’s how to set it up on Mac:

1. Install Postgres
   • Download this quick Postgres Installer
   • Or here’s the full documentation
2. Install csvkit
   • It’s simpler if you have pip, so install pip

3. Load your CSV file into Postgres:

    csvsql --db postgres://localhost:5432/postgres --insert --tables mytable /myfile.csv
   

   • mytable is the name of the new table
   • postgres is the name of the database, normally available by default
4. Open Wagon and connect to your database: (Don’t have access to Wagon? Sign up for early access!)
   • Nickname: csvsql
   • Type: Postgres
   • Hostname: localhost
   • Port: 5432
   • Database: postgres
   • User: [your computer’s username]
   • Password: [empty]

Woo! You can now write SQL against your CSV file using Wagon.

Try this out with your next CSV file or an example dataset of movie scenes filmed in San Francisco (source). Our team favorite Blue Jasmine isn’t number 1!

Wagon is a great way to analyze databases and now small text files. Thanks csvsql.

Need help with this quick hack? Email us at hello@wagonhq.com.