Re: [SL] Output reports to csv

[Dr E. L. <el...@li...>]

Actually this is wrong.

psql -AtF, databasename < gl.psql > gl.csv will produce CSV.

and gl.psql looking like

SELECT  c.accno, c.description, ac.transdate, g.id,
    round(ac.amount::numeric,2) as Amount,
    g.reference,
    g.description, substr(g.notes,1,55) as Notes,
    ac.memo

FROM gl g
JOIN acc_trans ac ON (g.id = ac.trans_id)
JOIN chart c ON (ac.chart_id = c.id)
LEFT JOIN department d ON (d.id = g.department_id)
WHERE 1 = 1
    AND ac.transdate >= '2005-06-01'
    AND ac.transdate <= '2006-06-30'

UNION ALL

SELECT c.accno,  c.description, ac.transdate, a.id,
    round(ac.amount::numeric,2) as Amount,
    a.invnumber,
    ct.name, substr(a.notes,1,55) as Notes,
   ac.memo
FROM ar a
JOIN acc_trans ac ON (a.id = ac.trans_id)
JOIN chart c ON (ac.chart_id = c.id)
JOIN customer ct ON (a.customer_id = ct.id)
LEFT JOIN department d ON (d.id = a.department_id)
WHERE 1 = 1
   AND ac.transdate >= '2005-06-01'
   AND ac.transdate <= '2006-06-30'

UNION ALL

SELECT c.accno,  c.description, ac.transdate,  a.id,
    round(ac.amount::numeric,2) as Amount,
    a.invnumber,
    ct.name, substr(a.notes,1,55) as Notes,
   ac.memo
FROM ap a
JOIN acc_trans ac ON (a.id = ac.trans_id)
JOIN chart c ON (ac.chart_id = c.id)
JOIN vendor ct ON (a.vendor_id = ct.id)
LEFT JOIN department d ON (d.id = a.department_id)
WHERE 1 = 1
   AND ac.transdate >= '2005-06-01'
   AND ac.transdate <= '2006-06-30'

ORDER BY 1, 3, 4;

does it for my auditor :-)-O

el

on 3/1/07 10:42 PM C. Duncan Hudson said the following:
> David Boyle wrote:
>> On Thu, 2007-03-01 at 09:17 -0800, Wray Cason wrote:
>>   
>>> My bookkeeper wants to be able to crunch some numbers from my SQL Ledger
>>> database in a spreadsheet.  Is there any established way to output reports
>>> to a csv file or something similar?
>>>     
>> I imagine there is...but copy/paste works pretty well and is easy.
>>
>> David Boyle
>> NAPCO (Northwest Arkansas Paper Company)
>>
>>   
> There isn't, actually, a way to output to any kind of file.  The 
> technical solution is cut and paste, which I think leaves a lot to be 
> desired.  I use Postgres SQL Manager to generate reports.  It would be 
> nice if the functionality was built in, as that's not a tool I give to 
> any of my users.
> 
> Dunc