On Mon, Jan 2, 2012 at 2:13 AM, John Wiegley <[email protected]> wrote:
> How exactly are you loading the Ledger data into SQL? I've actually been
> wanting an 'sql' report for some data, to facility data mining with
> professional DB tools... Maybe we can get native SQL generation in for 3.1 or
> something.
Effectively I'm dumping CSV from ledger and reading it in
line-by-line, converting it to a hash so hooks can mess with it
easily, and then dumping the hash into the database table. All of this
happens inside a database transaction[1]. The specific format
string[2] I'm using is:
%(quoted(xact.beg_line)),%(quoted(date)),%(quoted(payee)),%(quoted(account)),%(quoted(commodity)),%(quoted(quantity(scrub(display_amount)))),%(quoted(cleared)),%(quoted(virtual)),%(quoted(join(note
| xact.note)))\n
You'll note that this is almost identical to the native CSV report
with the addition of the beginning line of the transaction, which I
use as xtn_id within the system.
The one thing that I'm not satisfied with is that if I have a
transaction like this:
2011/12/22 * Vanguard Dividend
Assets:Vanguard:Roth:VISGX 0.937 VISGX @ $21.54
Income:Investing
The 0.937 VISGX comes out just fine as display_amount and commodity,
but for the life of me I can't figure out how to get the $21.54.
What's the format command for that?
--Pete
[1]:
https://github.com/peterkeen/ledger-web/blob/master/lib/ledger_web/db.rb#L14
[2]:
https://github.com/peterkeen/ledger-web/blob/master/lib/ledger_web/config.rb#L72
