Extract Data from Excel-files

[Benjamin Walkenhorst]

Hello everyone,

Finally I get to use perl at work! =)
I am to facing the following problem:

There is a folder on a file-server in our network which contains 60 - 70
subfolders. Each of these subfolders contains a number of Excel-files
(.xls) ranging from zero to maybe five.
Each of these files again contains several worksheets, from which I am
to extract certain cells (these are at fixed positions) and somehow put
these to some nice output format (csv, html, maybe an SQL-database).

As of now, I am more concerned about extracting the data.
There is a module, Spreadsheet::ParseExcel, which I've tried out, and it
seems to work. But the documentation says it can not handle all versions
of excel.
I am unsure what versions of Excel were used creating these files,
probably Excel 2000 and XP.
I'm going to try Spreadsheet::ParseExcel first, but if it does not work
- what other ways to access Excel-files are there? What reasons could
you think of to prefer one way over the other(s)?

Thanks for your suggestions,

kind regards,

Benjamin

[Shaun Bramley]

Ben,

I have been able to use Spreadsheet::ParseExcel with 2000 and XP XL
files. I'm not an Excel expert but if I am not mistaken 2000 and XP use
the same file structure. The complications might arise with XP 2003, or
97 files. A simple work around would be to open/close all spreadsheets
with one version of Excel. For a definite answer I would recommend that
you send an email to the modules author (I forget his name) but I found
him to be pleasant and responsive when I was using the module and had
questions.

Shaun


-----Original Message-----
From: Benjamin Walkenhorst [mailto:krylon@gmx.net]
Sent: Sunday, February 15, 2004 5:25 AM
To: [email]beginners@perl.org[/email]
Subject: Extract Data from Excel-files


Hello everyone,

Finally I get to use perl at work! =)
I am to facing the following problem:

There is a folder on a file-server in our network which contains 60 - 70
subfolders. Each of these subfolders contains a number of Excel-files
(.xls) ranging from zero to maybe five.
Each of these files again contains several worksheets, from which I am
to extract certain cells (these are at fixed positions) and somehow put
these to some nice output format (csv, html, maybe an SQL-database).

As of now, I am more concerned about extracting the data.
There is a module, Spreadsheet::ParseExcel, which I've tried out, and it
seems to work. But the documentation says it can not handle all versions
of excel. I am unsure what versions of Excel were used creating these
files, probably Excel 2000 and XP. I'm going to try
Spreadsheet::ParseExcel first, but if it does not work
- what other ways to access Excel-files are there? What reasons could
you think of to prefer one way over the other(s)?

Thanks for your suggestions,

kind regards,

Benjamin

--
To unsubscribe, e-mail: [email]beginners-unsubscribe@perl.org[/email]
For additional commands, e-mail: [email]beginners-help@perl.org[/email]
<http://learn.perl.org/> <http://learn.perl.org/first-response>

[Benjamin Walkenhorst]

Hello,

On Sun, 15 Feb 2004 10:18:56 -0500
"Shaun Bramley" <s_bramley@hotmail.com> wrote:

> Ben,
>
> I have been able to use Spreadsheet::ParseExcel with 2000 and XP XL
> files. I'm not an Excel expert but if I am not mistaken 2000 and XP
> use the same file structure. The complications might arise with XP
> 2003, or 97 files. A simple work around would be to open/close all
> spreadsheets with one version of Excel. For a definite answer I would
> recommend that you send an email to the modules author (I forget his
> name) but I found him to be pleasant and responsive when I was using
> the module and had questions.
>
> Shaun

Thank you very much, that sounds very encouraging! =)
I'm just *so* glad I don't have to learn VBA or something like that...
Being able to use perl for this task fits in really nice, because I have
been using perl quite a lot on my local machines recently.

Kind regards,
Benjamin

[R. Joseph Newton]

Benjamin Walkenhorst wrote:

> Hello everyone,
>
> Finally I get to use perl at work! =)
> I am to facing the following problem:
>
> There is a folder on a file-server in our network which contains 60 - 70
> subfolders. Each of these subfolders contains a number of Excel-files
> (.xls) ranging from zero to maybe five.
> Each of these files again contains several worksheets, from which I am
> to extract certain cells (these are at fixed positions) and somehow put
> these to some nice output format (csv, html, maybe an SQL-database).
>
> As of now, I am more concerned about extracting the data.
> There is a module, Spreadsheet::ParseExcel, which I've tried out, and it
> seems to work. But the documentation says it can not handle all versions
> of excel.
> I am unsure what versions of Excel were used creating these files,
> probably Excel 2000 and XP.
> I'm going to try Spreadsheet::ParseExcel first, but if it does not work
> - what other ways to access Excel-files are there? What reasons could
> you think of to prefer one way over the other(s)?
>
> Thanks for your suggestions,
>
> kind regards,
>
> Benjamin

It is probably blasphemous to say this here, but if I was facing a mass of
Excel, or any other M$ Office files, I would just use VB. It has the advantage
of placement. It has immediate access to the built-in functionality. Here is
the VB for a single file open and resave as CSV:

Sub Macro2()
'
Workbooks.Open Filename:="E:\d_drive\job\Lab Software.xls"
ActiveWorkbook.SaveAs Filename:="E:\d_drive\job\Lab Software.csv", _
FileFormat:=xlCSV, CreateBackup:=False
End Sub

When you think out the logic for how to collect the input and output filenames
and paths, that should be fairly straightforward.

I got the above syntax by recording a macro and then opening the macro widow,
and choosing Edit. Toss in some logic for filename collection and looping, and
then it just takes a keypress.

Then you can use Perl to prodess the SCV output, since this is a task at which
Perl excels.

Joseph

Joseph