Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

Re: Win32::OLE Excel search and replace commas

by davies (Parson)
on Oct 11, 2010 at 09:34 UTC ( #864569=note: print w/replies, xml ) Need Help??

in reply to Win32::OLE Excel search and replace commas

It's not quite that simple. Excel, at least in English-based locales, uses commas to separate formula options. If you try to replace commas with nothing, Excel will stop at the first critical comma in a formula and complain. Non-critical commas will probably result in wrong results or #NAME. Therefore, if you are in such a locale, you have two options that are immediately obvious to me. One is to change locale to something that uses a different character (I think France uses semicolons, for example). The other is to copy everything and Paste Special Values. Then you can use the usual Excel find & replace tool, Ctrl-H, before writing the CSV.

An option here is to use a special character, something you wouldn't expect to see anywhere. Then, once you have imported the data into Perl, you can replace every special character with a comma and get back to the embedded commas which are, I suspect, what you are having trouble with. There's an interesting thread on CSVs still active at problems parsing CSV.


John Davies
  • Comment on Re: Win32::OLE Excel search and replace commas

Replies are listed 'Best First'.
Re^2: Win32::OLE Excel search and replace commas
by generator (Pilgrim) on Oct 11, 2010 at 22:34 UTC
    Thanks for the feedback. I didn't find a way to run a search and replace using OLE. (I was attempting to replicate the manual steps I took, while manually editing these files).

    I did, however, find a solution. I extracted the cell values then ran a substitute =~ s/,//; on the scalar value before writing it to a text file, followed by a comma.

    #!c:/perl/bin/perl.exe use strict; use warnings; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; open (OUTPUT,">>","c:/assess/output.txt"); my $Excel = Win32::OLE->GetActiveObject ('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); my $Book = $Excel->Workbooks->Open ("C:/assess/assessment.xls"); my $Sheet = $Book->Worksheets(1); my $LastRow = $Sheet->UsedRange->Find({What=>"*", SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row}; my $array = $Sheet->Range("B2:"."F".$LastRow)->{'Value'}; foreach my $ref_array (@$array) { foreach my $scalar (@$ref_array) { $scalar =~ s/,//; print OUTPUT "$scalar,"; } print OUTPUT "\n"; } $Book->Close;

    Your warning regarding the use of commas in formulas was wise but in this case irrelevant. The subsequent processing used only integer values associated with unit cost, unit rate and account code (text string). All fields with formulas are ignored.

    Thanks again for taking the time to reply. It ain't pretty but it will do the job.



      Ah, the joys of having code to look at. You're going to a lot of trouble to generate the CSV, when Excel will do it for you automatically.
      use strict; use warnings; use Win32::OLE; my $Excel=Win32::OLE->new('Excel.Application'); $Excel->{Visible}=1; $Excel->{DisplayAlerts}=0; my $Book = $Excel->Workbooks->Open("F:\\assess\\assessment.xls") or di +e "Can't open file"; $Book->SaveAs({Filename => "F:\\Assess\\Assessment.csv", FileFormat => 6, #xlCSV, CreateBackup => 0}); $Excel->Quit;
      A few points. First, indenting code within if blocks, loops etc. will make it easier for you to understand what your code is doing in a few months' time. Second, I commented in Re^3: Win32::Ole excel external data range on both with and relying on an existing instance of Excel. I suspect both you and the other OP copied code from the same place. Third, the thread I mentioned earlier describes dealing with embedded commas and quotes, but the general rule for CSVs is that you can embed a comma in quotes. The output of my test file is:
      1,2,3,4 2,"Who, What?",4,5 3,4,5,6 4,5,6,7
      If, despite this, you really need to get rid of commas, the following code will remove commas from text:
      use strict; use warnings; use Win32::OLE; my $Excel=Win32::OLE->new('Excel.Application'); $Excel->{Visible}=1; my $Book = $Excel->Workbooks->Open("F:\\assess\\Lorem.xls") or die "Ca +n't open file"; $Excel->Cells->Replace({ What => ",", Replacement => ""});
      This won't get rid of formatting commas in numbers. To do that, you will need to change the formats. But from your code, you're looking at the underlying numbers anyway, rather than printing a file.
      End of update


      John Davies
        Thanks again for sticking with me on this. You are right about my taking the "long way" around to get my CSV file. As I stated in my original post I was trying to replicate the steps I took manually to accomplsh the same thing. Until your post, I could not find how to replicate the search and replace finding commas and replacing with nothing

        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});
        ...cleaned up any (text) fields with commas in them -- my biggest problem.

        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

        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; }
        ...checks the current directory for any excel files and pulls them into an array. Then each entry in that array is pushed through the routine you provided and the export file is like named with the CSV extant.

        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

        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";
        $Book->SaveAs({Filename => "C:\\Monks\\$nwnam.csv",
        ...sections dynamic. I tried "$files", "./$files" and "$fildir/$nwnam.csv" without success. Any ideas? It would be great if I could use the same code no matter where the user chooses to put the files as long at the compiled perl program is in the same directory with the excel workbooks.

        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.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://864569]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others lurking in the Monastery: (4)
As of 2018-02-25 00:36 GMT
Find Nodes?
    Voting Booth?
    When it is dark outside I am happiest to see ...

    Results (312 votes). Check out past polls.