http://qs321.pair.com?node_id=11124634


in reply to How is Perl for automation?

Most advices already passed should get you on your way, but I'd like to add one thing, as you explicitely mention Excel.

As I am confronted with an increasing customer demand of results being presented in Excel, I have found that generating the Excel should be the final step.

Don't see Excel as your (main) source for data manipulation. Read input from many sources (Excel, LibreOffice, CSV, JSON, YAML, XML, MS-SQL databases, PostgreSQL databases, Oracle databases, whatever ...) then use your business logic in perl, optionally store intermediate results in a local database like PostgreSQL or SQLite so small fixes can be done quickly without parsing/fetching all the sources again, and then generate the required output. Another big advantage of storing your work data in a local database is that the generation of the required outpout is much easier to test/change.

If Excel is requested, I prefer to export every sheet in CSV, and then use the tools available in Text::CSV_XS' examples, like csv2xlsx to convert and merge the data into an Excel file.


Enjoy, Have FUN! H.Merijn

Replies are listed 'Best First'.
Re^2: How is Perl for automation?
by Fletch (Bishop) on Dec 04, 2020 at 16:26 UTC

    Very important point. Keep in mind that spreadsheets are widely seen as risky by auditors and their ilk. At $work (large US bank) we've had a big initiative put in place to catalogue and manage the inventory of spreadsheets used because of the inherent risks. You can't (as easily) put the logic in a spreadsheet under SCM or subject it to a test suite like you can perl (or python, or R, or ...) which generates the underlying CSV that's displayed.

    The cake is a lie.
    The cake is a lie.
    The cake is a lie.

[reply]
[d/l]

      Hi,

      1) Thank you for making me aware of the risk issue. Another monk also offered solid advice on how to NOT use excel as source of data. You are all genuinely helpful folks.

      2) So, you are saying that Perl is used in a big bank? Even now? That is good!!

[reply]
        So, you are saying that Perl is used in a big bank? Even now? That is good!!

        I've heard very scary stories about IT in banks. Lots of legacy software (COBOL and the like) dating back to 1960s or 1970s, emulators in emulators, and a lot of pre- and post-processing around that to keep things working. "Never change a running system" codified in expensive and time-consuming recertification processes.

        Don't expect better from other businesses. I know of medical lab equipment running in production environment way into the 2000s based on a CP/M system. And even worse, a MUMPS system running the main business, both medical and financial, hacked togeter since the 1980s by a few unskilled people, in the same company, also way into the 2000s.

        You will find Perl in a lot of unexpected places over time. It's called swiss army chainsaw for a reason. I've used Perl in all of my jobs over the last two decades, and I will continue to do so. It does not solve all problems, but it solves a lot of small problems with very little effort, and the bigger ones with reasonable effort.

        Alexander

        --
        Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
Re^2: How is Perl for automation?
by fidodido (Sexton) on Dec 05, 2020 at 12:29 UTC

    Some coincidence this!! I was actually thinking of using Excel as source and write the data to another excel file which would have about 20 worksheets...Had it not been for your advice, I would not have known.

    I'm still far away from that level of scripting, but the guidance you have provided is worth it's weight in gold. Thank you.

[reply]

In Section Seekers of Perl Wisdom