After playing with your suggestions (and your code) I noted that your use of the "FileFormat" in...

$Book->SaveAs({Filename        => "F:\\Assess\\Assessment.csv", 
               FileFormat      => 6, #xlCSV, 
               CreateBackup    => 0});
[download]

Can you tell me where I might find the various options for "FileFormat" documented? The documentation installed with the module on my Windows Active State Perl installation isn't very comprehensive. The best piece of guidance I've found on this module was online at http://www.tek-tips.com/faqs.cfm?fid=6715.

After finding your second response (almost a month after you posted it), I revisited this project and extended it a little to correct for situations where users enter a 1 or 2 digit code where my accounting program will expect to find a three digit code with leading zeros. By formatting the range containing the codes before saving the file as CSV I can correct for that.

My current version (still a work in progress), here...

use strict;
use warnings;
use Win32::OLE;

my $fildir = "./";
opendir DIR, $fildir;
my @files = grep { /.xls/ } readdir(DIR);
closedir DIR;

foreach my $files (@files)     {
    chomp $files;

my $Excel=Win32::OLE->new('Excel.Application');
    $Excel->{Visible}=0;
    $Excel->{DisplayAlerts}=0;
    my $Book = $Excel->Workbooks->Open("C:\\Monks\\$files") or die "Ca
+n't open file"; 
    my $sheet = $Book -> Worksheets(1);
    $sheet -> Range ('A:A') -> {NumberFormat} = "000";

my $nmlng = length $files;
my $nwnam = substr ($files,0,($nmlng - 4));

$Book->SaveAs({Filename        => "C:\\Monks\\$nwnam.csv", 
               FileFormat      => 6, #xlCSV, 
               CreateBackup    => 0});

    unlink ($files);
$Excel->Quit;
                }
[download]

Typical excel data would look like:

Code,PropertyName,Unit Count,Unit Cost, Total Cost, TypeAbbreviation, 
+Comments
1,First Account,5,10.00,50.00,cod,User text here
002,"Second Account, The",10,20.00,200.00,cod,More narrative
003,Third Account,5,20.00,100.00,cod,Another comment
[download]

I've had no success finding a way to make the filepaths in the...

my $Book = $Excel->Workbooks->Open("C:\\Monks\\$files") or die "Can't 
+open file";
[download]

