Need help on reading a xls file

[Vidhya B]

Hi all,

I need to read a .xls file. How will I be able to do it?

#uname -a
HP-UX filesvr B.11.23 U 9000/800 77929270 unlimited-user license

Please help.

[Duncan Edmonstone]

At the risk of being obnoxious, I'd say:

1. FTP the .xls file to a PC with Excel installed
2. Open the file in Excel
3. Read it

But then I guess when you said "read" a .xls file, you didn't mean read it yourself did you? Presumably you want to do something with the data in an Excel file? Without knowing what you want to do its difficult to say, but I'd imagine Perl would probably have something you need. You'll probably need to get an additional module for reading/converting excel data, so go to http://www.cpan.org and do a search on "excel" - that should throw up plenty of modules that will help.

And if you don't know Perl, what a great opportunity to learn!

HTH

Duncan

[Vidhya B]

Hi,

I don want to read a excel :)

I want my script to read excel.

I want to grep few words from my excel. How will I be able to do it?

[Johan Guldmyr]

Would you be able to export it as a .csv?
Then you'd get it in a nicely text-readable format.

[Matti_Kurkela]

Because .xls is a binary format, simply using text-based Unix tools like grep on it will not work so well: the output may/will contain unprintable characters and other random junk.

You need something that can understand the .xls format. Duncan already suggested using Perl and its modules.

For example, if you know the worksheet name and cell coordinates of the data you're looking for, the Perl module Spreadsheet::ParseExcel would be able to check exactly the cells you want.

But if you don't know where the words you search may be located within an Excel workbook file, it might be easier to use a converter like excel2txt or xls2csv to convert the Excel file to one or more text files (.txt or .csv), which could be then searched using standard Unix commands.

Links to above-mentioned Perl utilities:

http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel-0.59/lib/Spreadsheet/ParseExcel.pm

http://search.cpan.org/~kclark/excel2txt/excel2txt

http://search.cpan.org/~ken/xls2csv-1.06/script/xls2csv

Instructions for installing Perl modules:
http://www.cpan.org/misc/cpan-faq.html#How_install_Perl_modules
http://perldoc.perl.org/cpan.html
http://perldoc.perl.org/perlmodinstall.html

As Johan said, if you can get the file in .csv format directly from the source, you could use Unix tools directly.

MK

[Michael Sillers]

You could use strings to get the readable characters, then pipe it through grep but it won't retain any format such as columns.

[vasanth_2]

Hi,

open the .xls file you want to view in hp-ux, save that file as "Text (Tab Delimited)" in save as dropdown. Ftp the file to the server in ascii mode. Then grep/more it in the server. You can view the file.

Note: you can view only the current sheet as per the above process.


Hope this helps.

Thanks
Vasanth.

[Vidhya B]

Hi,

Is there anyway I can do the viceversa thing.

My script stores the output in a file which will be like a notepad.
I need to create columns in them and place contents in them in order like excel.

Is that possible to do that? What will help me do that?

Kindly help.
Sorry for the delay in assigning points.

[Vidhya B]

Hi,

As vasanth said, If I save I am able grep like that.Thanks vasanth!!

But My excel has got 5 sheets.
I need to save only 3rd sheet to be saved in that way.

Is there any possibility to do that through command line?

Kindly help.

[Steven Schweda]

> Is there anyway I can do the viceversa thing.

That's a question? Define "the viceversa
thing".

> My script stores the output in a file
> which will be like a notepad.

Showing some sample output might be more
helpful than a vague description.

> I need to create columns in them and place
> contents in them in order like excel.

Huh?

> Kindly help.

You first. What, exactly, are you trying to
do? Where? With what? Are you trying to
create a file which Excel can read?

I know approximately nothing about Excel, but
I suspect that it can read a file which
contains comma-separated values. Do you need
to do any more than add some commas to what
you store in your output file?

[Vidhya B]

Hi,

My output is like below.

Time settings Done
TCP & IP Forwarding Done
User restriction - group Done
Service Restriction No


I need to arrange the them in two different columns with proper alignment.

I need the below lines to be in one column

Time settings
TCP & IP Forwarding
User restriction - group
Service Restriction

and the below line to be in column 2
Done
Done
Done
No


Both the columns should have proper separation, So that one can easily find which is done or not done on seeing the output.

Please help.

[Steven Schweda]

> My output is like below.

Output from _what_?

> I need to arrange the them in two different
> columns with proper alignment.

So, do you want the output to look nice? And
this question has nothing to do with "a .xls
file"? (So why ask in this thread?)

> [...] in column 2

And where would you like column 2 to start in
the line? (How much space should column 1
occupy?) Most computers can't read your mind
any better than I can. You often need to
tell them _exactly_ what you want them to do.
Which means that you often first need to know
what you want done.

If you're creating this stuff in a shell
script, and you have the "printf" command,
then some alignment tasks can be pretty easy.
For example:

dyi # printf '%30s %s\n' 'Time settings' 'Done'
Time settings Done

dyi # printf '%-30s %s\n' 'Time settings' 'Done'
Time settings Done

[Vidhya B]

Hi,

I need my output actually to be in excel. Atlease I need them to be properly aligned, so that the output can be read easily without any confusion.

I am not able to understand the commands you have used. Can you please explain them?

Thanks!!!

[Johan Guldmyr]

Can't you do this kind of formatting inside excel?

[Vidhya B]

With Steven Schweda's command, I am able to get the output like this.


Time Setting Done

Banner Done

Disable Direct root login Done

System stack non-execuatable Done

TCP & IP Forwarding Done

Removing files Done

Service Restriction No

UID 0 only for root No

User Restriction-group Done

User Session Timeout Done


Is there any possibility to draw a table to this?

I need the font to be in center alignment. Is there any command which helps me do this?

Thanks in Advance!!!

[Johan Guldmyr]

You could do it with HTML and add table tags in a "foreach line" while loop (i don't know what it's called). But a script that reads each line and for each line it finds your data and then you can set it to add before and after (or ) all depending on how you want it to look.

[Vidhya B]

Hi,

Is there any command in HP-UX which can help me create table?

[Dennis Handly]

>Is there any command in HP-UX which can help me create table?

There is nroff(1) and tbl(1) but you may not want to go back to the formatting dark ages.

[James R. Ferguson]

Hi:

> Is there any command in HP-UX which can help me create table?

Go back and re-read Steven's comments about 'printf'. For that matter, read the Unix manpages regarding it.

> I need the font to be in center alignment. Is there any command which helps me do this?

Excel will allow you to format a column with left, right or center alignment. Why re-invent the wheel when clearly you are more comfortable with Excel rather than anything Unix.

Regards!

...JRF...

[Steven Schweda]

> I need my output actually to be in excel.

I don't know what that means. Are you trying
to create a file which Excel can read, or are
you trying to create nice-looking output, or
what? Or don't you know?

> Output from _what_?

Still a mystery.

> Can you please explain them?

man printf

> Is there any possibility to draw a table to
> this?

I don't know what that means. Do you?

> > Kindly help.
>
> You first. What, exactly, are you trying to
> do? Where? With what? Are you trying to
> create a file which Excel can read?

Still wondering.

[Vidhya B]

Thanks Johan!!! I used html. It worked.

Thanks all!!