Solved

convert excel file to text file using perl

Posted on 2011-09-22
8
827 Views
Last Modified: 2012-06-27
I want to simply convert an excel file line the one attached into a text file in a linux box using perl or shell script. When I do it in my mac or pc it doesn't convert correctly. Its leaves extra stuff besides just the data.
sample.xls
0
Comment
Question by:libertyforall2
8 Comments
 
LVL 12

Expert Comment

by:Amick
ID: 36584320
As simple as that file is, why not just save it as a .csv file from Excel or Open Office and be done?
If that doesn't fit your needs, there are many Excel readers available for Perl.  You might try
Spreadsheet::Read - a very simple example program is available at this link:
Spreadsheet::Read
0
 

Author Comment

by:libertyforall2
ID: 36584605
csv file wont work either. I need a straight txt file.
0
 
LVL 2
ID: 36585780
There are some perl modules that will read and extract data from a xls file.  A quick search on cpan.org comes up with a few.  A possible one to try would be:
http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel-0.59/lib/Spreadsheet/ParseExcel.pm

I have never used it myself but seems to have some good reviews on cpan.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 29

Expert Comment

by:gowflow
ID: 36585862
Hi Libertyforall2
Is this what your looking for ? Pls download this file and make sure your macros security settings are set to medium if your not familiar start blank Excel choose from the menu Tools/Macros/Security and make sure Medium is selected. Press on the Trusted Publisher tab in the same window and make sure both options Trust All installed addin and Trust Access to Visual Basic Projects are both ticked. Press OK and close Excel
1) Load the attach file and when prompt press Enable macros.
2) Press on Export to Text File button loacated on the top you should get a message telling that the file has been saved in the same location of where this workbook is located.
3) Check the file and see if all is ok.

Pls feel free to let me know your comments or any modification you may require.
gowflow
sample.xls
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36586438
When I run gowflow's solution on my English-UK windows Excel 2007 system, it produces a text file, but with a weird twist - the dates are all changed to dates in 2006, rather than 2010.  I think this is because it has the option set to use the 1904 date system rather - does it originate on a Mac?

Obvuously this may not be an issue for you, but I just wanted to point it out in case anyone else is seeing it.  Just exporting the file as a Windows tab delimited text file produces the correct dates for me, and the file is otherwise just the same as gowflow's - what was the extra stuff you were getting when you saved it this way?
0
 
LVL 26

Accepted Solution

by:
wilcoxon earned 500 total points
ID: 36587102
What exactly do you want the output to look like?  This will create a text file with each field separated by a space (since you said csv won't work, I assume tab-delimited also won't work).

You call it as:

script.pl xls_filename

and it will produce a text file with the same name with xls changed to txt.
#!/usr/local/bin/perl

use strict;
use warnings;
use Spreadsheet::ParseExcel;

my $fil = shift || die "Usage: $0 xls_filename\n";
my $out = $fil;
$out =~ s{\.xlsx?$}{.txt};
die "could not construct output filename ($out)" unless ($out and $out ne $fil);

my $parser = Spreadsheet::ParseExcel->new();
my $xls = $parser->parse($fil) or die $parser->error;
my $sheet = $xls->worksheet(0);
my ($row_min, $row_max) = $sheet->row_range;
my ($col_min, $col_max) = $sheet->col_range;

open OUT, '>', $out or die "could not write $out: $!";
for my $row ($row_min..$row_max) {
    my @data;
    for my $col ($col_min..$col_max) {
        my $cell = $sheet->get_cell($row, $col);
        push @data, defined($cell) ? $cell : '';
    }
    print OUT join(' ', @data), "\n";
}
close OUT;

Open in new window

0
 
LVL 9

Expert Comment

by:parparov
ID: 36588051
CSV is a TEXT file, as you've been told a few times before already.
0
 

Author Closing Comment

by:libertyforall2
ID: 36898439
Great!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question