[GNC] csv of invoice data

[david whiting]

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 at 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 at 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