Solved

convert excel file to text file using perl

Posted on 2011-09-22
8
876 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 30

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

734 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