A tools to convert a file with delimiter to excel file

[Patrick Chim]

Hi,

I have heard before that there is a tool (perl?) that can convert a text file with delimiter to an excel file (xxx.xls), isn't it ?

As I have a lot of text file need to import to excel files so I am eager to have this tools (if it really exists) !

Regards,
Patrick

[H.Merijn Brand (procura · Accepted solution]

search.cpan.org can be used to find perl modules

you need Spreadsheet::WriteExcel and if your text file is in any form of CSV, you might want Text::CSV or Text::CSV_XS

Spreadsheet modules can be fetched from ftp://download.xs4all.nl/pub/mirror/CPAN/modules/by-module/Spreadsheet/ or any other CPAN mirror
These modules might require other modules. If it requires IO::Scalar, that can be found in IO-stringy. It's very likely that they also want modules from the OLE:: namespace that are unlikely to run on anything but M$win

When running on M$win32, the easiest way to go is using ActivePerl from ftp://downloads.activestate.com/ActivePerl/Windows/5.6/ActivePerl-5.6.1.633-MSWin32-x86.msi and install http://www.activestate.com/PPMPackages/5.6plus/Spreadsheet-WriteExcel.ppd with ppm

Installing perl when running on Cygwin (my preferred way) is a breaze, but you then have to build the modules yourself

This question does not belong in the HP-UX forums, but in Windoze

[harry d brown jr]

Patrick,

Procura is our perl man so just follow his advice.

live free or die
harry

[Deepak Extross]

Patrick,

Excel can open a comma-separated-file <*.csv> correctly.

Try this:

1. Find out what the separator in your text files is, and simply use 'sed' to replace it with a comma.
For example, if your text file is aaa.txt and delimiter is '|', then do this:
sed 's/|/,/g' aaa.txt > aaa.csv

2. Now just open aaa.csv using Excel.

3. Through Excel, you can save the file as either a .csv (comma-seprated value file) or a .xls (m$ excel format), the choice is yours.

hope this helps.

[H.Merijn Brand (procura]

Deepak, good thing to mention that. I forgot. Brings it nicely back to HP-UX :)

CSV however is far more complicated than it looks from the surface. If you can unambiguously parse the text file, using a comma does not guarantee correct parsing by Excel.

Use either Text::CSV_XS (very fast, but you need the same C compiler as your perl was made with: has XS code), or Text::CSV (slower, but pure perl) to create CSV data that is fully standard compliant.

Both can deal with binary/Unicode data and yield a very reliable connection to Excel.

Example script:

#!/opt/perl/bin/perl

use strict;
use warnings;

use Text::CSV_XS;

my $csv = Text::CSV_XS->new ({ binary => 1, always_quote => 1 });

sub parse ($)
{
# your parse code here
split m/\s+/, $_[0]; # example
} # parse

while (<>) {
unless ($csv->combine (parse ($_))) {
print STDERR "Data error: ", $csv->error_input, "\n";
return;
}
print $csv->string, "\r\n";
}