topic Re: convert text file to binary excel file in Operating System - HP-UX

convert text file to binary excel file

peterchu — Sun, 21 Nov 2004 03:00:43 GMT

is there any method / tools good to convert text file (eg. .csv / .prn) to excel file on the shell ? thx

Re: convert text file to binary excel file

Neil Wilson_2 — Sun, 21 Nov 2004 05:04:12 GMT

Not sure if there is a command to do this. There are however other commands to convert files, e.g.

DOS convervsion (ux2dos), e.g.
ux2dos hpux.file > dos.file

dd (convert and copy a file)
Check out the man page.. you need to see the
"-conv" switch option.

Re: convert text file to binary excel file

Steven E. Protter — Sun, 21 Nov 2004 11:18:45 GMT

I personally would use excel to do that conversion.

SEP

Re: convert text file to binary excel file

Hein van den Heuvel — Sun, 21 Nov 2004 20:16:18 GMT

Like SEP I just have Excel do it.
I have 'associated' the .cvs extention with Excell. So if I open (doubleclick) a .csv file on my pc, it invokes Excell and just works. I then 'save as' ... 'excel workbook' to save it as a binary, full functionallity .xls file.

fwiw,
Hein.

Re: convert text file to binary excel file

Ranjith_5 — Sun, 21 Nov 2004 21:13:55 GMT

Hi,
Make excel as the default program to open .csv file. Thats it. Next time onwords CSV files will get opened with excel automatically.

But do u want this to be used in the shell as you mentioned?

Regards,
Syam

Re: convert text file to binary excel file

Bill Hassell — Sun, 21 Nov 2004 21:34:56 GMT

Excel has a very complex file format that varies with the revision of Excel used to save the file. It is certainly easy to take data in a file and convert it to .csv (Comma Delimited Values). Even if you did convert a .csv to Excel format, you'd still have to open the file in Excel--which is exactly what you would do to open the .csv file--and the results would be the same.

Re: convert text file to binary excel file

Elmar P. Kolkman — Mon, 22 Nov 2004 02:14:34 GMT

But if you really want it, it can be done using perl and the SpreadSheet::WriteExcel module... I've done it and it works (even on HP-UX). But it takes some programming knowledge and perhaps a good compile environment to make it work. And you might need some extra modules.

There is even a DBD for excel files. It is really beta, and gets awfully slow when inserting 17000 lines of data in a worksheet (table), but it works too. And is a bit easier to use. But it uses a lot of other modules, like SpreadSheet::WriteExcel, SpreadSheet::ParseExcel, etc.

You might also look into openoffice. I don't know if you can do it use it from commandline to do what you want, but it should work.

It's probably easier to do it using Excel. If you want formatting done the right way, use Excel macro's or make sure Excel can not make faults when importing the data by using formats that can only be interpreted one way (dates are the main issue here. In the Netherlands, we use a dateformat of dd-mm-yyyy. But an english Excel version will 01-11-2004 as 11th of january, not the 1st of november... By using yyyy-mm-dd as the date format, excel will never misunderstand a date from your file).

Re: convert text file to binary excel file

peterchu — Tue, 23 Nov 2004 01:31:09 GMT

thx Elmar P. Kolkman 's reply ,

I have downloaded the module that you memtioned and try some example , it seems ok to write some lines to the excel file , but I have a problem to use this module , I just want to convert the file from .prn to excel format , i tried the below script , but the excel file tab.xls can't be read , I am no need any formatting , could suggest how to modify the script so that I can convert the file ? thx.

#!/usr/bin/perl -w

use strict;
use Spreadsheet::WriteExcel;

open (TABFILE, "tab.txt") or die "tab.txt: $!";

my $workbook = Spreadsheet::WriteExcel->new("tab.xls");
my $worksheet = $workbook->add_worksheet();

# Row and column are zero indexed
my $row = 0;

while () {
chomp;
# Split on single tab
my @Fld = split('\t', $_);

my $col = 0;
foreach my $token (@Fld) {
$worksheet->write($row, $col, $token);
$col++;
}
$row++;
}