Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

PERL csv files to Excel

by slick.user (Novice)
on Mar 13, 2023 at 17:56 UTC ( #11150962=perlquestion: print w/replies, xml ) Need Help??

slick.user has asked for the wisdom of the Perl Monks concerning the following question:

Hi all, I was using PERL Win32 OLE to open multiples csv files and save as 1 Excel file. This was quick and fast.
use Win32::OLE; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel';
I just switch to a new system and I don't have Excel 32-bit on this system (only 64-bit). I cannot use Win32::OLE libraries any more. Is my next bet is to use:
use Text::CSV; use Spreadsheet::WriteExcel;
Open each csv and write to Excel file? Thanks.

Replies are listed 'Best First'.
Re: PERL csv files to Excel
by Tux (Canon) on Mar 14, 2023 at 08:30 UTC
Re: PERL csv files to Excel
by Corion (Patriarch) on Mar 13, 2023 at 18:05 UTC

    You should still be able to use Win32 OLE together with a 64bit Office. Maybe switchting to a 64bit Perl like Strawberry Perl helps?

      Below is the error I got. I'm on Strawberry Perl x64 bit. Turns out I need to remove use Win32::OLE::Const 'Microsoft Excel';.
      use strict; use warnings; use Win32::OLE; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; my $EXCEL = Win32::OLE->new("Excel.Application","Quit"); $EXCEL->{"Visible"} = 1; $EXCEL->{DisplayAlerts}= 0; $EXCEL -> {"SheetsInNewWorkBook"} = 4; my $Workbook = $EXCEL -> Workbooks -> Add();
      This is perl 5, version 32, subversion 1 (v5.32.1) built for MSWin32-x64-multi-thread Microsoft® Excel® for Microsoft 365 MSO (Version 2208 Build 16.0.15601.20526) 64-bit No type library matching "Microsoft Excel" found at line 5. Win32::OLE(0.1712): GetOleTypeLibObject() Not a Win32::OLE::TypeLib object at C:/Strawberry/perl/vendor/lib/Win32/OLE/ line 49.
Re: PERL csv files to Excel
by Bod (Vicar) on Mar 14, 2023 at 16:44 UTC

    I use Spreadsheet::WriteExcel to write Excel spreadsheets occasionally. This is on 64-bit Office365 and Strawberry Perl.
    Spreadsheet::Read is used daily here without any issues.

    Neither require me to use OLE modules.

Re: PERL csv files to Excel
by harangzsolt33 (Hermit) on Mar 25, 2023 at 07:01 UTC
    Okay, I know you have asked this question several days ago, and maybe you have already found a solution to it. But it inspired me to write a little program that performs this conversion. What's unique about this Perl script is that it does not rely on any Perl modules or external libraries or inline C code. It doesn't even require you to have Windows or Office installed at all. Here is the program:

    By the way, it converts one CSV file to one XLSX Excel file, but converting multiple CSV files into one Excel file should be relatively easy with only some slight tweaking.

      "What's unique about this Perl script is that it does not rely on any Perl modules ..."

      The first three lines of code:

      use 5.004; use strict; use warnings;


      $ corelist warnings Data for 2022-05-27 warnings was first released with perl v5.6.0

      So, clearly this will not work with Perl 5.004. If you're going to assert a minimum version, ensure you have tested with that version.

      On an unrelated note, please stop messing with colours. I'm pretty sure that you've been pointed to "Writeup Formatting Tips" in the past; if not, or if you didn't read it, please do read it, paying particular attention to the Don't get carried away section (near the end). What it says there about <font> tags applies equally to bgcolor attributes. Putting your <code> content inside a <table> with BGCOLOR=66CCDD is not a clever way to exploit what perhaps you considered a loophole.

      If you want to see fancy colours when viewing code, go to your Display Settings and set up appropriate CSS. Don't force your preferences on others.

      — Ken

        Okay, I removed the colors.

        Yes, I know. To make it work, I added a dummy file into the lib directory of perl called and it has a number 1 in it. So, that way I can leave the warnings in it yet will work on old Perl! Yes, maybe I should clarify that. Right? But I figured that the number of people who would need that advice is really small. I could probably count on one hand how many will run this code using an old perl interpreter. And of course, when they try it, it's going to say that there is no such thing us Now, at this point, I hope these guys will use their brilliant mind to either remove the first line where warnings is mentioned or they'll add a fake file like I have.

      You've done a really bad job at trying to deliver one thing, badly reinventing wheels. Why would a non-masochist use anything like this?
        I don't know. The goal is to get an XLSX file. Is it not? This thing does the job right. In the next version, I may add an LZW compressor written in pure Perl to deliver smaller content. :-)

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11150962]
Approved by davies
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (4)
As of 2023-06-08 22:56 GMT
Find Nodes?
    Voting Booth?
    How often do you go to conferences?

    Results (35 votes). Check out past polls.