[GNC] csv of invoice data

[Morgan Read]

Follow up to:
https://lists.gnucash.org/pipermail/gnucash-user/2023-August/108625.html
Apologies for not following up until after the month has rolled over...

David, yes.  Thank you.  I think that has put me on the right track. 
There's a bit of a steep hill to climb, but at least I know now which 
hill it is :)

M

> 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>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: OpenPGP_signature
Type: application/pgp-signature
Size: 833 bytes
Desc: OpenPGP digital signature
URL: <http://lists.gnucash.org/pipermail/gnucash-user/attachments/20230902/4477da6f/attachment.sig>