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

        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
Who is Participating?
woolmilkporcConnect With a Mentor Commented:
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.
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
rammaghentharAuthor Commented:
Hi Woolmilkporcji,
                 Let me work on it and come back to you
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.