$Book->SaveAs({Filename        => "C:\\Monks\\$nwnam.csv",
[download]

In my many google searches, many people suggest creating VB Macros in Excel then converting them to comparable statements in Win32::Ole. However I've not had any luck finding any beginner's tutorials or samples of how to do that. If you can suggest a source or process, I'd appreciate it.

Thanks again. As a interested (but novice) Perl programmer, people like you and most of the other Monks who take the time to point the way are what keeps me from quitting in frustration.

OK, let's try to deal with your issues in order.

Can you tell me where I might find the various options for "FileFormat" documented? The documentation installed with the module on my Windows Active State Perl installation isn't very comprehensive.

This is one of my pet peeves. Thank you for stroking it. Yes, the documentation is not what it should be. The VBA constants are documented here. My nose tells me that there's some way of importing them to Perl as named constants, but I haven't looked for or stumbled on it as yet. However, converting them to their actual values is trivial - start Excel, bring up the IDE (Alt-F11), go to the Immediate pane (Ctrl-G) and type in ?xlcsv or whichever you want (VBA doesn't care about case). It will return the value.



...checks the current directory for any excel files and pulls them into an array.

I'm not ecstatic about this approach. Without suggesting that it won't work, I would tend to loop through the directory, checking each file in turn. The problem I have with your approach is that if the directory is shared, you are either blocking access to all files or risking a file being deleted or added between the start and end of the process. By handling files one at a time, the period in which this can happen is minimised.

Googling for '"current directory" perl' got me this - I'd be interested to hear from anyone if this is a Good Thing. It returns the current path in Unix format, i.e. with slashes, not backslashes. Your code must compensate. You will normally have to use double backslashes so that Perl understands that you mean a backslash and not some control character.



I've had no success finding a way to make the filepaths in the sections dynamic.

I think this is down to the slashes and backslashes again.
This gets me to the following code:

use strict;
use warnings;
use Cwd;
use Win32::OLE;

my $ext = ".xls";
my $newext = ".csv";

my $Excel=Win32::OLE->new('Excel.Application');
$Excel->{Visible}=1;
$Excel->{DisplayAlerts}=1;    #Set to 0 when the code is working, but 
+keep at 1 while debugging.

my $dir = getcwd;
$dir =~ s/\//\\\\/g;        #Sort out the slashes and backslashes
opendir(DIR, $dir) or die "can't opendir $dir: $!"; #Cookbook recipe 9
+.5
while (defined(my $file = readdir(DIR))) {
     if ((!-d $file) and (substr($file, -length($ext)) eq $ext)) {    
+#Someone WILL call a directory something.xls. I have the scars.
        my $Book = $Excel->Workbooks->Open("$dir\\\\$file") or die "Ca
+n't open file $dir\\\\$file"; 
        my $sheet = $Book -> Worksheets(1);
        $sheet -> Range ('A:A') -> {NumberFormat} = "000";
            
            #The next line is pretty explicit to try to make it clear 
+what's going on.
        $Book->SaveAs({Filename     => $dir . "\\\\" . substr($file, 0
+, length($file) - length($ext)) . $newext, 
                       FileFormat   => 6, #xlCSV, 
                       CreateBackup => 0});
        $Book->Close;
    }
}
closedir(DIR);
$Excel->Quit;
[download]

I haven't tested it rigorously - I don't want to make dozens of files I don't need, especially as I do use CSVs - but I have tested most parts of it, and I think it will do what you need. If I have read the substr docs correctly, it can be used to change the extension directly, but that's beyond (a) me, (b) the scope of this lecture. :-)

You mention needing the Perl and Excel files in the same directory. I don't find I need that. Provided the machine can find the Perl - either via the PATH or specified as part of the call on the command line - it will work no matter where the Perl code is. This I have tested.



In my many google searches, many people suggest creating VB Macros in Excel then converting them to comparable statements in Win32::Ole. However I've not had any luck finding any beginner's tutorials or samples of how to do that.

I'm not sure whether you mean writing VBA or converting it. As far as converting is concerned, this is the classic reference. For writing VBA, Googling "excel vba introduction" gives lots of places. I'm a Perl beginner but not an Excel beginner, so I can't really recommend any of them. "Excel $year Power Programming With VBA" by John Walkenbach is outstanding. It starts with baby steps and eventually gets seriously sophisticated.

Regards,

John Davies

Update: deleted redundant relic line from code.

(John) Davies,

Thanks for the suggested sources. I have collected and recorded the FileFormat codes after converting them as you suggested. I was also pleased with the link you provided that finally allowed me to understand how a VB "Macro" within Excel could be converted to Win32::OLE directives. Clearly your googling yielded better results than mine.

The relative file path/name issue was a little challenging. (My plan is to compile this script with Active State's PDK and let the user put the program in any directory they choose along with the source excel files.) I found that the method use to indicate "currently logged directory" in the file grep command would not work similarly within the Win32::OLE statements.

I settled on using Win32::OLE's GetFullPathName call to pull the full path hoping it would work better than the ./ prefix used for discoverng the excel files.

The $Book SaveAs Filename gave me some headeaches because one of my source files had spaces in the name. I tried applying quote marks around the pathname/filename without success. Eventually I settled for stripping out the spaces in the name before writing the file back as a CSV.

I wasn't able to get your program to work, but am fairly satisfied with the current state of mine.

use strict;
use warnings;
use Win32::OLE;

my $fildir = "./";
opendir DIR, $fildir;
my @files = grep { /.xls/ } readdir(DIR);
closedir DIR;
foreach my $files (@files)     {
    chomp $files;

my $pathfile = Win32::GetFullPathName($files);
my $adbspath = $pathfile;
$adbspath =~ s/\\/\\\\/g;

my $Excel=Win32::OLE->new('Excel.Application');
    $Excel->{Visible}=0;
    $Excel->{DisplayAlerts}=0;
    my $Book = $Excel->Workbooks->Open("$adbspath") or die "Can't open
+ file"; 
    my $sheet = $Book -> Worksheets(1);
    $sheet -> Range ('A:A') -> {NumberFormat} = "000";

my $nmlng = length $pathfile;
my $till = rindex ($pathfile,".xls",$nmlng);
my $nwnam = substr ($pathfile,0,$till);
# remove spaces from file name to facilitate saveas filename
$nwnam =~ s/ //g;
my $qnwnam = "\"".$nwnam.".csv\"";
print "$qnwnam \n";
$Book->SaveAs({Filename        => "$nwnam.csv", 
               FileFormat      => 6, #xlCSV, 
               CreateBackup    => 0});
#    unlink ($files);
$Excel->Quit;
                }
[download]

Again thanks for the answers. I'm feeling a whole lot better about the future opportunities to convert excel spreadsheets to usable data import files.
<><

generator