Formatting Bteq Output for use in Excel using XML spreadsheets

Extensibility

Formatting Bteq Output for use in Excel using XML spreadsheets

In the interest of collaboration and innovation, I would like to share how we solved some formatting requirements for a client.  The goal here was to utilize existing outputs from BTEQ.  The modified job was outputting a .CSV which had the file name extension changed to .XLS to trick Excel into opening it as a worksheet.  This was effective, but limited the ability to format the output.  Taking advantage of Excel's ability to read an XML spread sheet you can apply any formatting available in Excel to your Bteq outputs using some simple concat from SQL, and CAT from Unix.

Please share any thoughts/problems or better ways to utilize this solution.  I have implemented this on two different fairly simple reports.  I have moved aggregation from the SQL queries to the Excel spread sheet, and also created multi tabbed reports using this output.

All we intend to do here is add some XML formatting portions to the existing output to apply formatting.

  Two XML files will be created that hold the specific details of the formatting requirements.  These XML documents will be combining with the output from the script and then uploaded in the same fashion it is currently completed in.

No changes other than the formatting are addressed in the SSCR.

  The basic idea behind adding formatting information to Bteq out puts utilizes Excel’s ability to read .XML formatting.

This is backwards compatible to Excel 2003.

  Currently the .ctl file is exporting a large file of string values, separated by comma’s, tabs or w/e.  A basic .CSV.  Instead of outputting csv files, we can use the Bteq and SQL to output what are called <node rows> in XML.

The basic structure of a Node row :  

<noderow>

   <provider>WILLIAMSON MEMORIAL HOSPITAL</provider>

   <city>WILLIAMSON</city>

   <state>WV</state>

   <medicaid_no></medicaid_no>

   <adult_ped_days>20.000</adult_ped_days>

</noderow>

So, in the SQL, all we are doing is appending the XML information. 

The select looks something like

SELECT top 10

   '<NodeRow>'||

    '<Provider><Data ss:Type="String">'||TRIM(Hospital_name)||'</Data></Provider>'||

        '<City><Data ss:Type="String">'||CITY||'</Data></City>'||

    '<State><Data ss:Type="String">'||STATE||'</Data></state>'||

'</NodeRow>'(TITLE'')

   FROM ADRS;

All of the formatting work can be completed within Excel, and then saved.  Once the file is saved we simply cut the formatting pieces off, store it on the Unix box and use Unix to append the formatting pieces to the SQL Node row export.  The great part about this, is we can actually have the business do all the formatting, and save the files off as xml Workbooks.  We can then take those generated XML’s and simply append them to our work. 

Unix Cmd:

cat 188_Top_Format.xml dataNodes.xml > temp1

cat temp1 188_bottom_format.xml > final.xls

 General outline:

XML_FORMATTING_1

      NODE_ROW_OUTPUT_FROM_BTEQ1

XML_FORMATTING_2

     NODE_ROW_OUTPUT_FROM_BTEQ2

XML_FORMATTING_3

This solution gives the capability to do ALMOST any formatting available in Excel, to include  Print headers/footers.  You cannot use some functions, and pivots/graphs are out.

The Storage of the XML formatting pieces on the ScrFiles Unix box takes minimal space, and allows for easy formatting changes. IE: Simply adjust the XML_Formatting1,2,3 file depending on how you want to adjust.

The Node_OutPut_XML1/2 are exactly the same as the .CSV the scripts currently output.  They can be very large and are cleaned up after export.

I have a functioning example in my folder on the Unix box.  All of the time I spent developing this solution was my own, so nothing to worry about with timelines.

I hope this gives a basic understanding of what I tried to do, let me know if you need any further explanation

1 REPLY
Enthusiast

Re: Formatting Bteq Output for use in Excel using XML spreadsheets

Thanks for sharing your solution!