how to get all vmstat output file in Excel file (each line in seperate column)

Hi
        I have following script to get vmstat output . I want each column of output in seperate column of Excel file. I have added .xls extension along with output file and checked the file . It was showing each full line per one row which format  I don't need? I think some seperator need to introduce in the script? any help on this please?




Script file
####### cat /backup/vmstat/vmstat_col.ksh
##This is to collect the vmstat output
#/bin/ksh
/usr/bin/vmstat 60 1440 > /backup/vmstat/vmstat.`date +%d`

Cron entry
2 0 * * * /backup/vmstat/vmstat_col.ksh >/dev/null 2>&1

I have attached current output file for reference
vmstat.02.txt
LVL 2
rammaghentharAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

farzanjCommented:
Excel understand CSV format.  You can convert it into CSV format and then excel will be able to open it as per your requirement.

From your file, I can see that it is space delimited.  If you have sed, you can issue command like

sed 's/^  *//;s/  */,/g'  vmstat.02.txt > vmstat.02.csv

Or using perl
perl -pe 's/^\s+//;s/\s+/,/g' vmstat.02.txt > vmstat.02.csv
0
woolmilkporcCommented:
Pipe the vmstat output to awk:
/usr/bin/vmstat 60 1440 | awk '{if(($0~"avm"&&NR==4)||($0~"[0-9]"&&NR>6)) {for(n=1;n<NF;n++) {printf " %s;",$n} {print " "$NF}}}' > /backup/vmstat/vmstat.`date +%d`.csv

Open in new window

This will give you a list of delimited values which you can process with Excel.

Please note that I used ";" as the column separator and used the extension ".csv" for the output file, so Excel will recognize the format automatically.
However, this depends on your locale settings. You might have to use "," as the delimiter.

Change the separator to whatever character in this place:

... ...  {printf " %s;",$n} ...
... ...  {printf " %s,",$n} ...

The spaces in front of each field are required to keep Excel from changing certain numbers to "date" format.

The output file will contain one header line like:

 r; b; avm; fre; re; pi; po; fr; sr; cy; in; sy; cs; us; sy; id; wa; pc; ec

Finally, please note that you won't see any data in the output file until the 1440 60-second cycles have passed.

So for testing you should use fewer or shorter cycles.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rammaghentharAuthor Commented:
Hi Woolmilkporcji,
                 Let me work on it and come back to you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Unix OS

From novice to tech pro — start learning today.