macOS – Run SQLite Operations from Terminal

command linemacossqliteterminal

When I download transactions in Quicken Essentials for Mac, my bank adds "DEBIT PURCHASE" and other junk to the Payee info.

Quicken has no ability to find and replace globally.

But I dug into the Quicken data file and it turns out all the transactions are stored in a SQLite file.

I was able to open this file with Liya and then run a query, e.g.,

UPDATE ZFIPAYEE SET ZNAME = replace(ZNAME,'DEBIT PURCHASE - ','');

which cleaned up my transactions.

Is there some way I can run this kind of query directly from Terminal without having to open up Liya and then opening the file?

Best Answer

Yes. You can use the /usr/bin/sqlite3 command line tool to modify the database. It ships, default, with OS X so you don't need to do anything other than open a Terminal window and then run:

cd path/to/quicken/storage/directory
sqlite3 <quicken database file>

SQLite version 3.7.13 2012-07-17 17:46:21
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> UPDATE ZFIPAYEE SET ZNAME = replace(ZNAME,'DEBIT PURCHASE - ','');
sqlite> .quit

You can do it all from one command line like this if you like:

sqlite3 <quicken database file> 'UPDATE ZFIPAYEE SET ZNAME = replace(ZNAME,\'DEBIT PURCHASE - \',\'\');'

That will open, modify and close your database file and return the prompt.

For more information on using the SQLite command line too see: http://www.sqlite.org/sqlite.html

Related Question