PROVE IT !!If you know it then PROVE IT !! Skill Proficiency Test

Create Well Formatted Excel with HTML Email template in Teradata

Warehouse environment is all about predective analytics and there are various data which needs to be extracted from host system which can be used for reporting, However generating the mails & report from host system without any tools is a great challenge.

Simplified : – With the below process we can generate & send email as good like human drafted email using the best mailing tools from unix/linux box  itself.

     STEP 1 ) Export the data in a File

#!/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

STEP 2 )  Create a perl script to format the file into traditional Xls file

Prerequisite: Spreadsheet:WriteExcel Perl Module should be installed  in UNIX.

#!/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

Step 3 ) :  Create Sendemail module & HTML File for formatted text email template.

==============================================================================================

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx  
 

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

 

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

FOR INTERNAL USE ONLY – NOT FOR REDISTRIBUTION
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

We are experiencing relatively bad performance in the functioning of Online Queries due to Teradata Database, xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Below are the checkpoint's which will be verified and Notified , Stay tuned 🙂
* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
* xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
 

<<<>>

<<<<>>>>

=========================================================================================================================================================================================================================================================================================================

Step 4 ) Send emial using Mailx ( With Attachment) or sendemail(without attachment)

Tags:

Add a Comment

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