How to create pivot tables via perl?

rafa has asked for the wisdom of the Perl Monks concerning the following question:

Replies are listed 'Best First'.
Re: How to create pivot tables via perl? by marto (Cardinal) on Feb 01, 2007 at 16:14 UTC
rafa, Check out Creating Pivot tables from Perl, I provided a non Perl solution to this problem which may be of use to you. Populating the Excel file with your data from the database should not be a problem. Update: If you do need help querying your database check out the DBI module along with Database Programming. Regards using Win32::OLE with Excel have a look at Using Win32::OLE and Excel - Tips and Tricks from the tutorials section of this site. If you have an issue with anything I have mentioned (or anything else related to this task) let us know. Hope this helps. Martin	[reply]
Re^2: How to create pivot tables via perl? by rafa (Novice) on Feb 01, 2007 at 18:39 UTC
Thanks Martin. I have read your reply on this query before and have also looked into the other links you mentioned. I'm gonna look again and see if there's anything I missed. Most of my knowledge on this are only based on examples I found on the web. As commented by an Anonymous Monk on "Using Win32::OLE and Excel - Tips and Tricks", I too have yet to stumble upon documentation containing all the valid fields and subfields. An example is how to format all the cells of a column as a Number with 2 decimal places, stuff like that. To ww, I did try to look for it in "Super Search", but I really haven't found what I am looking for. Just hoping that this time someone can give me an answer. Thanks for the advice nonetheless. Rafa	[reply]
Re^3: How to create pivot tables via perl? by Corion (Patriarch) on Feb 01, 2007 at 18:44 UTC
The easy way is, as always with MS Office automation, to record whatever you're doing with the macro recorder and then translate the created VB code to Perl.	[reply]
Re^4: How to create pivot tables via perl? by rafa (Novice) on Feb 01, 2007 at 19:55 UTC
Re^5: How to create pivot tables via perl? by Corion (Patriarch) on Feb 02, 2007 at 08:26 UTC
Re^3: How to create pivot tables via perl? by marto (Cardinal) on Feb 01, 2007 at 20:32 UTC
In addition to the fantastic advice Corion has provided for you, please check out the resources I link to in Re: Excel VB Controls. If you hit any problems post the code and the monks will do their best to help you. Cheers Martin	[reply]
Re^2: How to create pivot tables via perl? by smithers (Friar) on Feb 03, 2007 at 22:24 UTC
You might also consider placing your pivot code in the database in the form of a stored procedure. This centralizes your pivot logic and opens up additional options (i.e. google on pivot table for Oracle PL/SQL or SQL server s-procs, etc. and you will find ample examples).	[reply]
Re: How to create pivot tables via perl? by ww (Archbishop) on Feb 01, 2007 at 17:52 UTC
Welcome to the Monastery, rafa. Your observation that "`I am looking for a ...`" begs for a pointer on the topic of "looking for" -- Super Search -- and the more general suggestion that the Search box (in the PM bar, near the top of most pages) is a wonderfully facile prayerbook. As you might expect, upon reflection, and as Parson Marto's response above exemplifies, many questions confronting a Novice here have been asked and answered before. Finding those answers can often be much quicker than awaiting replies to an SoPW.	[reply]
Re: How to create pivot tables via perl? by bduggan (Pilgrim) on Feb 01, 2007 at 19:53 UTC
I've found Data::Table really handy for making pivot tables.	[reply]
Re: How to create pivot tables via perl? by bfdi533 (Friar) on Feb 02, 2007 at 19:31 UTC
I had to do something similar in the recent past and here are two links that might be of help with the actual rotation/pivot portion of your solution: using PDL->transpose on CSV file Transpose a matrix of chars	[reply]