I would choose the latter since you can import your data directly into Excel as is:

• Name your file "something.txt"
• Start Excel
• Click File, Open
• Change 'Files of type:' to 'Text Files'
• Select the file to import and click the Open button
• Click the 'Delimited' radio button and click next
• In the delimiters section type '|' in the 'Other' box and click through whatever other options you need.

--Jim

Update: Note that unless you can find a module that converts directly from a text file of your format to an Excel "*.xls" file that you will still have to go through the Excel import feature (even if you tweak it with Perl).

KISS ++ jlongino. The simplest way is almost always the best. You don't need a nuclear warhead like Perl to crack an itty bitty chestnut like this....but of course once you learn Perl you can never go back to simple Excel macros without wishing for....

cheers

tachyon

s&&rsenoyhcatreve&&&s&n.+t&"$'$`$\"$\&"&ee&&y&srve&&d&&print

I hate to give a non-Perl answer but, keeping with KISS there is a simpler way. Any version of Excel 97 or above (maybe lower, but I cannot verify) will import a pipe delimited file, or any single char delimited file. The import wizard will allow about anything as the delimiter.

Just go to Excel, open a copy of the file (preferrablly after changing the extension to .txt). Excel will open up the import wizard.
In the first screen select "Delimited"
On the next screen select "Other" and put a "|" in the box.
Click through to the end
Enjoy a frosty beverage of your choice

grep

grep> cd pub 
grep> more beer

I suppose that instead of an Excel macro she/he could use  Win32::OLE or somesuch. ;)

--Jim

Let's assume for the sake of argument that you want to create a CSV (comma-separated-value) file. The approach is to iterate through the lines of the flat-file database, converting each record into the desired otuput format (CSV).

Iterating through the lines of a file is fairly simple, just let perl's <> operator open the file for you:

# loop through every file named on the command line, setting
# $_ to each successive line
while (<>) {
  chomp;  # remove any line ending (implicitly uses $_)

  # ... aha! the interesting part goes here
  my @fields = split /\|/;  # implicitly uses $_

  # Now we have the values for this row in $fields[0], $fields[1], ...

  # write to STDOUT
  print join( ',', @fields ), "\n";
}
[download]

BTW, exclusive of anything related to Perl, Excel should be able to import this format directly (just name the file with a '.txt' extension and useExcel's Import Wizard), but I suspect that is not what you are asking.

Of course, you could simply translate '|' (pipe) characters into ',' (commas), but I suspect that, too, is not the point of your question.

dmm

My first impulse was to say that although this is probably feasible, it is a bad idea. My second thought was that if this pertains to an intranet, you should probably write a Win32 Perl script that operates via network which is fairly trivial (assuming your work site has some type of LAN).

However, after a simple search (which I recommend you always give a try before posting) on Google, at least three different plug-ins were listed that may do the trick for you. Naturally I leave the exercise to you but I'm sure that the Monastery (myself included) would enjoy hearing about your success (or otherwise) in this adventure. I still think that if you are dealing with a LAN, my second thought would still be the quickest, easiest approach.

Here are the three hits I mentioned:

• NSF - Information - Links to Plug-ins and Viewers ... Links to Plug-ins and Viewers for File Formats on the NSF Web Site. File Format, Plug-in/Viewer. Acrobat (.pdf). Acrobat Reader. Excel (.xls). Excel 97/2000 Viewer. ... www.nsf.gov/home/pubinfo/plugins.htm   -->  relevant link
• [DOC] Installing the Piranha Excel Plug-in File Format: Microsoft Word 97 for Macintosh - View as HTML Installing the Piranha Excel Plug-in. Make sure Excel is not open. Enter Global Access through the Library’s pull-down menu, http://lib.bus.umich.edu. ... eres.bus.umich.edu/web/guides/piranhainstl.doc
• JC_ActiveDoc Plug-In for Netscape ... JC_ActiveDoc(TM) is a Netscape Plug-in that allows you to view Micrsoft Word, Excel, PowerPoint right inside Netscape. It's ideal for Intranet information ... Description: Netscape plug-in, that allows you to view Microsoft Word, Excel, and PowerPoint documents right inside... Category: Computers > Software > Shareware > Windows > Internet > Browser Enhancements www.jcsoft.com/docobject/
HH&H (Happy Holidays & Hunting)

--Jim

#!/usr/bin/perl -w

use strict;

open IN, "infile.dat";
open OUT, ">data.csv";

while (<IN>) {
    chomp $_;
    my @fields = split(/|/, $_);
    my $newLine = join(",", @fields);
    print OUT "$newLine\n"
}
close IN;
close OUT;
[download]

Your script:

open IN, "infile.dat";
open OUT, ">data.csv";

while (<IN>) {
    chomp $_;
    my @fields = split(/|/, $_);
    my $newLine = join(",", @fields);
    print OUT "$newLine\n"
}
close IN;
close OUT;
[download]

would corrupt the data with:

hi|I'm with GNU,Inc.|I need a comma in my title
this|will|produce|,|to|many|fields
[download]

Using pipes to delimit data is a better idea (usually) because it is less common in data. There are also several modules to parse CSV. Incuding Text::CSV.

UPDATE: tilly has pointed out that Text::CSV_XS is a better solution than Text::CSV. I handles imbedded line endings in the data.

grep

grep> cd pub 
grep> more beer

Hi, i am new to perl. Recently, i have encounter a problem and i need some advise from u guys. Hope u can help me. Anyone know how to export a .csv file into excel using perl ??? I can only write a prog. to enter some data into excel but cant seem to call out a .csv file into excel using perl. Thks in advance.