Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1368
  • Last Modified:

convert excel file to text file using perl

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
libertyforall2
Asked:
libertyforall2
1 Solution
 
AmickCommented:
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
 
libertyforall2Author Commented:
csv file wont work either. I need a straight txt file.
0
 
Anthropomorphic_PersonificationCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
gowflowCommented:
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
 
andrewssd3Commented:
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
 
wilcoxonCommented:
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
 
parparovCommented:
CSV is a TEXT file, as you've been told a few times before already.
0
 
libertyforall2Author Commented:
Great!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now