How to create an excel report from sql in Teradata

This post is in response to a topic on LinkedIn Group. When we say Excel report, we mean real Excel report with multiple tabs and not just one file with delimiter as comma which you can open in Excel. With the data present in tables you may have to create reports and share it with business. We know how to export data from Table into file in Teradata. You can use BTEQ export or FASTEXPORT depending on volume of the data you want in your report. So the export part is same. Lets look at below example:

#!/bin/ksh
 bteq <<end_of_bteq
 .LOGON SERVER/USERNAME,PASSWORD
 .SET SEPARATOR '~';
 .SET FORMAT OFF;
 .SET ECHO OFF;
 .SET TITLEDASHES OFF;
 .EXPORT REPORT FILE=file1.dat;
 SEL INFOKEY,INFODATA FROM DBC.DBCINFO;
 .EXPORT RESET;
 .LOGOFF
 end_of_bteq

Once the export is successful you will have a file named file1.dat. Now we want this info in an excel report. For this we will use perl module since it provides good features to handle excel. You just have to install Spreadsheet:WriteExcel Perl Module in UNIX and you are good.

Lets look at below example:

#!/bin/perl
 use Spreadsheet:WriteExcel;
 #make sure you have excel module installed.
 my $i=0; my $u=0;
 #i & u are perl variables to handle which cell to write value to. Excel first cell is 0,0
 my $workbook = Spreadsheet:WriteExcel->new("Report_File.xls");
 #create a new excel file with name Report_File
 $worksheet = $workbook->add_worksheet("Tab1");
 #create a new TAB with name Tab1
 my $file1 = 'file1.dat';
 #locate file to read
 open my $info1, $file1 or die "Could not open $file1: $!";
 #open file to read
 while( my $line1 = <$info1>) {
 #run the while loop to read content of file
 my @values = split('~', $line1);
 #file is delimited so read different column values in read line
 foreach my $value (@values) {
 #for each value read, run the loop
 $worksheet->write($i, $u, $value);
 #write the value in the cell
 $u++
 #increment the counter to move to next column
 }
 $u = 0;
 #reset column to 0
 $i++;
 #increment row to next
 last if $. == system("wc -l file1.dat");
 #read till end of line
 }
 close $info1;
 #close the file to read
 workbook->close();
 #close the excel generated and remove lock

If you want more tabs, just use the above code again or run the loop from worksheet to close info. This is how you can create a proper excel file from data present in table in Teradata.

Leave a Reply

Your email address will not be published. Required fields are marked *