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

Hi Perl Monks,

I am a new Perl user and am trying to save a Excel file as a tab delimeted text file. When I run the following script it creates a .csv or .txt file (depending on the output file name), but it is still in a binary format. 

                                                                      
+                                                                     
+                       
#should save C:\\work\\perl_WinOle\\sym_text_testdoc.xls as
#       a text or csv formatted document.
#    creates a file with .txt or csv extension, but
#       document is not a tab or comma delimeted text file.

[download]

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 

use Win32;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
#$file = "C:\\work\\perl_WinOle\\test.csv";
$OutFile = "C:\\work\\perl_WinOle\\sym_text_testdoc.txt";
my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
my $xl = Win32::OLE::Const->Load($Excel);
$Excel->{'Visible'} = 1;         # if you want to see what's going on

my $Book = $Excel->Workbooks->Open( "C:\\work\\perl_WinOle\\sym_text_t
+estdoc.xls" );

$Book->Activate();
$Book->SaveAs($OutFile);
$Book->Close;

[download]

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXxxx

I found this snippet of code that saves a Word document as a text file: $document->SaveAs($txt, wdFormatText);

I assume that Excel would be somthing like: $book->SaveAs($txt, excelFormatText); excelFormatText probably should be something different. Does anyone know how to save an Excel file as a text delimeted text file?

Sorry about the format of my message, my question I entered does have spaces and lines for clarity, don't know why they don't exist in the examle posted message

Thanks, Steveb94553

20080601 Janitored by Corion: Added formatting, code tags, as per Writeup Formatting Tips

Replies are listed 'Best First'.
Re: Saving Excel file in Tab delimeted text or .csv
by graff (Chancellor) on May 30, 2008 at 00:52 UTC
    I don't know about using the "Win32" modules (I'm not a M$-Windows user), but I happen to have a nice, general purpose utility that I whipped up a while back -- I just posted it here at the Monastery: xls2tsv.

    The cpan:://Spreadsheet::ParseExcel module makes for quite a nice approach, and it works on all OS's (not just windows), which is a huge factor for me. It's also possible, using that module, to keep track of things like font styles (bold, italic, underline) and colors in cell values, in case that's something that matters to you.

[reply]
Re: Saving Excel file in Tab delimeted text or .csv
by NetWallah (Canon) on May 29, 2008 at 23:51 UTC
    You are probably looking for xlCSV (Value=6) (Other options in this MSDN article).

    The syntax for the SaveAs method is 

      $book->SaveAs(FileName, FileFormat, Password, WriteResPassword, Read
+OnlyRecommended
                ,CreateBackup, AddToMru, TextCodepage, TextVisualLayou
+t, Local);

    [download]
    Here is the relevant page in the MSDN Excel 2007 doc.

    Also, please use <code> tags when you display code, as documented in the writeup formatting tips.

         "A fanatic is one who redoubles his effort when he has forgotten his aim."—George Santayana

[reply]
[d/l]
      Thanks for your feedback, this helped me save my .xls file as a tab
delimited text file. The link to the MSDN Excel 2007 doc is very
helpful. Sorry about the formatting, let me know if there are any more
issues.
I also would like to thank graff and cbu for their reply.
After spending upwards of three days trying to figure this out,
I should have asked a question sooner. Hopefully I can become
proficient at Perl enough to help others.
A modest donation has been sent to the Perl Monks.

I did run into an issue where a dialog prompt comes up asking if I
want to save the .txt file. I remembered another question I came
across while trying to solve the format issue and was able to leverage
off of it to eliminate the dialog prompt by adding
"$Excel->{DisplayAlerts} = "False";" to the script.

<s
#    Saves C:\\work\\perl_WinOle\\sym_text_testdoc.xls as a text or
#    MSDOS text formatted document. (Tab delimited)

use Win32;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
#$file = "C:\\work\\perl_WinOle\\test.csv";
$OutFile = "C:\\work\\perl_WinOle\\sym_text_testdoc.txt";
my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
my $xl = Win32::OLE::Const->Load($Excel);
#$Excel->{'Visible'} = 1;         # if you want to see what's going on

my $Book = $Excel->Workbooks->Open( "C:\\work\\perl_WinOle\\sym_text_t
+estdoc.xls" );

$Book->Activate();
$Excel->{DisplayAlerts} = "False";
$Book->SaveAs($OutFile, xlTextMSDOS);
#$Excel->{DisplayAlerts} = "False";
$Book->Close();
$Excel->Quit();
e>

Thanks for your help and have a great weekend.
Steve Bell

      [download]
[reply]
[d/l]
        Thanks for using <code> tags. However, these should only be around the actual CODE posted. Your text comments should be outside the tags.

        Regarding the DisplayAlerts attribute, I suggest you set it this way: 

        my $FALSE         = 0;
my $TRUE          = ! $FALSE;

$Excel->{DisplayAlerts} =  $FALSE;

        [download]
        Alternatively, you could use the Readonly module for the constants, or use literal values 0 and 1.

             "A fanatic is one who redoubles his effort when he has forgotten his aim."—George Santayana

[reply]
[d/l]
Re: Saving Excel file in Tab delimeted text or .csv
by Tux (Canon) on May 30, 2008 at 07:09 UTC

    My Spreadsheet::Read module comes with 'xlscat', which can export CSV with delimiters of choice.


    Enjoy, Have FUN! H.Merijn
[reply]
Re: Saving Excel file in Tab delimeted text or .csv
by steveb94553 (Initiate) on May 30, 2008 at 17:33 UTC 
    Thanks for your feedback, this helped me save my .xls file as a tab
delimited text file. The link to the MSDN Excel 2007 doc is very
helpful. Sorry about the formatting, let me know if there are any more
issues.
I also would like to thank graff and cbu for their reply.
After spending upwards of three days trying to figure this out,
I should have asked a question sooner. Hopefully I can become
proficient at Perl enough to help others.
A modest donation has been sent to the Perl Monks.

I did run into an issue where a dialog prompt comes up asking if I
want to save the .txt file. I remembered another question I came
across while trying to solve the format issue and was able to leverage
off of it to eliminate the dialog prompt by adding
"$Excel->{DisplayAlerts} = "False";" to the script.

<s
#    Saves C:\\work\\perl_WinOle\\sym_text_testdoc.xls as a text or
#    MSDOS text formatted document. (Tab delimited)

use Win32;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
#$file = "C:\\work\\perl_WinOle\\test.csv";
$OutFile = "C:\\work\\perl_WinOle\\sym_text_testdoc.txt";
my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
my $xl = Win32::OLE::Const->Load($Excel);
#$Excel->{'Visible'} = 1;         # if you want to see what's going on

my $Book = $Excel->Workbooks->Open( "C:\\work\\perl_WinOle\\sym_text_t
+estdoc.xls" );

$Book->Activate();
$Excel->{DisplayAlerts} = "False";
$Book->SaveAs($OutFile, xlTextMSDOS);
#$Excel->{DisplayAlerts} = "False";
$Book->Close();
$Excel->Quit();
e>

Thanks for your help and have a great weekend.
Steve Bell

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom