OLE Excel file saveAs comma delimited

goBoating · Sep 25, 2002

WinXP Professional
MicroSoft Excel 2002 SP-1
ActivePerl 5.6.1 from activeState build 633

I am trying to use Perl to do the following:
1 - open an excel spreadsheet
2 - save the spreadsheet as a comma delimited file
3 - parse the csv file for certain info
4 - send appropriate emails based on that parsed info

Number 1 works. I can open/read the file contents.
Number 3 is trivial with a little splitting and regex's.
Number 4 is trivial with Mail::Sender.

I'm having trouble with Number 2. I can't get the thing to save to anything other than the Excel file format. I really want to put the content of the Excel file into a more easily
manipulated format.

The following chunk works without a the FileFormat ttributte.
It will save to the indicated name, but since the format is ommitted, it saves as an Excel file, not comma delimitted.

Code:

unless ($book->SaveAs({ 
    Filename =>'C:\path\to\projects\data2.csv',
    CreateBackup =>'False' } ))
     { warn &quot;Failed to save data2.csv, $!\n&quot;; }
[code]
I'd like to get the fileFormat attribute to work... something like...
[code]
my $format = 'xlCSVMSDOS';
unless ($book->SaveAs({ 
    Filename =>'C:\path\to\projects\data2.csv',
    Fileformat =>$format,
    CreateBackup =>'False' } ))
     {
     warn &quot;Failed to save data2.csv, $!\n&quot;;
    }
[code]

Anybody have any idea what is wrong here?
Or, a better approach to solving this problem.  Yeah, I know VB is the standard MicroSoft tool for this stuff.  However, I'm sure that if I can get the Excel  file contents into an easily handled form, the parsing and email tricks are trivial with Perl while they are not with VB.

Here is the enitre open...saveAs chunk.
[code]
#!perl -w
use strict;
use Win32::OLE;

my ($app, $xlfile, $book);
$app = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
    or die &quot;Oops, cannot start Excel&quot;;

$xlfile = 'c:\path\to\simple\excel_file\data.xls';
$book = $app->Workbooks->Open({FileName => $xlfile, ReadOnly=>1});    

my $sheet = $book->Worksheets(1);
# get a ref to the range that has data
# print some data to show that we did, in fact
# get the correct file open
my $array = $sheet->Range(&quot;A1:F3&quot;)->{Value};
        for (@$array) {
            for (@$_) {
                print defined($_) ? &quot;$_|&quot; : &quot;<undef>|&quot;;
            }
            print &quot;\n&quot;;
        }

# save as comma delimited
my $format = 'xlCSVMSDOS';
unless ($book->SaveAs({ Filename =>'C:\path\to\projects\OLE\data2.csv',
                        Fileformat =>$format,
                        CreateBackup =>'False' } ))
    {
    warn &quot;Failed to save data2.csv, $!\n&quot;;
    }

# 
$book -> Close();
$app -> Quit();

#_________________________________________________________________________
# VB macro saved from inside EXCEL
# Sub saveMe()
# '
# '  saveMe Macro
# ' Macro recorded 9/25/2002 by julmer
# '
# '
#     ActiveWorkbook.SaveAs Filename:=&quot;C:\path\to\simple\excel_file\data.csv&quot;, _
#         FileFormat:=xlCSVMSDOS, CreateBackup:=False
# End Sub

'hope this helps

If you are new to Tek-Tips, please use descriptive titles, check the FAQs, and beware the evil typo.

PaulTEG · Jun 15, 2003

FileFormat perhaps?
Paul

It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...

Haunter · Jun 15, 2003

goBoating,

Check this!

NAME
DBD::Excel - A class for DBI drivers that act on Excel File.

DESCRIPTION
This module is still ALPHA VERSION!

The DBD::Excel module is a DBI driver.
This module enables you to manipulate Excel files with SQL via DBI.

This module can handle files of Excel95, 97 and 2000.

The module will work on the majority of Windows, UNIX and
Macintosh platforms.

REQUIREMENT
This module requires these modules:
Spreadsheet:

arseExcel
Spreadsheet::WriteExcel
DBI
SQL::Statement

INSTALLATION
The module can be installed using the standard Perl procedure:

