3

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?

7

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

  • Awesome! Now ... is there a way to replace with wildcards? I also want to delete all the CHECKCARD 1215 references, where the 1215 is a date that changes all the time. Does replace have a wildcard function? – Dan Jan 2 '14 at 20:39
  • ...or maybe I could just 'trim' off the first 14 characters where like "CHECKCARD ____%" ? – Dan Jan 2 '14 at 20:42
  • SQLite supports just about the whole gamut of SQL queries, so yes you can use wildcards with replace. You're venturing in to a topic that's slightly out of bounds for Ask Different though because it's not Apple-specific. It's actually a programming question. – Ian C. Jan 2 '14 at 20:43
  • Which StackExchange site do you suggest? I Googled wildcards for replace and came up with nothing easy. – Dan Jan 2 '14 at 20:44
  • 1
    stackoverflow.com -- don't quote me a wildcards and replace though. I am far from an SQL expert. – Ian C. Jan 2 '14 at 20:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .