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

rammaghenthar used Ask the Experts™
        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
/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
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Most Valuable Expert 2013
Top Expert 2013
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.


Hi Woolmilkporcji,
                 Let me work on it and come back to you

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial