Solved

convert excel file to text file using perl

Posted on 2011-09-22
8
643 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Utilizing an array to gracefully append to a list of EmailAddresses
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

707 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

17 Experts available now in Live!

Get 1:1 Help Now