Auto Update Excel Sheet For DBAs

A Oracle DBA’s job always involves daily health checkups of the database and application and preparing a report of the same and sending it to your supervisors.

I tried experimenting something on the same and it worked out. Found it interesting so sharing it with you. Here I am checking server mountpoint size and updating the same automatically in excel. The same can be replicated for any kind of SQL statments.

Below are the steps to Automatically update excel file from txt (spool) files.

1. To generate spool file ::

script -q -c ‘df -h’ db_space.txt (Works on Linux, kindly check for other OS)

2. move spool file to local machine

3. Open Excel file:
Setup Import::
1. Click On “Data” menu option
2. Click “from text”
3. Browse and select the above spool file.
4. Choose Delimited button Click on Next.
5. Select space as delimiter , Click Next then Click Finish
6. Click Properties, Tick Refresh data when opening file, untick adjust column width, tick preserve cel formatting and select overwrite existing cells with new data, clear unused cells and then Click OK, OK.
7. Format the table as required. And Save the file.

Now every time you replace spool file with latest values Excel sheet automatically gets updated when you open it.

Advertisements

Posted on July 8, 2014, in Oracle E-Business Suite, Oracle Scripts and Commands and tagged , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: