UNIX for Dummies Questions & Answers

Hi All,

I want to convert a '|' delimited file to excel file. I want it is an automated process (this means I need not to open excel, import and choose '|' delimited, then ... to generate an excel) I need to use a scripts or proram to convert the file.

Do any one have this tools?

A program to convert '|' delimited file into excel.

Hi,

The best way is using the import function in Excel, if "|" in not a valid dilimeter for Excel you can change this on a UNIX box.

Maybe you need some Vbasic knowlegde to automate the import.

JR

My first thoughts would be to convert the pipe into a tab char, comma char or semi-colon char (all supported by Excel). Does the current cata use tabs, commas or semi-colons?

If not, then use sed to convert the pipe into a supported delimeter and have at it. If your data does have the three above mentioed chars, then you can group your columns with double-tics and then seperate the columns with a delimeter and you should be good to go.

My Hope is to automate this "import" and "convert" in a program.
Furthermore, I would like the platform is UNIX.

My expected whole process is as below:

1. Use unix scripts to generate a '|' delimited text file.
2. Use "convert program" to convert the '|' delimited text file into an excel text file.
3. Email the excel file to user.


Point 2 is my wanted program.

You can't create an excel file in any other application apart from excel. You can however create a file in a text format that can open in excel....this is your only method.

I would think the only way you can acheive your aim is to create the file in unix with an '.xls' extension...then mail it directly from unix.

When the file is received they can open it with Excel...you will have to change the file to a tab delimiter to make it open in the appropriate columns...other delimiters will not be recognised unless they are specified when converting text to columns.

I don't see any other way around this I'm afraid. If you want help with the automated mailing of files then use search as it's a popular topic for discussion.

Not exactly true, other things besides Excel can produce "Excel files". I've done so in PERL scripts many times before using this:
http://search.cpan.org/search?dist=S...eet-WriteExcel

If you're writing something to produce a "|" delimeted file and then converting the "|" character to something else, why not produce the file with that other character to begin with?

If it's a "text" file and is "|" or comma delimeted or whatever, emailing it form a shell script is pretty straight-forward:

cat your_file.txt | mail -s "Here's that CSV file for you" any@body.com

Agreed...in that if you are writing the script whcih produces the delimiter...you may be able to make it a tab straight off. If it's requried for something else as a pipe...then you can decide to either create with tab..email then translate to pipes...or vice versa.

And I like the link with the perl ....I stand almost corrected (almost).