Include Column Names in SQLite Query Results

Posted on by Ian

By default, the SQLite command line interface displays query results using a pipe-separated list, without column headers.

This might be fine if you’re using the results in an application that doesn’t need the column names, but if you’re just a human, it can make it hard to read the data, especially if there are lots of columns.

Fortunately, there’s a quick way to present the column names in your query results.

Actually, there are at least a couple of ways to do this, and I explain them in this article.

Continue reading →

Export an Entire SQLite Database to an SQL File

Posted on by Ian

The SQLite command line provides several methods for exporting or backing up an entire database. One of those involves the .dump dot command.

The .dump command enables you to convert a whole database to a single ASCII text file. In other words, it renders the whole database as SQL. The text file contains all the SQL statements required to create the tables, insert data, etc.

You can also use .dump to create a compressed file. I outline both of these methods below.

Continue reading →

Automatically Open SQLite Query Results in Excel

Posted on by Ian

The SQLite command line interface has a handy little feature where you can open your SQL query results in an Excel file.

When you run a query, instead of the query being output to your console as it normally would, it instead opens as an Excel spreadsheet (or LibreOffice, or whatever program your system uses to open CSV files).

You have two options when doing this:

  • Use a temporary file
  • Use a permanent file (i.e. save it for later)

This article covers both options.

Continue reading →