I received a text file from a client that contains the output of one sql script (The output represents detailed statistics about all sql statements running in one critical database).
The output is similar to the following:
You can download a sample file similar to that of our client from:
https://1drv.ms/u/s!ArrpKxPFc2APxRw0izNXIYqVHGdw?e=UCgUBs
The output is not formatted as you see.
The question is: why you have to write a program to get a formatted output?. You may simply run the query again using tools like: SQL Developer or SQLCL and get a better formatted output that you may save it as Excel sheet or CSV file?.
Ok, let me explain the background:
Our client migrated one key database from an old system to Exadata. He captured all the statistics related to the old database before the migration and exported it to a file in a bad unreadable format.
The source database was completely stopped after migration, and we cannot access it.
Our client wants to analyze and compare the SQL statistics before and after migration to see the performance enhancements.
If you tried to open and format this file in Excel, you will not be able to do that. I didn’t find a simple way to do that. The data of one line is scattered across 8 lines.
Also, this file contains more than 6000 lines, you cannot modify it manually!. If we assumed that one sql statement lines (8 lines in the output) needs half minute to be formatted manually, then you will need more than two days to fully format this file.
So, I thought that writing small python program can do the job for me quickly.
The python program will read the above output file, and produce a more formatted output, like the following one:
You can see a sample formatted output from here:
https://1drv.ms/u/s!ArrpKxPFc2APxR0ZdQcA18nTrYdZ?e=5HbOmn
Following is my python program to format the file:
Also, the script is available here:
https://1drv.ms/u/s!ArrpKxPFc2APxR6PEIbezcf6mX1f?e=Fjezjs
Before running the script against the file, do some quick preparations:
1- Open the file in text editor like Notepad or Notepad++
2- Highlight the header row and copy it, then click CTRL+H to replace all header lines with noting.
3- Add “EOF” in the last line.
4- Save the sample file.
See a demo about this subject:
https://www.youtube.com/watch?v=I_wngcahmLg
That is all.
Thanks
Ahmed
Ahmed AbdelFattah Blog ! 