Solved

convert excel file to text file using perl

Posted on 2011-09-22
8
694 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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

948 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now