haunter@battlestrata.com

goBoating · Jun 15, 2003

Cool - Thanks, both of you.

'hope this helps

If you are new to Tek-Tips, please use descriptive titles, check the FAQs, and beware the evil typo.

sidphanse · May 8, 2005

goboating,

I am new to using Win32:

LE and I am having the same problem. However, it isn't clear how and if you were to fix the issue based on the issue.

Can someone please explain?

Thanks,
Sid.

goBoating · May 9, 2005

This is what I ended up with. It could likely be much more concise, but, once I got it working, I went on to my next chore. Because we thought we might convert this code to poke the values into an RDBMS, I set this up to parse the values out of the spreadsheet and insert them into a comma delimitted file using the DBI and DBD::CSV. That way, all I had to do was tweak a connection string to point to the RDBMS when/if it was needed.

Code:

sub convert_to_csv
{
# start Excel via OLE
my $app = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;}) or die "Oops, cannot start Excel";

# open the retrieved file
my $xlfile = 'C:\projects\dev_tickle_2\tmp.XLS';
my $book = $app->Workbooks->Open({FileName => $xlfile, ReadOnly=>1});
#_________________________________________________________________________

# my $ws = $book->Worksheets('Deliverables');
my $ws = $book->Worksheets('sheet2');
for (1..5000) # probably not more than 1000 milestones and/or deliverables
	{
	my $row_num = $_;
	if (defined $ws->range('a'.$row_num)->Value)
		{
		my $c1 = 'a'.$row_num;
		my $c2 = 'z'.$row_num;
		my $row_array = $ws->Range("$c1:$c2")->{Value};
        for (@$row_array) # a row of fields
        	{
			# parse row
			#    0         1           2          3         4             5            6        7              8             9      10         11               12              13     14        15     16              17                   18                  19     20
			my ($pip_num, $pip_title, $task_num, $delv_num, $event_title, $start_date, $ms_due, $ms_submitted, $ms_accepted, undef, $delv_due, $delv_submitted, $delv_accepted, undef, $sow_due, undef, $addendum_desc, $addendum_submitted, $addendum_approval, undef, $psgs_mngr, $psgs_proj_lead, $prog_mngr, $fed_proj_lead) = @$_;
			# debug # unless ($psgs_mngr =~ /\w/) { die "No psgs_mngr: $psgs_mngr\n"; }
			my ($event_type, $psgs_due, $submit_date, $accept_date);
        	unless ($$_[0] =~ /^\d+$/) { next; } # skip if first cell is not a number (like a pip number)

			if ($delv_num =~ /\d/)  # event is a deliverable
				{
				$event_type 	= 'Deliverable';
				$psgs_due 		= $delv_due;
				$submit_date 	= $delv_submitted;
				$accept_date	= $delv_accepted;
				}
			else 					# event is a MILESTONE
				{
				$event_type 	= 'Milestone';
				$psgs_due 		= $ms_due;
				$submit_date 	= $ms_submitted;
				$accept_date	= $ms_accepted;
				}
			# CSV Header
			# pip_num, pip_title, psgs_mngr, psgs_proj_lead, prog_mngr, fed_proj_lead, task_num, event_title, event_type, psgs_due, submit_date, accept_date, sow_due
			$dbh->do("INSERT INTO pips VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", undef, $pip_num, $pip_title, $psgs_mngr, $psgs_proj_lead, $prog_mngr, $fed_proj_lead, $task_num, $delv_num, $event_title, $event_type, $psgs_due, $submit_date, $accept_date, $sow_due);
   			}
   		}
   	}
# In order to prevent the user dialog to Save the Workbook,
# set the property to saved. Then, Close and Quit.
$book->{Saved} = '1';
$book ->Close('False');	# close the workbook
$app ->Quit();			# close Excel
print "\n";
}

'hope this helps

If you are new to Tek-Tips, please use descriptive titles, check the FAQs, and beware the evil typo.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

OLE Excel file saveAs comma delimited

goBoating

Programmer

PaulTEG

Technical User

Haunter

Programmer

goBoating

Programmer

sidphanse

Programmer

goBoating

Programmer

Similar threads

Part and Inventory Search

Sponsor