Comparing two Gnucash files

[John Ralls]

On Jul 11, 2013, at 10:46 AM, Maf. King <maf at chilwell.net> wrote:

> Hi Alois,
> 
> Would there be any merit for you to do a save as....uncompressed XML for both 
> sqlite databases - maybe your diff tool will give you a clue about the newer 
> file?
> 
> I can already hear the howls of anguish from the devs about attempting a merge 
> of the 2 data files, though!  Back up and back up often if you take that path. 
> - there is a gnucash-qif utility somewhere on the web and possibly you could 
> import newer transactions from one of the files, but not sure about how to 
> handle date changes or deletions...
> 
> 
> On Thu 11 July 13 19:38:41 Alois Mahdal wrote:
>> Hi,
>> 
>> I need to compare two sqlite-based GC files (holding few months
>> of personal finances) that may or may not be different.
>> 
>> However, if they are, either one of them is newer, or (in worse
>> case) they must have a common ancestor, which I guess physically
>> does not exist, unless GC saves history of revisions like e.g.
>> git.
>> 
>> Note that the changes on both sides might be as wall additive as
>> substractive, so usual rule as "bigger = newer" is way too risky
>> here.  Also there won't be many chages, I'd guess minor (like date)
>> edits to 10-100 items, maybe a split here and there.
>> 
>> Now if it was a CSV or any other simple structure, I'd just grab
>> <my favorite diff/merging tool> and resolve the "conflict" using
>> merge if necessary.  I realize, though, that GC's data structure
>> must be very complicated, so if I am able to simply dump the
>> sqlite file and give it a try, I could spend hours on it and not
>> be sure, and in case of merging, I could easily end up with unusable
>> file.
>> 
>> No backups available :/
>> 
>> So is ther a "safe way" to do it?  Or should I just bite the bullet,
>> flip a coin and carefully revise the data according to other sources?

Duly howling. ;-)

This stackoverflow page might be helpful:
http://stackoverflow.com/questions/2093263/how-to-compare-two-sqlite-databases-on-linux

Maf's suggestion to save both as uncompressed XML files is good. Unfortunately diff and merge are likely to show lots of changes from differently-ordered blocks. I found a project on SourceForge, http://diffxml.sourceforge.net/, that claims to overcome at least some of those limitations.

I'll reiterate Maf's warning to make backups. Of course, saving as XML does that, and if you save your merge results to a third file you're covered.

There are -- or should be -- log files saved alongside the sqlite databases, named foo.gnucash.timestamp.log. They contain only changes to transactions, so they won't tell the complete story, but they might be helpful in determining the history of each file, including how long ago they diverged.

Regards,
John Ralls