Spreadsheet::WriteExcel must be available in perl lib, before running this OR you can set your PERL5LIB environment variable to point to your local lib where you have Spreadsheet::WriteExcel
#!/usr/local/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
if($#ARGV ne 1)
{
print "\n Usage: txt2xls \n Example: txt2xls \"|\" *.psv\n\n";
}
my $token;
my $file;
my $del;
my $wb;
my @files = @ARGV[1..$#ARGV];
foreach $file (@files){
open (TXTFILE, "$file") or die;
my $wb = Spreadsheet::WriteExcel->new("$file.xls");
my $excel = $wb->addworksheet();
my $row = 0;
my $col;
while (<TXTFILE>) {
chomp;
if ($ARGV[0] =~ /\|/)
{
$del="\\|";
}
else
{
$del = $ARGV[0];
}
my @Fld = split(/$del/, $_);
$col = 0;
foreach $token (@Fld) {
$excel->write($row, $col, $token);
$col++;
}
$row++;
}
}
##########################################################
How to run
copy the contents from start script to end script and place in a file named txt2xls
$ chmod a+x txt2xls
$ copy the txt2xls to bin
$ txt2xls "|" filename
$ ls
filename filename.xls
10 comments:
Hi,
Thanks for your code. Can you also post how to convert text files to excel using shell script?
Thnx,
Joe
For work with excel files advise try-how to recover data in Excel spreadsheet after you save,as far as i know software is free,it can repair corrupted Microsoft Excel worksheets,restore data after problems with corrupted worksheet problem as a result of virus attack, disk and network failures or any other problem,an scan a damaged Microsoft Excel worksheet and repair it,work with *.xls, *.xlam, *.xltx, *.xlt and *.xlsm formats,recover modified Excel worksheets and shows recovered data in the same way, like it will be displayed in Microsoft Excel worksheet.
hello this program didnt work what i should do exactly where i should copy this file which bin and can you help me step by step if you please thanks im waitng a reply at al88ch@gmail.com || al88ch@hotmail.com
Do you have copy writer for so good articles? If so please give me contacts, because this really rocks! :)
Very good, simple example. Thanks. Note for cygwin users...when installing modules from cpan, get ready to learn to use the rebaseall command. Nothing is simple anymore.
May the golden eggs from a 1000 golden egg laying geese cover your lawn. This saved me a ton of mental gruntwork. This is the kind of thing I always page to the bottom of a man page for--an example.
For those wanting to do this stuff from a shell script on windows, there's cygwin. You'll probably have to run the cpan script and fiddle with rebaseall and stuff, but it's worth it. Check out the http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.37/lib/Spreadsheet/WriteExcel.pm page. It very quickly pointed me to using write_string vs. write so excel's @$%@##$@ scientific notation idiocy wouldn't bite me.
Again, THANK YOU!!
TOPIC:-Perl: Convert a text file to microsoft excel sheet
I want to know which all moduels are installed and then after this i want to set them into as env variable.
please give me the synstax
Hi thanks for the code..
I was breaking my head from last 1 week for this.
But When I tried to execute this script I got the error as
Can't locate Spreadsheet/WriteExcel.pm in @INC (@INC contains: /etc/perl /usr/local/lib/perl/5.10.0 /usr/local/share/perl/5.10.0 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.10 /usr/share/perl/5.10 /usr/local/lib/site_perl .) at /bin/txt2xls line 4.
BEGIN failed--compilation aborted at /bin/txt2xls line 4.
Please help me on resolving this thing,
You have mentioned "Spreadsheet::WriteExcel must be available in perl lib, before running this OR you can set your PERL5LIB environment variable to point to your local lib where you have Spreadsheet::WriteExcel" . I have not done this... How do we do this ?
Vanakkam, Khaliq
Thanks for the script! It should do exactly what I need, convert a delimited ascii text file to a binary Excel file on Linux - ah, but I get an error message trying to use it. Do you have an ideas what is wrong?
My usage is
% txt2xls.pl "|" brothersAndSisters.txt
the file to be converted is 569 lines.
The error message I get is
(in cleanup) Can't call method "_close" on unblessed reference at /usr/lib/perl5/site_perl/5.8.5/Spreadsheet/WriteExcel/Workbook.pm line 536, line 569 during global destruction.
Of course I get a zero size output file.
Sorry I am not a Perl expert so no idea how to fix this. Can you offer any help?
Nandri! Thank you.
Sadhunathan Nadesan
sadhunadesan@gmail.com
Post a Comment