Generating Excel Report Through Unix Script

[pareshan]

I am getting big output through my script which has 8 columns are hundred of rows which eventually i have to put in excel and its really time consuming to put it by manually
so I am wondering

Is there any way to generate excel reports or any other good reports through Unix script?

It will be helpful if I can get any examples regarding to that.

Thanks In advance

[Mel Burslan]

if the 8 columns you mentioned are in a uniformly spaced output file, you should have no trouble importing it into your excel spreadsheet. I do not remember under which menu item it is/was but I have done this in the past and it changes with almost every version of MS office products.

Also, if your columns are not uniformly space, you can separate the fiels by a "," (comma) and name the file as report.csv. When you copy a *.csv file to your pc, provided you have excel installed on your PC, it will immediately mark it as an excel file and double clicking it will open it in excel.

Hope this helps

[Rick Garland]

The answer to this question will depend on what type of data you are collecting and how you want the data to be displayed in Excel.

Essentially you will separate your output by some delimiter (say a comma character) so when you import the data into Excel you tell it that the comma is the delimiter.

Here is a sample script I did some time ago. Checking the disk space (using the bdfmeg.sh script) and NICs. The output of this script produces a 1 line record with fields that are delimited by a comma. Again, what data are you presenting, how you want to present, how many records, field, etc.

#!/bin/ksh

PATH=$PATH
export PATH

HOST=`hostname`
FS=`/sysadm/bin/bdfmegs.sh -l | sed '1,1d' | awk '{printf "%-s,%-s,%-s", $6, $2,
$3}'`
NIC=`lanscan -p | while read line
do
lanadmin -x $line | grep -v ERROR | grep -v Auto | grep -v or | awk -F= '{prin
t $2}'| sed -e 's/.$/,/g'
done`

echo $HOST,$FS,$NIC | sed -e 's/.$//'

[OldSchool]

basically, if you double-qoute character strings and "comma-separate" fields/columns, then simply saving w/ the ".csv" extension should allow excel to open it.

be careful when moving file from unix to windows, as you either need to ftp in ascii mode, or run ux2dos on it.

if for some reason, you have "commas" in your data, the pick an unused character as the delimiter. once the file is moved where windows/excel can get at it, then you should find the import wizard in Data -> Get External Data -> Import Text File

[James R. Ferguson]

Hi:

One way to add comma seperators and double quotes as OldSchool indicated is this:

# perl -ne 'BEGIN{$sep=","};@a=split;print "\"",join "\"$sep\"", @a;print "\"\n"' file

Change the value of $sep to fit your requirements --- a comma, a pipe symbol, etc.

For example using this data and a pipe character as the seperator ($sep="|"):

# cat myfile
a b c 123 456
x y z 9 9 9

# perl -ne 'BEGIN{$sep="|"};@a=split;print "\"",join "\"$sep\"", @a;print "\"\n"' myfile

"a"|"b"|"c"|"123"|"456"
"x"|"y"|"z"|"9"|"9"|"9"

Regards!

...JRF...

[Bill Hassell]

If the data is aligned in fixed columns, then you simply ftp the file to your PC, then select:

Data -> Import External Data -> Import Data

then select the file and a dialog window pops up to allow you to refine the columns (usually not needed), change the formatting for a column (numbers, text, etc) and select the upper-left cell to start the data. This same dialog box can handle other field delimiters such as tabs, commas, etc.

The command line interface for EMC, Axiom, FalconStor, Hitachi, DynaPath, Brocade, etc are usually Excel friendly. CSV formats are OK but more trouble than they are worth unless you can't provide fixed column spacing.