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

hey guys,
does anyone know how to convert a excel file to a text file using win32::OLE on windows i've got a code below but it doesn't seem to work pls help?
use Win32::OLE::Const; use Win32::OLE::Const 'Microsoft Excel'; my $Excel = Win32::OLE->new("Excel.Application" , sub { $_[0]->Quit } +) or die Win32::OLE::LastError; $Excel->{Visible} = 1; my $Book = $Excel->Workbooks->Open("C:\\Documents and Settings\\clong\ +\Desktop\\Convert to Text file\\Quarantined_IMSIHLR2_20070611_Complet +ed1.xls") my $Sheet = $Book->Worksheets(Quarantined_IMSIHLR2_20070611_Completed1 +); $Excel->{DisplayAlerts} = 0; # avoid being prompted $Sheet->SaveAs( { FileName => "C:\\Documents and Settings\\clong\\Desk +top\\Convert to Text file\\test.txt" , FileFormat => xlText } +) or die Win32::OLE::LastError; $Book->Close(); $Excel->Quit;

Replies are listed 'Best First'.
Re: how to convert excel file to text file?
by GrandFather (Saint) on Mar 13, 2008 at 06:30 UTC

    What manner of "doesn't seem to work"? Generates an error? Generates a file, but not as you would like? Generates an empty file? Doesn't generate a file and doesn't generate an error? Something else?

    Update: your code (adjusted to use one of my spread sheets) works for me. Are you sure you have the paths and worksheet names correct? You do use strictures (use strict; use warnings;) don't you? You will get warnings if you have used the wrong worksheet name for example.


    Perl is environmentally friendly - it saves trees
      hi
      when i use perl, it doesn't save the info as text file, no text file appear and when i run it, if it doesn't work, the dos like screen just end the program by itself the path to the file i've check it a lot of times, and still it doesn't work

        You might like to modify the path in the following then see if it works as a sanity check:

        use strict; use warnings; use Win32::OLE::Const; use Win32::OLE::Const 'Microsoft Excel'; my $path = 'C:\Delme~~'; my $Excel = Win32::OLE->new ("Excel.Application", sub { $_[0]->Quit }) or die Win32::OLE::LastError; $Excel->{Visible} = 1; my $Book = $Excel->Workbooks->Add (); my $Sheet = $Book->Worksheets ('Sheet1'); $Sheet->Range ('A1')->{Value} = "Hello World"; $Sheet->Range ('A2')->{Value} = "Hi World"; $Excel->{DisplayAlerts} = 0; # avoid being prompted $Sheet->SaveAs ({FileName => "$path\\test.txt", FileFormat => xlText +}) or die Win32::OLE::LastError; $Book->Close (); $Excel->Quit; open IN, '<', "test.txt"; print <IN>; close IN;

        Prints:

        Hello World Hi World

        Perl is environmentally friendly - it saves trees
Re: how to convert excel file to text file?
by Tux (Canon) on Mar 13, 2008 at 15:19 UTC

    Your code will only work on windows. Microsoft Excel files have a well defined internal format, so Spreadsheet::ParseExcel can read them on any Operating system. Certainly when combined with Spreadsheet::Read, you will have a very portable and readable interface. Spreadsheet::Read comes with the xlscat tool, which enables you to convert Excel sheets to plain text or CSV.


    Enjoy, Have FUN! H.Merijn