Recovered from a crash to find columns swapped and assets negative

[John Ralls]

> On Feb 15, 2016, at 10:26 AM, Rean Jacob <reanjacob at gmail.com> wrote:
> 
> Wow! Thanks a ton.
> 
> Mac – OS X 10.11.13
> 
> Pretty good with command line.
> 
> Please guide me. 
> 

The following query will reverse the sign on all of the splits in the file which were entered before a certain date (posted means the date on which the transaction is effective, it's the one that displays in the register; entered means the date and time at which you created the transaction). If that's not exactly what you think you need, you need to provide more details.

First, from GnuCash after loading your file, select File>Save As.... At the top of the save dialog box change Data Format to SQLite3, give a new name, then click OK. Close GnuCash.

Open a Terminal session and at the prompt say sqlite3 /path/to/db-account-file.
At the prompt, enter

UPDATE splits SET value_num = -value_num, quantity_num = -quantity_num WHERE tx_guid IN (SELECT guid FROM transactions WHERE enter_date < 20050731000000);

That's all one line. Substitute the actual date and time of your crash for 20050731000000. Quit the SQLite3 shell with ctrl-d and restart GnuCash. Examine the data carefully to make sure it did exactly what you want. When you're satisfied you can rename the xml file for safety and Save As back to the XML format. If it's screwed up analyze why and adjust the SQL query as necessary. Open the old file in GnuCash, delete the db file from the command-line, re-do the Save As, and repeat with the adjusted query.

Regards,
John Ralls