I needed to play with the invoices data a while ago and used the query below (gnucash file saved as an sqlite database). I didn't need the full invoice details and only selected a few of the fields. This should be enough to get you started. Note that the invoices are in one table and the invoice items are in another table.
SELECT invoices.date_posted, invoices.id, invoices.notes, accounts.name AS acct_name, entries.description, quantity_num, quantity_denom, b_price_num, b_price_denom, quantity_num / CAST(quantity_denom AS FLOAT) AS quantity, b_price_num / CAST(b_price_denom AS FLOAT) AS price FROM entries, accounts LEFT JOIN invoices ON entries.bill = invoices.guid David On Mon, 28 Aug 2023 at 16:24, Morgan Read via gnucash-user <gnucash-user@gnucash.org> wrote: > > Hi again :) > > In addition to wanting to deal with some old invoices, I'm also wanting > to generate a csv of invoice data. There's this post a few years back, > but I feel there must be better. I've discovered the page on > 'ledger-cli': https://wiki.gnucash.org/wiki/Ledger-CLI - which seems > complicated (perhaps not) but, I'm using a sqlite as a back-end anyway. > > It says here: > https://wiki.gnucash.org/wiki/Published_tools > That bad things will happen if accessing the GnuCash data outside of > GnuCash - but, one of the reasons for switching to sqlite a while back > was accessing the db to pull things out - and no harm can be done if the > db is accessed read-only, can it? Further down the page there's some > links to export invoice data under 'Export invoices' - the GIP invoice > printer says something about a csv template: > https://lists.gnucash.org/pipermail/gnucash-user/2008-June/025615.html > > I've set up access to the db via unixodbc and from there opened it in LO > Base - but having got there, I got to wondering if there's an easier way > to get a csv of invoice data? > > Thanks > -- > Morgan Read > > Grande Bretagne > Em: <email:mstuffATreadDOTorgDOTnz> > _______________________________________________ > gnucash-user mailing list > gnucash-user@gnucash.org > To update your subscription preferences or to unsubscribe: > https://lists.gnucash.org/mailman/listinfo/gnucash-user > ----- > Please remember to CC this list on all your replies. > You can do this by using Reply-To-List or Reply-All. -- David Whiting _______________________________________________ gnucash-user mailing list gnucash-user@gnucash.org To update your subscription preferences or to unsubscribe: https://lists.gnucash.org/mailman/listinfo/gnucash-user ----- Please remember to CC this list on all your replies. You can do this by using Reply-To-List or Reply-All.
