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:
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=Spreadsheet-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).
Heh... They don't call PERL the "Swiss Army Chainsaw of Programming" for nothing 
Hi,
Since our company HP-UX haven't Perl installed, it just has simple c complier. Have any other program to perform the same things?
many thanks
regards
wilson
You can do this as suggested above with a tab delimted file...you can't have any formatting, and only use one worksheet (the benefits of the perl method), but it will serve it's purpose.
well for my movie database (so what if its not work related, i still needed to keep track of this stuff heh)
i have a flatfile that is colon delimited. then i open excel and choose my file and it will promt me for what kinda delimiter and a few other questions that i want to use.
HP has been shipping HP-UX with an old version of perl in /usr/contrib/bin for some time. Are you sure it's not there? Oh, well...
The HP-UX Porting and Archive Centre has perl available for free download.