From: Wray C. <wr...@au...> - 2007-03-01 17:18:14
|
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? Wray Cason "Wrayman" Auxilium Computing www.auxiliumcomputing.com wr...@au... main: 206-595-2080 fax: 425-895-9825 msn: wr...@pi... aim: AuxiliumWrayman yahoo: wraycason skype: wrayman |
From: Penguin P. <pen...@gm...> - 2007-03-02 06:23:02
|
Message: 27 Date: Thu, 1 Mar 2007 12:43:25 -0500 (EST) From: ma...@hu... Subject: Re: [SL] Output reports to csv To: sql...@li... Cc: sql...@li... Message-ID: <297...@bi...> Content-Type: text/plain; charset=iso-8859-1 > 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? What I do is highlight the HTML in the Firefox, Ctrl-C, then in Excel say "Paste Special" and then "Text". It might work with OpenOffice as well. m I can confirm it does in OpenOffice, You Paste though in unformatted text or it fills your spreadsheet with (I think you call them) URL links to Sql-Ledger (that in itself has uses in presentations and updating prices etc..) Tim |
From: Dr E. L. <el...@li...> - 2007-03-02 10:20:42
|
It is almost trivial, to put this statement of mine into a perl script, and using the appropriate CPAN module, to generate Excell or OpenOffice spreadsheets. el on 3/2/07 8:22 AM Penguin Products said the following: > What I do is highlight the HTML in the Firefox, Ctrl-C, then in Excel say > "Paste Special" and then "Text". > > It might work with OpenOffice as well. > > m > > I can confirm it does in OpenOffice, You Paste though in unformatted text or > it fills your spreadsheet with (I think you call them) URL links to > Sql-Ledger (that in itself has uses in presentations and updating prices > etc..) > > Tim |
From: david <da...@ke...> - 2007-03-02 10:32:58
|
On Fri, 2007-03-02 at 12:18 +0200, Dr Eberhard Lisse wrote: > It is almost trivial, to put this statement of mine into a perl script, > and using the appropriate CPAN module, to generate Excell or OpenOffice > spreadsheets. > It's NOT trivial. I'm sure I could manage it, and I'm sure you, being the ultimate renaissance man, could manage it too.. but I have to say that CSV export is a very sensible addition to the wish list. By your reasoning, there shouldn't be an invoice function in SL because anyone could do their own. Please try not to be a pompous ass. > el > > on 3/2/07 8:22 AM Penguin Products said the following: > > > What I do is highlight the HTML in the Firefox, Ctrl-C, then in Excel say > > "Paste Special" and then "Text". > > > > It might work with OpenOffice as well. > > > > m > > > > I can confirm it does in OpenOffice, You Paste though in unformatted text or > > it fills your spreadsheet with (I think you call them) URL links to > > Sql-Ledger (that in itself has uses in presentations and updating prices > > etc..) > > > > Tim > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share your > opinions on IT & business topics through brief surveys-and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users > |
From: C. D. H. <Du...@Du...> - 2007-03-02 14:43:45
|
david wrote: > On Fri, 2007-03-02 at 12:18 +0200, Dr Eberhard Lisse wrote: > >> It is almost trivial, to put this statement of mine into a perl script, >> and using the appropriate CPAN module, to generate Excell or OpenOffice >> spreadsheets. >> >> > > > It's NOT trivial. I'm sure I could manage it, and I'm sure you, being > the ultimate renaissance man, could manage it too.. but I have to say > that CSV export is a very sensible addition to the wish list. > I believe it is sensible, and I would say should be required - because every other accounting package does it. It has has been discussed many times on the list before, and we always get the 'Just cut and paste' responses. And then eventually Dieter will chime in and say, "you have the source add it". Dunc |
From: Dr E. L. <el...@li...> - 2007-03-02 15:45:24
|
Please don't try to be an idiot. el on 3/2/07 12:40 PM david said the following: > On Fri, 2007-03-02 at 12:18 +0200, Dr Eberhard Lisse wrote: >> It is almost trivial, to put this statement of mine into a perl script, >> and using the appropriate CPAN module, to generate Excell or OpenOffice >> spreadsheets. >> > > > It's NOT trivial. I'm sure I could manage it, and I'm sure you, being > the ultimate renaissance man, could manage it too.. but I have to say > that CSV export is a very sensible addition to the wish list. > > By your reasoning, there shouldn't be an invoice function in SL because > anyone could do their own. Please try not to be a pompous ass. |
From: Christopher A. B. <cb...@ge...> - 2007-03-02 22:46:23
|
I wouldn't advise anyone on this list to try to be an idiot. The good doctor has already filled that position. -Chris ----- Original Message ----- From: "Dr Eberhard Lisse" <el...@li...> To: da...@ke... Cc: sql...@li... Sent: Friday, March 2, 2007 7:45:08 AM (GMT-0800) America/Los_Angeles Subject: Re: [SL] Output reports to csv Please don't try to be an idiot. el on 3/2/07 12:40 PM david said the following: > On Fri, 2007-03-02 at 12:18 +0200, Dr Eberhard Lisse wrote: >> It is almost trivial, to put this statement of mine into a perl script, >> and using the appropriate CPAN module, to generate Excell or OpenOffice >> spreadsheets. >> > > > It's NOT trivial. I'm sure I could manage it, and I'm sure you, being > the ultimate renaissance man, could manage it too.. but I have to say > that CSV export is a very sensible addition to the wish list. > > By your reasoning, there shouldn't be an invoice function in SL because > anyone could do their own. Please try not to be a pompous ass. ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys-and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ sql-ledger-users mailing list sql...@li... https://lists.sourceforge.net/lists/listinfo/sql-ledger-users |
From: Dr E. L. <el...@li...> - 2007-03-03 20:24:24
|
I didn't ask you to advise anyone, I asked you not to try and be one. el on 3/3/07 12:46 AM Christopher A. Bailey said the following: > I wouldn't advise anyone on this list to try to be an idiot. The good doctor has already filled that position. > > -Chris > > ----- Original Message ----- > From: "Dr Eberhard Lisse" <el...@li...> > To: da...@ke... > Cc: sql...@li... > Sent: Friday, March 2, 2007 7:45:08 AM (GMT-0800) America/Los_Angeles > Subject: Re: [SL] Output reports to csv > > Please don't try to be an idiot. > > el > > on 3/2/07 12:40 PM david said the following: >> On Fri, 2007-03-02 at 12:18 +0200, Dr Eberhard Lisse wrote: >>> It is almost trivial, to put this statement of mine into a perl script, >>> and using the appropriate CPAN module, to generate Excell or OpenOffice >>> spreadsheets. >>> >> >> It's NOT trivial. I'm sure I could manage it, and I'm sure you, being >> the ultimate renaissance man, could manage it too.. but I have to say >> that CSV export is a very sensible addition to the wish list. >> >> By your reasoning, there shouldn't be an invoice function in SL because >> anyone could do their own. Please try not to be a pompous ass. > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share your > opinions on IT & business topics through brief surveys-and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users > > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share your > opinions on IT & business topics through brief surveys-and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users -- Dr. Eberhard W. Lisse \ / Obstetrician & Gynaecologist (Saar) el...@li... el108-ARIN / * | Telephone: +264 81 124 6733 (cell) PO Box 8421 \ / Please do NOT email to this address Bachbrecht, Namibia ;____/ if it is DNS related in ANY way |
From: Wray C. <wr...@au...> - 2007-03-03 22:15:53
|
For what it is worth, I find this mailing list useful when it contains technical discussion of SQL ledger. I don't find it useful when it contains flaming and sniping. > I didn't ask you to advise anyone, I asked you not to try and be one. > > el > > on 3/3/07 12:46 AM Christopher A. Bailey said the following: >> I wouldn't advise anyone on this list to try to be an idiot. The good >> doctor has already filled that position. >> >> -Chris >> >> ----- Original Message ----- >> From: "Dr Eberhard Lisse" <el...@li...> >> To: da...@ke... >> Cc: sql...@li... >> Sent: Friday, March 2, 2007 7:45:08 AM (GMT-0800) America/Los_Angeles >> Subject: Re: [SL] Output reports to csv >> >> Please don't try to be an idiot. >> >> el >> >> on 3/2/07 12:40 PM david said the following: >>> On Fri, 2007-03-02 at 12:18 +0200, Dr Eberhard Lisse wrote: >>>> It is almost trivial, to put this statement of mine into a perl >>>> script, >>>> and using the appropriate CPAN module, to generate Excell or >>>> OpenOffice >>>> spreadsheets. >>>> >>> >>> It's NOT trivial. I'm sure I could manage it, and I'm sure you, being >>> the ultimate renaissance man, could manage it too.. but I have to say >>> that CSV export is a very sensible addition to the wish list. >>> >>> By your reasoning, there shouldn't be an invoice function in SL because >>> anyone could do their own. Please try not to be a pompous ass. >> >> ------------------------------------------------------------------------- >> Take Surveys. Earn Cash. Influence the Future of IT >> Join SourceForge.net's Techsay panel and you'll get the chance to share >> your >> opinions on IT & business topics through brief surveys-and earn cash >> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV >> _______________________________________________ >> sql-ledger-users mailing list >> sql...@li... >> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users >> >> >> ------------------------------------------------------------------------- >> Take Surveys. Earn Cash. Influence the Future of IT >> Join SourceForge.net's Techsay panel and you'll get the chance to share >> your >> opinions on IT & business topics through brief surveys-and earn cash >> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV >> _______________________________________________ >> sql-ledger-users mailing list >> sql...@li... >> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users > > -- > Dr. Eberhard W. Lisse \ / Obstetrician & Gynaecologist (Saar) > el...@li... el108-ARIN / * | Telephone: +264 81 124 6733 (cell) > PO Box 8421 \ / Please do NOT email to this address > Bachbrecht, Namibia ;____/ if it is DNS related in ANY way > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share > your > opinions on IT & business topics through brief surveys-and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users > > Wray Cason "Wrayman" Auxilium Computing www.auxiliumcomputing.com wr...@au... main: 206-595-2080 fax: 425-895-9825 msn: wr...@pi... aim: AuxiliumWrayman yahoo: wraycason skype: wrayman |
From: Christopher A. B. <cb...@ge...> - 2007-03-06 06:35:04
|
Wray, I agree with you. It's just that I've read so many posts where "el" trashes people for asking perfectly good questions or, worse yet, making suggestions that I feel like he really is just being an asshole. Let's call a spade a spade. Ideas are a vital part of discussion groups like this one. From reading this list, I'm not the first one to complain about him, but I will try to refrain from sinking to his level again. Thanks for keeping us on topic. -Chris ----- Original Message ----- From: "Wray Cason" <wr...@au...> To: sql...@li... Sent: Saturday, March 3, 2007 2:15:43 PM (GMT-0800) America/Los_Angeles Subject: Re: [SL] Output reports to csv For what it is worth, I find this mailing list useful when it contains technical discussion of SQL ledger. I don't find it useful when it contains flaming and sniping. > I didn't ask you to advise anyone, I asked you not to try and be one. > > el > > on 3/3/07 12:46 AM Christopher A. Bailey said the following: >> I wouldn't advise anyone on this list to try to be an idiot. The good >> doctor has already filled that position. >> >> -Chris >> >> ----- Original Message ----- >> From: "Dr Eberhard Lisse" <el...@li...> >> To: da...@ke... >> Cc: sql...@li... >> Sent: Friday, March 2, 2007 7:45:08 AM (GMT-0800) America/Los_Angeles >> Subject: Re: [SL] Output reports to csv >> >> Please don't try to be an idiot. >> >> el >> >> on 3/2/07 12:40 PM david said the following: >>> On Fri, 2007-03-02 at 12:18 +0200, Dr Eberhard Lisse wrote: >>>> It is almost trivial, to put this statement of mine into a perl >>>> script, >>>> and using the appropriate CPAN module, to generate Excell or >>>> OpenOffice >>>> spreadsheets. >>>> >>> >>> It's NOT trivial. I'm sure I could manage it, and I'm sure you, being >>> the ultimate renaissance man, could manage it too.. but I have to say >>> that CSV export is a very sensible addition to the wish list. >>> >>> By your reasoning, there shouldn't be an invoice function in SL because >>> anyone could do their own. Please try not to be a pompous ass. >> >> ------------------------------------------------------------------------- >> Take Surveys. Earn Cash. Influence the Future of IT >> Join SourceForge.net's Techsay panel and you'll get the chance to share >> your >> opinions on IT & business topics through brief surveys-and earn cash >> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV >> _______________________________________________ >> sql-ledger-users mailing list >> sql...@li... >> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users >> >> >> ------------------------------------------------------------------------- >> Take Surveys. Earn Cash. Influence the Future of IT >> Join SourceForge.net's Techsay panel and you'll get the chance to share >> your >> opinions on IT & business topics through brief surveys-and earn cash >> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV >> _______________________________________________ >> sql-ledger-users mailing list >> sql...@li... >> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users > > -- > Dr. Eberhard W. Lisse \ / Obstetrician & Gynaecologist (Saar) > el...@li... el108-ARIN / * | Telephone: +264 81 124 6733 (cell) > PO Box 8421 \ / Please do NOT email to this address > Bachbrecht, Namibia ;____/ if it is DNS related in ANY way > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share > your > opinions on IT & business topics through brief surveys-and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users > > Wray Cason "Wrayman" Auxilium Computing www.auxiliumcomputing.com wr...@au... main: 206-595-2080 fax: 425-895-9825 msn: wr...@pi... aim: AuxiliumWrayman yahoo: wraycason skype: wrayman ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys-and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ sql-ledger-users mailing list sql...@li... https://lists.sourceforge.net/lists/listinfo/sql-ledger-users |
From: Michael H. <mh...@it...> - 2007-03-07 02:42:51
|
Yes, please, stop replying to him! (The only time I have to see his crap is when somebody replies and I read the thread. :) On a more serious note, while it is easy enough to filter people like that, first impressions do count. New members of the list seeing his sort of unprofessional trash talk (even to someone else) may not stick around long enough to find out how many genuinely nice and helpful people there actually are on the list. I hate to think how much business he has cost Dieter... On Mar 5, 2007, at 10:34 PM, Christopher A. Bailey wrote: > Wray, > I agree with you. It's just that I've read so many posts where "el" > trashes people for asking perfectly good questions or, worse yet, > making suggestions that I feel like he really is just being an > asshole. Let's call a spade a spade. Ideas are a vital part of > discussion groups like this one. From reading this list, I'm not > the first one to complain about him, but I will try to refrain from > sinking to his level again. Thanks for keeping us on topic. > > -Chris > ----- Original Message ----- > From: "Wray Cason" <wr...@au...> > To: sql...@li... > Sent: Saturday, March 3, 2007 2:15:43 PM (GMT-0800) America/ > Los_Angeles > Subject: Re: [SL] Output reports to csv > > For what it is worth, I find this mailing list useful when it contains > technical discussion of SQL ledger. I don't find it useful when it > contains flaming and sniping. > >> I didn't ask you to advise anyone, I asked you not to try and be one. >> >> el >> >> on 3/3/07 12:46 AM Christopher A. Bailey said the following: >>> I wouldn't advise anyone on this list to try to be an idiot. The >>> good >>> doctor has already filled that position. >>> >>> -Chris >>> >>> ----- Original Message ----- >>> From: "Dr Eberhard Lisse" <el...@li...> >>> To: da...@ke... >>> Cc: sql...@li... >>> Sent: Friday, March 2, 2007 7:45:08 AM (GMT-0800) America/ >>> Los_Angeles >>> Subject: Re: [SL] Output reports to csv >>> >>> Please don't try to be an idiot. >>> >>> el >>> >>> on 3/2/07 12:40 PM david said the following: >>>> On Fri, 2007-03-02 at 12:18 +0200, Dr Eberhard Lisse wrote: >>>>> It is almost trivial, to put this statement of mine into a perl >>>>> script, >>>>> and using the appropriate CPAN module, to generate Excell or >>>>> OpenOffice >>>>> spreadsheets. >>>>> >>>> >>>> It's NOT trivial. I'm sure I could manage it, and I'm sure you, >>>> being >>>> the ultimate renaissance man, could manage it too.. but I have >>>> to say >>>> that CSV export is a very sensible addition to the wish list. >>>> >>>> By your reasoning, there shouldn't be an invoice function in SL >>>> because >>>> anyone could do their own. Please try not to be a pompous ass. >>> >>> -------------------------------------------------------------------- >>> ----- >>> Take Surveys. Earn Cash. Influence the Future of IT >>> Join SourceForge.net's Techsay panel and you'll get the chance to >>> share >>> your >>> opinions on IT & business topics through brief surveys-and earn cash >>> http://www.techsay.com/default.php? >>> page=join.php&p=sourceforge&CID=DEVDEV >>> _______________________________________________ >>> sql-ledger-users mailing list >>> sql...@li... >>> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users >>> >>> >>> -------------------------------------------------------------------- >>> ----- >>> Take Surveys. Earn Cash. Influence the Future of IT >>> Join SourceForge.net's Techsay panel and you'll get the chance to >>> share >>> your >>> opinions on IT & business topics through brief surveys-and earn cash >>> http://www.techsay.com/default.php? >>> page=join.php&p=sourceforge&CID=DEVDEV >>> _______________________________________________ >>> sql-ledger-users mailing list >>> sql...@li... >>> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users >> >> -- >> Dr. Eberhard W. Lisse \ / Obstetrician & Gynaecologist (Saar) >> el...@li... el108-ARIN / * | Telephone: +264 81 124 6733 (cell) >> PO Box 8421 \ / Please do NOT email to this address >> Bachbrecht, Namibia ;____/ if it is DNS related in ANY way >> >> --------------------------------------------------------------------- >> ---- >> Take Surveys. Earn Cash. Influence the Future of IT >> Join SourceForge.net's Techsay panel and you'll get the chance to >> share >> your >> opinions on IT & business topics through brief surveys-and earn cash >> http://www.techsay.com/default.php? >> page=join.php&p=sourceforge&CID=DEVDEV >> _______________________________________________ >> sql-ledger-users mailing list >> sql...@li... >> https://lists.sourceforge.net/lists/listinfo/sql-ledger-users >> >> > > > Wray Cason "Wrayman" > Auxilium Computing > www.auxiliumcomputing.com > wr...@au... > main: 206-595-2080 > fax: 425-895-9825 > msn: wr...@pi... > aim: AuxiliumWrayman > yahoo: wraycason > skype: wrayman > > ---------------------------------------------------------------------- > --- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to > share your > opinions on IT & business topics through brief surveys-and earn cash > http://www.techsay.com/default.php? > page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users > > > ---------------------------------------------------------------------- > --- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to > share your > opinions on IT & business topics through brief surveys-and earn cash > http://www.techsay.com/default.php? > page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users |
From: Paul T. <pt...@wa...> - 2007-03-03 09:48:44
|
C. Duncan Hudson schreef: > david wrote: > >> On Fri, 2007-03-02 at 12:18 +0200, Dr Eberhard Lisse wrote: >> >> >>> It is almost trivial, to put this statement of mine into a perl script, >>> and using the appropriate CPAN module, to generate Excell or OpenOffice >>> spreadsheets. >>> >>> >>> >> It's NOT trivial. I'm sure I could manage it, and I'm sure you, being >> the ultimate renaissance man, could manage it too.. but I have to say >> that CSV export is a very sensible addition to the wish list. >> >> > I believe it is sensible, and I would say should be required - because > every other accounting package does it. It has has been discussed many > times on the list before, and we always get the 'Just cut and paste' > responses. And then eventually Dieter will chime in and say, "you have > the source add it". > > Dunc > > Being both a bookkeeper and a user I might be a bit prejudiced, but if your accountant or bookkeeper is not able to use the sql-reports that are built-in that should be reason for concern. The management can not read a trial balance or a profit and loss account, that is why they like to see it in excel or even better a pie-diagram or something equally simple. If your accountant needs the same treatment, you probably hired a manager and not a person with bookkeeping skills? Anyway, install a generic printer (yes, i am talking history) and print to file should do almost any trick. If the copy paste to open office is too much of a hassle that is. Yes, it works, open office and firefox... Greetings from Holland, Paul Tammes |
From: Dr E. L. <el...@li...> - 2007-03-03 18:49:04
|
Paul, you assume that the accountant uses SL. Very few do. But the *DO* use Excel. And with regards to the renaissance nonsense-man, this took me 10 minutes, while fink was pulling the preadsheet::WriteExcel module: #!/usr/bin/perl use lib '/sw/lib/perl5'; use DBI; $host = 'localhost'; #################### $dbase = ""; #################### use Spreadsheet::WriteExcel; my $excel = new Spreadsheet::WriteExcel( "gl.xls" ); my $sheet = $excel -> addworksheet("gl"); $db = DBI->connect("dbi:Pg:dbname=$dbase;host=$host", $undef, $undef); $query = qq|SELECT c.accno, c.description, ac.transdate, g.id, rount(ac.amount::numeric,2), g.reference, g.description, substr(g.notes,1,55), 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-07-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-07-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-07-01' AND ac.transdate <= '2006-06-30' ORDER BY 1, 3, 4|; $dbh = $db->prepare($query); $in = $dbh->execute or die; $line = 0; while (@record = $dbh->fetchrow_array) { for ($column=0; $column<=7; $column++) { $sheet->write($line,$column,$record[$column]); } $line++; } el on 3/3/07 11:48 AM Paul Tammes said the following: > C. Duncan Hudson schreef: >> david wrote: >> >>> On Fri, 2007-03-02 at 12:18 +0200, Dr Eberhard Lisse wrote: >>> >>> >>>> It is almost trivial, to put this statement of mine into a perl script, >>>> and using the appropriate CPAN module, to generate Excell or OpenOffice >>>> spreadsheets. >>>> >>>> >>>> >>> It's NOT trivial. I'm sure I could manage it, and I'm sure you, being >>> the ultimate renaissance man, could manage it too.. but I have to say >>> that CSV export is a very sensible addition to the wish list. >>> >>> >> I believe it is sensible, and I would say should be required - because >> every other accounting package does it. It has has been discussed many >> times on the list before, and we always get the 'Just cut and paste' >> responses. And then eventually Dieter will chime in and say, "you have >> the source add it". >> >> Dunc >> >> > Being both a bookkeeper and a user I might be a bit prejudiced, but if > your accountant or bookkeeper is not able to use the sql-reports that > are built-in that should be reason for concern. The management can not > read a trial balance or a profit and loss account, that is why they like > to see it in excel or even better a pie-diagram or something equally simple. > > If your accountant needs the same treatment, you probably hired a > manager and not a person with bookkeeping skills? > Anyway, install a generic printer (yes, i am talking history) and print > to file should do almost any trick. > If the copy paste to open office is too much of a hassle that is. Yes, > it works, open office and firefox... > > Greetings from Holland, > Paul Tammes > > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share your > opinions on IT & business topics through brief surveys-and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users -- Dr. Eberhard W. Lisse \ / Obstetrician & Gynaecologist (Saar) el...@li... el108-ARIN / * | Telephone: +264 81 124 6733 (cell) PO Box 8421 \ / Please do NOT email to this address Bachbrecht, Namibia ;____/ if it is DNS related in ANY way |
From: <ma...@hu...> - 2007-03-06 08:32:25
|
> It is almost trivial, to put this statement of mine into a perl script, > and using the appropriate CPAN module, to generate Excell or OpenOffice > spreadsheets. > Nice. m |
From: Russell D. <rj...@ex...> - 2007-03-03 10:26:14
|
On Fri, 02 Mar 2007 12:18:46 +0200 Dr Eberhard Lisse <el...@li...> wrote: > It is almost trivial, to put this statement of mine into a perl script, > and using the appropriate CPAN module, to generate Excell or OpenOffice > spreadsheets. > Could you please detail the steps to enable this? This would be very useful. TIA Russell -- Please, if possible, don't send me MS Word or Powerpoint attachments. Send plain text, rich text format, html, or pdf instead. Why? See: http://www.gnu.org/philosophy/sylvester-response.html |
From: Dr E. L. <el...@li...> - 2007-03-03 18:08:09
|
Use the Excewrite module or whatever it is called these days, write the hedaings, add the formats, use the DBI module, prepare and execute the statement, read through every record and write to the spreadsheed. el on 3/3/07 12:25 PM Russell Davie said the following: > On Fri, 02 Mar 2007 12:18:46 +0200 > Dr Eberhard Lisse <el...@li...> wrote: > >> It is almost trivial, to put this statement of mine into a perl script, >> and using the appropriate CPAN module, to generate Excell or OpenOffice >> spreadsheets. >> > > Could you please detail the steps to enable this? > > This would be very useful. > > TIA > > Russell > > > -- Dr. Eberhard W. Lisse \ / Obstetrician & Gynaecologist (Saar) el...@li... el108-ARIN / * | Telephone: +264 81 124 6733 (cell) PO Box 8421 \ / Please do NOT email to this address Bachbrecht, Namibia ;____/ if it is DNS related in ANY way |
From: Les R <ope...@gm...> - 2007-03-06 21:13:57
|
Hi All, Ok, I'll write a script for this. What was the original outline for the SQL? Is there any interest in a payroll module? I've been maintaining a payroll system for a regional college here is Sask for the last 20 years or so and have had an "intimate relationship" with the CCRA deduction formulae for a long time... (grin) Les Richardson Open Admin for Schools |
From: David B. <dav...@sb...> - 2007-03-01 17:28:25
|
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) > > > Wray Cason "Wrayman" > Auxilium Computing > www.auxiliumcomputing.com > wr...@au... > main: 206-595-2080 > fax: 425-895-9825 > msn: wr...@pi... > aim: AuxiliumWrayman > yahoo: wraycason > skype: wrayman > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share your > opinions on IT & business topics through brief surveys-and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users |
From: Dr E. L. <el...@li...> - 2007-03-02 04:23:34
|
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 |
From: Craig I. <cr...@da...> - 2007-03-13 15:28:05
|
EL, This was very helpful for me, thank you for posting it. Before I spend a bunch of time on this - can you tell me how much of an undertaking it would be to pull monthly P&Ls into a CSV like your example below? My goal is to output a report that would show all 12 months individually. Thanks! Craig > -----Original Message----- > From: sql...@li... [mailto:sql-ledger- > use...@li...] On Behalf Of Dr Eberhard Lisse > Sent: Thursday, March 01, 2007 11:23 PM > To: sql...@li... > Subject: Re: [SL] Output reports to csv > > 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 > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share > your > opinions on IT & business topics through brief surveys-and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users |
From: Dr E. L. <el...@li...> - 2007-03-13 19:49:44
|
Craig, It's a pleasure. What are P&Ls? I am not sure I understand what you are asking, but maybe you have a look this statement: -- -- This statement is copylefted by Dr E W Lisse -- and may not be used by the pompous Ozzie -- and the idiot Wannabe Kraut. -- -- SELECT date_part('month',ac.transdate) AS dmonth, to_char(ac.transdate,'MON') AS cmonth, sum(ROUND(ac.amount::NUMERIC,2)) AS amount FROM gl, acc_trans ac, chart WHERE (ac.trans_id = gl.id) AND chart.accno BETWEEN 4000 AND 4999 AND (ac.chart_id = chart.id) AND (ac.source not ilike '%yearend%') GROUP BY cmonth, dmonth ORDER by dmonth; Use the SQL, Luke! el on 3/13/07 5:27 PM Craig Isdahl said the following: > EL, > > This was very helpful for me, thank you for posting it. Before I > spend a bunch of time on this - can you tell me how much of an > undertaking it would be to pull monthly P&Ls into a CSV like your > example below? My goal is to output a report that would show all 12 > months individually. > > Thanks! > Craig > |
From: Craig I. <cr...@da...> - 2007-03-13 22:55:44
|
P&L = Profit & Loss (i.e. Income Statement) I'll give this a try later tonight or tomorrow and let you know how it goes... Craig > -----Original Message----- > From: sql...@li... [mailto:sql-ledger- > use...@li...] On Behalf Of Dr Eberhard Lisse > Sent: Tuesday, March 13, 2007 3:50 PM > To: sql...@li... > Subject: Re: [SL] Output reports to csv > > Craig, > > It's a pleasure. > > > What are P&Ls? I am not sure I understand what you are asking, but maybe > you have a look this statement: > > -- > -- This statement is copylefted by Dr E W Lisse > -- and may not be used by the pompous Ozzie > -- and the idiot Wannabe Kraut. > -- > -- > SELECT date_part('month',ac.transdate) AS dmonth, > to_char(ac.transdate,'MON') AS cmonth, > sum(ROUND(ac.amount::NUMERIC,2)) AS amount > FROM gl, acc_trans ac, chart > WHERE (ac.trans_id = gl.id) > AND chart.accno BETWEEN 4000 AND 4999 > AND (ac.chart_id = chart.id) > AND (ac.source not ilike '%yearend%') > GROUP BY cmonth, dmonth > ORDER by dmonth; > > > Use the SQL, Luke! > > el > > on 3/13/07 5:27 PM Craig Isdahl said the following: > > EL, > > > > This was very helpful for me, thank you for posting it. Before I > > spend a bunch of time on this - can you tell me how much of an > > undertaking it would be to pull monthly P&Ls into a CSV like your > > example below? My goal is to output a report that would show all 12 > > months individually. > > > > Thanks! > > Craig > > > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share > your > opinions on IT & business topics through brief surveys-and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users |
From: C. D. H. <Du...@Du...> - 2007-03-02 05:06:21
|
No, actually you're wrong. The original post said there was probably a means to build the file from within SQL-Ledger, and there is not. I indicated I use a tool to extract data, and you're showing us that you use a tool (psql) too. Unlike virtually every other Accounting package there is no way, from within the application to export data to a file. Dunc Dr Eberhard Lisse wrote: > 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 >> > > ------------------------------------------------------------------------- > Take Surveys. Earn Cash. Influence the Future of IT > Join SourceForge.net's Techsay panel and you'll get the chance to share your > opinions on IT & business topics through brief surveys-and earn cash > http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV > _______________________________________________ > sql-ledger-users mailing list > sql...@li... > https://lists.sourceforge.net/lists/listinfo/sql-ledger-users > > |
From: <ma...@hu...> - 2007-03-01 17:43:39
|
> 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? What I do is highlight the HTML in the Firefox, Ctrl-C, then in Excel say "Paste Special" and then "Text". It might work with OpenOffice as well. m |
From: C. D. H. <Du...@Du...> - 2007-03-01 21:35:33
|
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 |