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 lockIf 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.