Get difference of two text files in excel and highlighting the difference

Question

Alok_3 0 Newbie Poster

9 Years Ago

Get difference of two text files in excel and highlighting the difference using python.
eg. file1.txt

alok|test1|test2|test3|test4
alok1|test2|aewer|262771|262772
Rest|t1|rang|banh|test
Must|t1|rang|banh|test

file2.txt

alok|test11|test2|test3|test4
alok1|test2|aewer|262771|262772
Rest||rang|banh|test

In excel the difference should be shouwn as below:

file1.txt|alok||`test2`|test3|test4
file2.txt|alok|`test11`|test2|test3|test4
file1.txt|Rest|`t1`|rang|banh|test
file2.txt|Rest||rang|banh|test
`file1.txt`|`Must`|`t1`|`rang`|`banh`|`test`

Basically if a record is present but with some field with different value it should come as red and if the complete line is not presnt in the second file then and vice versa ie second record not presnt in first file then the complete record should come in blue color.
Any help will be appreciated.

excel python

Edited 9 Years Ago by Dani because: Formatting fixed

4 Contributors
5 Replies
507 Views
1 Year Discussion Span
Latest Post 8 Years Ago Latest Post by Alok_3

All 5 Replies

TrustyTony 888 pyMod

9 Years Ago

How is this connected with Python?

Edited 9 Years Ago by TrustyTony

vegaseat 1,735 DaniWeb's Hypocrite

9 Years Ago

Maybe a module like xlrd might do it.

https://pypi.python.org/pypi/xlrd

Edited 9 Years Ago by vegaseat

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Alok_3 0 Newbie Poster · Answer 1 · 2015-10-27T08:53:22+00:00

Created a framework, will format it and share soon

Informaticauser@server1 DEV $ cd FRAMEWORK_PACK
Informaticauser@server1 DEV $ ll
total 3644
-rwxrwxrwx 1 Informaticauser informatica     754 Oct  5 10:17 xargvinput.pl
-rwxrwxrwx 1 Informaticauser informatica     418 Oct  5 10:17 txttocsv.py
-rwxrwxrwx 1 Informaticauser informatica    1152 Oct  5 10:17 template_color_coding_excel.py
-rwxrwxrwx 1 Informaticauser informatica      24 Oct  5 10:17 run_trigger.sh
-rwxrwxrwx 1 Informaticauser informatica     691 Oct  5 10:17 process_final_upload.pl
-rwxrwxrwx 1 Informaticauser informatica      82 Oct  5 10:17 pre_upload_template2.txt
-rwxrwxrwx 1 Informaticauser informatica     450 Oct  5 10:17 delinsert.pl
-rwxrwxrwx 1 Informaticauser informatica     483 Oct  5 10:17 csvtoxls.py
-rwxrwxrwx 1 Informaticauser informatica     361 Oct  5 10:17 csvtotxt.py
-rwxrwxrwx 1 Informaticauser informatica     306 Oct  5 10:17 countcolumn.py
-rwxrwxrwx 1 Informaticauser informatica     672 Oct  5 10:17 compare
-rwxrwxrwx 1 Informaticauser informatica     124 Oct  5 10:17 colorcoding.sh
-rwxrwxrwx 1 Informaticauser informatica    1134 Oct  5 10:17 color_coding_excel.py
-rwxrwxrwx 1 Informaticauser informatica    1464 Oct  5 10:17 bteq_upload.wrapper.txt
-rwxrwxrwx 1 Informaticauser informatica     323 Oct  5 10:17 bteq_field_position.wrap
-rwxrwxrwx 1 Informaticauser informatica     391 Oct  5 12:25 bteq_perl_trigger.pl
-rwxrwxrwx 1 Informaticauser informatica     514 Oct  5 14:01 oracleconnect1.py
drwxrwxrwx 2 Informaticauser informatica    4096 Oct  6 11:45 BKP
-rwxrwxrwx 1 Informaticauser informatica     121 Oct  6 14:02 oraclerun1.sh
-rwxrwxrwx 1 Informaticauser informatica     121 Oct  6 14:02 oraclerun.sh
-rwxrwxrwx 1 Informaticauser informatica     116 Oct  6 14:02 csvtoxls.sh
-rwxrwxrwx 1 Informaticauser informatica     119 Oct  6 14:02 count.sh
-rwxrwxrwx 1 Informaticauser informatica 3556344 Oct 13 10:35 db2.jar
-rwxrwxrwx 1 Informaticauser informatica    7218 Oct 13 12:23 id_password_setup.pl
-rwxrwxrwx 1 Informaticauser informatica    2225 Oct 13 12:25 menu.sh
-rwxrwxrwx 1 Informaticauser informatica    5386 Oct 13 13:34 trigger
-rwxrwxrwx 1 Informaticauser informatica     580 Oct 14 06:50 cln.sh
-rwxrwxrwx 1 Informaticauser informatica     148 Oct 14 10:00 c.sh
-rwxrwxrwx 1 Informaticauser informatica     388 Oct 15 05:00 template_bteq_help_field
-rwxrwxrwx 1 Informaticauser informatica     119 Oct 15 05:00 pre_upload_template1.txt
-rwxrwxrwx 1 Informaticauser informatica     388 Oct 15 05:00 bteq_help_fields.txt
drwxrwxrwx 3 Informaticauser informatica    4096 Oct 15 05:00 CODE
-rwxrwxrwx 1 Informaticauser informatica     548 Oct 15 05:02 oracleconnect.py
-rwxrwxrwx 1 Informaticauser informatica    1095 Oct 16 11:46 hdtl.sh_VAF
-rwxrwxrwx 1 Informaticauser informatica    1095 Oct 16 11:50 hdtl.sh
-rw-r--r-- 1 Informaticauser informatica   19932 Oct 16 11:57 OutFile.txt
Informaticauser@server1 DEV $ cat xargvinput.pl
#!/usr/bin/perl
use strict;
use warnings;
print "Enter the filename you want to drop column from, ensure have a backup of old file\n";
my $filename = <STDIN>;
chomp $filename;
print "Enter teh number of columns you want to replace\n";
my $limit = <STDIN>;
chomp $limit;
my @arr1;
while (my @arr < $limit) {
   print "Enter the position number\n";
    my $num = <STDIN>;
    chomp $num;
    push @arr, $num;
push(@arr1, @arr);
$limit--;
}
open(WR,"> drop_columns.txt");
print WR join ",", @arr1;
close WR;
print join ",", @arr1;
print "\n";
my $del_col=`cat drop_columns.txt`;
chomp $del_col;
my $cmd1 ="cut --complement -f $del_col -d '|' $filename > $filename.chopped_file.txt";
system(" $cmd1 ");
print " result fiel is $filename.chopped_file.txt\n";

Informaticauser@server1 DEV $ cat txttocsv.py
#!/usr/bin/tpython

import os
import sys
import csv

sourcefile = sys.argv[1]
targetfile = sys.argv[2]
ofile = open(targetfile, "ab")
writer = csv.writer(ofile, dialect='excel', delimiter=',')
list=[]
file_reader = open(sourcefile, "r")
for row in file_reader:
        row=row.replace("|",",")
        row=row.strip("\n")
        list.append(row)
        writer.writerows([x.split(',') for x in list])
        list=[]
file_reader.close()
ofile.close()
Informaticauser@server1 DEV $ cat template_color_coding_excel.py
import csv
import sys
from xlwt import Workbook, easyxf
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
cell = easyxf('pattern: pattern solid, fore_colour red')
for row_number in range(0,rowrange,2):
    for column_number in range(columnrange):
        with open('Difference_csv.csv', 'rb') as f:
         rows = list(csv.reader(f))
         if rows[row_number][column_number] == rows[row_number+1][column_number]:
             text1 = rows[row_number][column_number]
             text2 = rows[row_number+1][column_number]
             print rows[row_number][column_number] + " matched " + rows[row_number+1][column_number]
             sheet1.write(row_number,column_number,text1)
             sheet1.write(row_number+1,column_number,text2)
         else:
            print rows[row_number][column_number] + " notmatched " + rows[row_number+1][column_number]
            text3 = rows[row_number][column_number]
            text4 = rows[row_number+1][column_number]
            sheet1.write(row_number,column_number,text3,cell)
            sheet1.write(row_number+1,column_number,text4,cell)
book.save('Compare_result.xls')
Informaticauser@server1 DEV $ cat run_trigger.sh
sh menu.sh
perl trigger
Informaticauser@server1 DEV $ cat process_final_upload.pl
print "Please enter the fullpath of file eg /home/id/abc.csv \n";
my $filename_path = <STDIN>;
chop($filename_path);
$cmd2 ="sed -i 's#csvfilewithfullpath#$filename_path#g' final_upload.txt";
$fieldwithcomma =`cat pre_teradata_upld2.txt`;
$fieldwithcolon =`cat pre_teradata_upld1.txt`;
$databasetable =`cat database_table.txt`;
chomp ($fieldwithcomma);
chomp ($fieldwithcolon);
chomp ($databasetable);
$cmd3 ="sed -i 's#fieldwithcomma#$fieldwithcomma#g' final_upload.txt";
$cmd4 ="sed -i 's#fieldwithcolon#$fieldwithcolon#g' final_upload.txt";
$cmd5 ="sed -i 's#database.tablename#$databasetable#g' final_upload.txt";
system ("$cmd2");
system ("$cmd3");
system ("$cmd4");
system ("$cmd5");

Informaticauser@server1 DEV $ cat pre_upload_template2.txt

insert into database.tablename (fieldwithcomma)
values(fieldwithcolon);
.logoff;
Informaticauser@server1 DEV $ cat delinsert.pl
#!/usr/bin/perl
use strict;
use warnings;
print "Enter teh field position filename\n";
my $filename = <STDIN>;
print "Enter the unzip .gz text file to insert delimiter in \n";
my $convertfile = <STDIN>;
chomp $filename;
chomp $convertfile;
open(my $fh, '<:encoding(UTF-8)', $filename)
or die "Could not open file '$filename' $!";

while (my $row = <$fh>) {
chomp $row;
`sed -i 's/./&|/$row' $convertfile`;
print "$row\n";
}
#`cat datatest.txt`;
Informaticauser@server1 DEV $ cat csvtoxls.py
import csv, xlwt
import os
import sys
sourcefile = raw_input('Enter a sourcefile name:\n ')
targetfile = raw_input('Enter a targetfile name:\n ')

f = open("%s" % sourcefile, 'rb')
reader = csv.reader(f)

workbook = xlwt.Workbook()
sheet = workbook.add_sheet("Sheet 1")

#mystyle = xlwt.easyxf('pattern: pattern solid, fore_colour red')
for rowi, row in enumerate(reader):
    for coli, value in enumerate(row):
        sheet.write(rowi,coli,value)

workbook.save("%s" % targetfile)
Informaticauser@server1 DEV $ cat csvtotxt.py
#!/usr/bin/tpython

import os
import sys
import csv
sourcefile = sys.argv[1]
targetfile = sys.argv[2]
ofile = open(sourcefile, "rb")
for row in ofile:
        row = row.replace("\t", "|")
        row = row.strip()
        list.append(row)
ofile.close()
#print list
file_write= open(targetfile,"a")
for row in list:
        file_write.write(row)
        file_write.write("\n")
file_write.close()
Informaticauser@server1 DEV $ cat countcolumn.py
import sys
import os
import csv
with open('Difference_csv.csv', 'rb') as f:
    reader = csv.reader(f, delimiter=',', skipinitialspace=True)
    first_row = next(reader)
    num_cols = len(first_row)
    f1 = open( 'color_columns.txt', 'w' )
    f1.write("%d" % num_cols)
    f1.close()
    print num_cols
Informaticauser@server1 DEV $ cat compare
#!/usr/bin/perl -W
use strict;
use warnings;
my $f1 = $ARGV[0];
my $f2 = $ARGV[1];
my $outfile = "Difference_$ARGV[0].$ARGV[1]";
my %results = ();
open FILE1, "$f1" or die "Could not open file: $! \n";
while(my $line = <FILE1>){   $results{$line}=1;
}
close(FILE1);
open FILE2, "$f2" or die "Could not open file: $! \n";
while(my $line =<FILE2>) {
 $results{$line}++;
}
close(FILE2);
open (OUTFILE, ">$outfile") or die "Cannot open $outfile for writing \n";
foreach my $line (keys %results) { print OUTFILE $line if $results{$line} == 1;
}
close OUTFILE;
my $cmd6="cat $outfile | sort >$outfile.final.txt";
my $cmd7="rm -rf $outfile";
system (" $cmd6 && $cmd7 ");
Informaticauser@server1 DEV $ cat colorcoding.sh
alias python='/home/userid1234567/python/bin/python2.7'
export PATH=/home/userid1234567/python/bin:$PATH
python color_coding_excel.py
Informaticauser@server1 DEV $ cat color_coding_excel.py
import csv
import sys
from xlwt import Workbook, easyxf
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
cell = easyxf('pattern: pattern solid, fore_colour red')
for row_number in range(0,0,2):
    for column_number in range():
        with open('Difference_csv.csv', 'rb') as f:
         rows = list(csv.reader(f))
         if rows[row_number][column_number] == rows[row_number+1][column_number]:
             text1 = rows[row_number][column_number]
             text2 = rows[row_number+1][column_number]
             print rows[row_number][column_number] + " matched " + rows[row_number+1][column_number]
             sheet1.write(row_number,column_number,text1)
             sheet1.write(row_number+1,column_number,text2)
         else:
            print rows[row_number][column_number] + " notmatched " + rows[row_number+1][column_number]
            text3 = rows[row_number][column_number]
            text4 = rows[row_number+1][column_number]
            sheet1.write(row_number,column_number,text3,cell)
            sheet1.write(row_number+1,column_number,text4,cell)
book.save('Compare_result.xls')
Informaticauser@server1 DEV $ cat bteq_upload.wrapper.txt
rm -rf Table_details.txt
cp template_bteq_help_fields.txt bteq_help_fields.txt
perl bteq_perl_trigger.pl
bteq < bteq_help_fields.txt
cat Table_details.txt | cut -d '|' -f1 > teradata_fields.txt
cat Table_details.txt | cut -d '|' -f7 > teradata_field_length.txt
sed -i '1d' teradata_fields.txt teradata_field_length.txt
sed -i 's/4/40/g' teradata_field_length.txt
sed -i 's/ //g' teradata_field_length.txt teradata_fields.txt
cp teradata_fields.txt pre_teradata_fields.txt
cp teradata_fields.txt pre_teradata_fields1.txt
sed -i 's/$/ (varchar(/' teradata_fields.txt
paste teradata_fields.txt teradata_field_length.txt | sed 's/\t//g' >pre_query.txt
sed -i 's/$/)),/' pre_query.txt
sed -i '$s/,$//' pre_query.txt
sed -i 's/^/:/' pre_teradata_fields.txt
sed -i 's/$/,/' pre_teradata_fields.txt
sed -i '$s/,$//' pre_teradata_fields.txt
sed -i 's/$/,/' pre_teradata_fields1.txt
sed -i '$s/,$//' pre_teradata_fields1.txt
cat pre_teradata_fields.txt | xargs | sed 's/ //g' > pre_teradata_upld1.txt
cat pre_teradata_fields1.txt | xargs | sed 's/ //g' > pre_teradata_upld2.txt
rm -rf pre_teradata_fields.txt pre_teradata_fields1.txt teradata_fields.txt teradata_field_length.txt
cat pre_upload_template1.txt > final_upload.txt
cat pre_query.txt >> final_upload.txt
cat pre_upload_template2.txt >> final_upload.txt
perl process_final_upload.pl
rm -rf pre_teradata_upld1.txt pre_teradata_upld2.txt pre_query.txt database_table.txt Table_details.txt
#bteq < final_upload.txt
Informaticauser@server1 DEV $ cat bteq_field_position.wrap
cat: bteq_field_position.wrap: No such file or directory
Informaticauser@server1 DEV $ cat bteq_field_position.wrapper.txt
rm -rf Table_details.txt
cp template_bteq_help_fields.txt bteq_help_fields.txt
perl bteq_perl_trigger.pl
bteq < bteq_help_fields.txt
cat Table_details.txt | cut -d '|' -f1 > teradata_fields.txt
sed -i '1d' teradata_fields.txt
sed -i 's/ //g' teradata_fields.txt
cat -n teradata_fields.txt > teradata_fields_positions.txt

Informaticauser@server1 DEV $ cat bteq_perl_trigger.pl
#!/usr/bin/perl
print "Please enter the database.tablename which you want to upload csv/file \n";
my $database_tablename = <STDIN>;
chop($database_tablename);
my $path=`pwd`;
chomp $path;
$cmd1 ="sed -i 's#TS_72258_BASELDB.rupesh_cdc2#$database_tablename#g' $path/bteq_help_fields.txt";
system ("$cmd1");
open(WR1,">$path/database_table.txt");
print  WR1 ("$database_tablename");
close WR1;
Informaticauser@server1 DEV $ cat oracleconnect1.py
import cx_Oracle
import csv
import sys
table = raw_input('Enter tablename: ')
host = 'hostname'
port = portnumber
SERVICE_NAME = 'servicename'
login = 'Oracle_id'
passwrd = 'oracle_password'
SID = 'oracle_sid'
dsn = cx_Oracle.makedsn(host, port, SID).replace('SID','SERVICE_NAME')
con = cx_Oracle.connect(login, passwrd, dsn)

cur = con.cursor()

cur.execute('select * from %s' %(table))
row = cur.fetchall()
#print row
c = csv.writer(open("oracle_output1.csv","wb"), delimiter="|")
c.writerows(row)
con.close()
Informaticauser@server1 DEV $ cat oraclerun1.sh
alias python='/home/Informaticauser/python/bin/python2.7'
export PATH=/home/Informaticauser/python/bin:$PATH
python oracleconnect.py
Informaticauser@server1 DEV $ cat oraclerun.sh
alias python='/home/Informaticauser/python/bin/python2.7'
export PATH=/home/Informaticauser/python/bin:$PATH
python oracleconnect.py
Informaticauser@server1 DEV $ cat csvtoxls.sh
alias python='/home/Informaticauser/python/bin/python2.7'
export PATH=/home/Informaticauser/python/bin:$PATH
python csvtoxls.py
Informaticauser@server1 DEV $ cat count.sh
alias python='/home/Informaticauser/python/bin/python2.7'
export PATH=/home/Informaticauser/python/bin:$PATH
python countcolumn.py
Informaticauser@server1 DEV $ cat id_password_setup.pl
#!/usr/bin/perl
print "
Select from below options to setup id and password:\n
1. First time userid setup (enter ur linux userid, ensure you place the unzip tar files at home folder)
2. Teardata
3. DB2
4. Oracle
5. Change default value of RDSA for DB2
6. Oracle for 2 different source or database
\n:";
my $useropt = <STDIN>;
chomp $useropt;
 if ($useropt eq 0){
    exit 0;
    }

elsif($useropt ==1){
my $pwd=`pwd`;
`chmod 777 *`;
chomp $pwd;
my $BKPPATH="$pwd"."/BKP/oraclerun.sh oraclerun1.sh .profile csvtoxls.sh count.sh";
chomp $BKPPATH;
my $copyrun="cp -rf $BKPPATH $pwd";
my $cmd1="cd $pwd/BKP";
system (" $cmd1 && $copyrun ");
print "$copyrun\n";
print "Enter the your linuxid eg syssitdmmmmuuu \n";
my $linuxid = <STDIN>;
chomp $linuxid;
`sed -i 's/userid/$linuxid/g' .profile oraclerun.sh oraclerun1.sh csvtoxls.sh count.sh`;
print "Updated Sucessfully\n";
}

elsif($useropt ==2){
my $pwd=`pwd`;
`chmod 777 *`;
chomp $pwd;
my $BKPPATH="$pwd"."/BKP/template_bteq_help_fields.txt pre_upload_template1.txt bteq_help_fields.txt";
chomp $BKPPATH;
my $copyrun="cp -rf $BKPPATH $pwd";
my $cmd1="cd $pwd/BKP";
system (" $cmd1 && $copyrun ");
print "$copyrun\n";
print "Enter the your Teradataid eg Informaticauser \n";
my $teradata_id = <STDIN>;
chomp $teradata_id;
print "Enter the your Teradata Password eg Informaticauser \n";
my $teradata_pass = <STDIN>;
chomp $teradata_pass;
`sed -i 's/Teradataid/$teradata_id/g' template_bteq_help_fields.txt pre_upload_template1.txt bteq_help_fields.txt`;
`sed -i 's/Teradatapassword/$teradata_pass/g' template_bteq_help_fields.txt pre_upload_template1.txt bteq_help_fields.txt`;
my $BKPPATH1="$pwd"."/CODE/BKP/td_pre.txt";
my $BKPPATH2="$pwd"."/CODE/BKP/td2.txt";
my $BKPPATH3="$pwd"."/CODE/BKP/td.txt";
chomp $BKPPATH1;
chomp $BKPPATH2;
chomp $BKPPATH3;
my $copyrun1="cp -rf $BKPPATH1 $pwd/CODE";
my $copyrun2="cp -rf $BKPPATH2 $pwd/CODE";
my $copyrun3="cp -rf $BKPPATH3 $pwd/CODE";
my $cmd11="cd $pwd/CODE/BKP";
system (" $cmd11 && $copyrun1 ");
system (" $cmd11 && $copyrun2 ");
system (" $cmd11 && $copyrun3 ");
print "$copyrun1\n";
`sed -i 's/Teradataid/$teradata_id/g' $pwd/CODE/td_pre.txt $pwd/CODE/td2.txt $pwd/CODE/td.txt`;
`sed -i 's/Teradatapassword/$teradata_pass/g' $pwd/CODE/td_pre.txt $pwd/CODE/td2.txt $pwd/CODE/td.txt`;
print "Updated Sucessfully\n";
}

elsif($useropt ==3){
my $pwd=`pwd`;
`chmod 777 *`;
chomp $pwd;
my $BKPPATH="$pwd"."/CODE/BKP/Data_setup";
chomp $BKPPATH;
my $copyrun="cp -rf $BKPPATH $pwd/CODE";
my $cmd1="cd $pwd/CODE/BKP";
system (" $cmd1 && $copyrun ");
print "$copyrun\n";
print "Enter the your Mainframeid eg bzyan1 \n";
my $mainframe_id = <STDIN>;
chomp $mainframe_id;
print "Enter the your Mainframepassword:
(Important:password with spcial character '@' is not supported)\n";
my $mainframe_pass = <STDIN>;
chomp $mainframe_pass;
`sed -i 's/mainframeid/$mainframe_id/g' $pwd/CODE/Data_setup`;
`sed -i 's/mainframepassword/$mainframe_pass/g' $pwd/CODE/Data_setup`;
print "Updated Sucessfully\n";

}

elsif($useropt ==4){
my $pwd=`pwd`;
`chmod 777 *`;
chomp $pwd;
my $BKPPATH="$pwd"."/BKP/oracleconnect.py";
chomp $BKPPATH;
my $copyrun="cp -rf $BKPPATH $pwd";
my $cmd1="cd $pwd/BKP";
system (" $cmd1 && $copyrun ");
print "$copyrun\n";
print "Enter the your Oraclehostname eg abjt.wload.company.co.uk \n";
my $oracle_hostname = <STDIN>;
chomp $oracle_hostname;
print "Enter port number eg 7896:\n";
my $port_no = <STDIN>;
chomp $port_no;
print "Enter servicename eg TPTCVS.wload.company.co.uk\n";
my $service_name = <STDIN>;
chomp $service_name;
print "Enter oracle SID eg TPTCVS.wload.company.co.uk\n";
my $sid_name = <STDIN>;
chomp $sid_name;
print "Enter username eg RO_USER:\n";
my $oracle_user = <STDIN>;
chomp $oracle_user;
print "Enter password:\n";
my $oracle_pass = <STDIN>;
chomp $oracle_pass;
`sed -i 's/hostname/$oracle_hostname/g' oracleconnect.py`;
`sed -i 's/portnumber/$port_no/g' oracleconnect.py`;
`sed -i 's/servicename/$service_name/g' oracleconnect.py`;
`sed -i 's/Oracle_id/$oracle_user/g' oracleconnect.py`;
`sed -i 's/oracle_password/$oracle_pass/g' oracleconnect.py`;
`sed -i 's/oracle_sid/$sid_name/g' oracleconnect.py`;
print "Updated Sucessfully\n";

}

elsif($useropt ==5){
my $pwd=`pwd`;
`chmod 777 *`;
chomp $pwd;
my $BKPPATH="$pwd"."/CODE/BKP/Data_setup";
chomp $BKPPATH;
my $copyrun="cp -rf $BKPPATH $pwd/CODE";
my $cmd1="cd $pwd/CODE/BKP";
system (" $cmd1 && $copyrun ");
print "$copyrun\n";
print "Enter the SID eg RDSA default is RDSA\n";
my $rdsa_id = <STDIN>;
chomp $rdsa_id;
`sed -i 's/RDSA/$rdsa_id/g' $pwd/CODE/Data_setup`;
print "Updated Sucessfully\n";

}

elsif($useropt ==6){
my $pwd=`pwd`;
`chmod 777 *`;
chomp $pwd;
my $BKPPATH="$pwd"."/BKP/oracleconnect.py";
chomp $BKPPATH;
my $copyrun="cp -rf $BKPPATH $pwd";
my $cmd1="cd $pwd/BKP";
system (" $cmd1 && $copyrun ");
print "$copyrun\n";
print "****************Deatils for first oracle database***************\n";
print "Enter the your Oraclehostname eg abjt.wload.company.co.uk \n";
my $oracle_hostname = <STDIN>;
chomp $oracle_hostname;
print "Enter port number eg 7896:\n";
my $port_no = <STDIN>;
chomp $port_no;
print "Enter servicename eg TPTCVS.wload.company.co.uk\n";
my $service_name = <STDIN>;
chomp $service_name;
print "Enter oracle SID eg TPTCVS.wload.company.co.uk\n";
my $sid_name = <STDIN>;
chomp $sid_name;
print "Enter username eg RO_USER:\n";
my $oracle_user = <STDIN>;
chomp $oracle_user;
print "Enter password:\n";
my $oracle_pass = <STDIN>;
chomp $oracle_pass;
`sed -i 's/hostname/$oracle_hostname/g' oracleconnect.py`;
`sed -i 's/portnumber/$port_no/g' oracleconnect.py`;
`sed -i 's/servicename/$service_name/g' oracleconnect.py`;
`sed -i 's/Oracle_id/$oracle_user/g' oracleconnect.py`;
`sed -i 's/oracle_password/$oracle_pass/g' oracleconnect.py`;
`sed -i 's/oracle_sid/$sid_name/g' oracleconnect.py`;
print "Updated for first Oracledb Sucessfully\n";

my $pwd=`pwd`;
`chmod 777 *`;
chomp $pwd;
my $BKPPATH="$pwd"."/BKP/oracleconnect1.py";
chomp $BKPPATH;
my $copyrun="cp -rf $BKPPATH $pwd";
my $cmd1="cd $pwd/BKP";
system (" $cmd1 && $copyrun ");
print "$copyrun\n";
print "****************Deatils for Second oracle database***************\n";
print "Enter the your Oraclehostname eg abjt.wload.company.co.uk \n";
my $oracle_hostname = <STDIN>;
chomp $oracle_hostname;
print "Enter port number eg 7896:\n";
my $port_no = <STDIN>;
chomp $port_no;
print "Enter servicename eg TPTCVS.wload.company.co.uk\n";
my $service_name = <STDIN>;
chomp $service_name;
print "Enter oracle SID eg TPTCVS.wload.company.co.uk\n";
my $sid_name = <STDIN>;
chomp $sid_name;
print "Enter username eg RO_USER:\n";
my $oracle_user = <STDIN>;
chomp $oracle_user;
print "Enter password:\n";
my $oracle_pass = <STDIN>;
chomp $oracle_pass;
`sed -i 's/hostname/$oracle_hostname/g' oracleconnect1.py`;
`sed -i 's/portnumber/$port_no/g' oracleconnect1.py`;
`sed -i 's/servicename/$service_name/g' oracleconnect1.py`;
`sed -i 's/Oracle_id/$oracle_user/g' oracleconnect1.py`;
`sed -i 's/oracle_password/$oracle_pass/g' oracleconnect1.py`;
`sed -i 's/oracle_sid/$sid_name/g' oracleconnect1.py`;
print "Updated for second Oracle db Sucessfully\n";

}

else {
     print "Incorrect option give correct option\n";
     my $useroption = <STDIN>;
     }

Informaticauser@server1 DEV $ cat menu.sh
reset
echo -e "\e[1;32;40m\e[0m"
echo -e "\e[1;32;5m                         ==== DTAS (Datawarehouse Test Automation Suite) ====                          \e[0m"
echo -e "\e[1;32;40m\e[0m"
echo -e "\e[1;32;5m  File Comparision: \e[0m"
echo -e "\e[1;32;40m    For File comparison enter 1 \e[0m"
echo -e "\e[1;32;5m  File Transformations: \e[0m"
echo -e "\e[1;32;40m    For converting pipe delimited to CSV file enter 2 \e[0m"
echo -e "\e[1;32;40m    For converting pipe delimited to excel enter 3 \e[0m"
echo -e "\e[1;32;40m    For converting convert fixed width to pipe delimited enter 4 \e[0m"
echo -e "\e[1;32;5m  Data Unload/Upload utilities: \e[0m"
echo -e "\e[1;32;40m    For table (TERADATA/DB2) unload (DB2 unload tested on informatica 76/84 server only) enter 5 \e[0m"
echo -e "\e[1;32;40m    For load file in teradata using BTEQ enter 6 \e[0m"
echo -e "\e[1;32;40m    For Unloading data from Hive to pipe dilimited file (support JSON/PARQUET/AVRO/CSV formats) enter 7 \e[0m"
echo -e "\e[1;32;40m    For Oracle unload (tested on informatica 76/84 server only) enter 8 \e[0m"
echo -e "\e[1;32;40m    For Oracle unload from two databases enter 9 \e[0m"
echo -e "\e[1;32;40m    Load file in DB2/Oracle - future development ( In progress ) \e[0m"
echo -e "\e[1;32;5m  File Processing utilities: \e[0m"
echo -e "\e[1;32;40m    For getting linenumber in file  enter 10 \e[0m"
echo -e "\e[1;32;40m    For  dropping colums from file enter 11 \e[0m"
echo -e "\e[1;32;5m  Database Utilities: \e[0m"
echo -e "\e[1;32;40m    For Getting  position number and field details teradata enter 12 \e[0m"
echo -e "\e[1;32;40m    For Getting position number and field details hive enter 13 \e[0m"
echo -e "\e[1;32;40m    For Housekeeping enter 14 \e[0m"
echo -e "\e[1;32;5m  Mainframe DB2 file create Utilities: \e[0m"
echo -e "\e[1;32;40m    For creating Db2 unload monthly files  enter 15 \e[0m"
echo -e "\e[1;32;40m    For setting ID and Passwords  enter 16 \e[0m"
echo -e "\e[1;32;40m    For db2 unload using portable program works on any server 17 \e[0m"
echo -e "\e[1;32;40m    For exit enter 0 \e[0m"
echo -e "\e[1;32;5m                                                                                                       \e[0m"
Informaticauser@server1 DEV $ cat trigger
#!/usr/bin/perl
print "\n:";
my $useropt = <STDIN>;
chomp $useropt;
 if ($useropt eq 0){
    exit 0;
    }

elsif($useropt ==1){

print "Enter the first filename to compare \n";
my $filename = <STDIN>;
chomp $filename;
print "Enter the second filename to compare \n";
my $filename1 = <STDIN>;
chomp $filename1;
$cmd_sort1 ="sort <$filename >a.$filename";
$cmd_sort2 ="sort <$filename1 >b.$filename1";
system (" $cmd_sort1 & $cmd_sort2 ");
$count_file1 = `cat $filename |wc -l`;
$count_file2 = `cat $filename1 | wc -l`;
if ($count_file1 eq $count_file2)
{
$cmd4="perl compare a.$filename b.$filename1";
system ("$cmd4");
`rm a.$filename b.$filename1`;
print "Difference will be in file Difference_a.$filename.b.$filename1.final.txt\n\n";
print "Converting teh difference file to csv\n";
$cmd5 ="python txttocsv.py Difference_a.$filename.b.$filename1.final.txt Difference_csv.csv";
system (" $cmd5 ");
`rm Difference_a.$filename.b.$filename1.final.txt`;
print " Colorcoding and convert to xls format\n ";
$csv_row_count =`cat Difference_csv.csv | wc -l`;
chomp $csv_row_count;
`sh count.sh`;
$csv_column_count = `cat color_columns.txt`;
chomp $csv_column_count;
$cmd_copy_template = "cp template_color_coding_excel.py color_coding_excel.py";
system (" $cmd_copy_template ");
$cmd7 ="sed -i 's/rowrange/$csv_row_count/g' color_coding_excel.py";
$cmd8 ="sed -i 's/columnrange/$csv_column_count/g' color_coding_excel.py";
system (" $cmd7 ");
system (" $cmd8 ");
`sh colorcoding.sh`;
`rm Difference_csv.csv color_columns.txt`;
print "final excel is Compare_result.xls \n";
}
else {
$cmd_cmp = "grep -v -F -f a.$filename b.$filename1 > not_matched_records.txt";
system (" $cmd_cmp ");
print "unmatched records in file not_matched_records.txt\n";
}
}
elsif($useropt ==5){
$path =`pwd`;
chomp $path;
$cmd1="perl $path/CODE/Data_setup";
system (" $cmd1 ");
}
elsif($useropt ==2){
print "Enter the inputfile name\n";
my $inputfile = <STDIN>;
chomp $inputfile;
print "Enter output filename\n";
my $outputfile = <STDIN>;
chomp $outputfile;
$cmd1 ="python txttocsv.py $inputfile $outputfile";
system (" $cmd1 ");
}
elsif($useropt ==10){
print "Enter the inputfile name\n";
my $inputfile = <STDIN>;
chomp $inputfile;
$cmd1 ="cat -n $inputfile > $inputfile.with.linenumber";
system (" $cmd1 ");
print " result in $inputfile.with.linenumber\n ";
}

elsif($useropt ==3){
print "Enter the inputfile followed by output filename \n";
`sh csvtoxls.sh`;
}

elsif($useropt ==4){
$cmd1="perl delinsert.pl";
system (" $cmd1 ");
}
elsif($useropt ==11){
$cmd1="perl xargvinput.pl";
system (" $cmd1 ");
`rm drop_columns.txt`;
}
elsif($useropt ==6){
$cmd1="sh bteq_upload.wrapper.txt";
system (" $cmd1 ");

}
elsif($useropt ==7){
print "Enter the table data to unload in format database.tablename and final result will be in database.tablename.txt file \n";
my $filename = <STDIN>;
chomp $filename;
$path=`pwd`;
chomp $path;
$cmd1 ="hive -e 'select * from $filename' > $path/$filename.txt";
system (" $cmd1 ");
system (" sleep 2");
$cmd2="sed -i 's/[[:space:]]\\+/|/g' $filename.txt";
#print " $cmd2\n";
system (" $cmd2 ");
}

elsif($useropt ==12){
$cmd1="sh bteq_field_position.wrapper.txt";
system (" $cmd1 ");
`rm teradata_fields.txt database_table.txt bteq_help_fields.txt Table_details.txt`;
print " output is in file teradata_fields_positions.txt \n";
}

elsif($useropt ==13){
print "Enter the table details in format database.tablename and final result will be in database.tablename.positions.txt file \n";
my $filename = <STDIN>;
chomp $filename;
$path=`pwd`;
chomp $path;
$cmd1 ="hive -e 'describe $filename' > $path/$filename.pos.txt";
system (" $cmd1 ");
$cmd2 ="cat -n $path/$filename.pos.txt > $path/$filename.positions.txt";
$cmd3 ="rm -rf $path/$filename.pos.txt";
system (" $cmd2 ");
system (" $cmd3 ");
}

elsif($useropt ==8){
print " Enter the oracle details in schema.tablename format\n";
`sh oraclerun.sh`;
print " output is in oracle_output.csv\n";
}

elsif($useropt ==9){
print " Enter the oracle details in schema.tablename format\n";
`sh oraclerun.sh`;
print " output is in oracle_output.csv\n";
print " Enter the oracle details in schema.tablename format\n";
`sh oraclerun1.sh`;
print " output is in oracle_output1.csv\n";

}

elsif($useropt ==14){
`sh cln.sh`;
print "Deleted all files, only source code files present\n";
}

elsif($useropt ==15){
print "For adding header/tailer for customer system monthly files\n";
print " Enter date in format DDMMYYYY\n";
my $date = <STDIN>;
chomp $date;
print " Enter filename to add header and tailor into\n";
my $filename = <STDIN>;
chomp $filename;
print " Enter Target file \n";
my $target = <STDIN>;
chomp $target;
`sh hdtl.sh $date $filename $target`;
print " output file will be FINAL_RDFP_$target._.$date.dat \n";
}

elsif($useropt ==16){
system ("reset");
system (" perl id_password_setup.pl ");
}

elsif($useropt ==17){
my $pwd=`pwd`;
chomp $pwd;
print "For DB2 table unload runs on any server\n";
print " Enter userid\n";
my $userid = <STDIN>;
chomp $userid;
print " Enter password:\n";
my $password = <STDIN>;
chomp $password;
print " Enter creator.tablename eg DJY0APD.TDLBAHO \n";
my $table = <STDIN>;
chomp $table;
`java -jar db2.jar $userid $password $table`;
`sleep 5`;
`sh c.sh`;
print " output file will be OutFile.txt \n\n";
    }

else {
     print "Incorrect option give correct option\n";
     my $useroption = <STDIN>;
     }
Informaticauser@server1 DEV $ cat cln.sh
chmod 777 *
shopt -s extglob
rm !(xargvinput.pl|db2.jar|c.sh|txttocsv.py|hdtl.sh|template_color_coding_excel.py|run_trigger.sh|process_final_upload.pl|pre_upload_template2.txt|delinsert.pl|csvtoxls.py|csvtotxt.py|countcolumn.py|compare|colorcoding.sh|color_coding_excel.py|cln.sh|bteq_upload.wrapper.txt|bteq_field_position.wrapper.txt|oraclerun.sh|csvtoxls.sh|count.sh|.profile|bteq_perl_trigger.pl|template_bteq_help_fields.txt|pre_upload_template1.txt|bteq_help_fields.txt|oracleconnect.py|oraclerun1.sh|id_password_setup.pl|menu.sh|trigger|oracleconnect1.py)
shopt -u extglob
Informaticauser@server1 DEV $ cat c.sh
sed -i 's|\([0-9][0-9][0-9][0-9]\)-\([0-1][0-9]\)-\([0-3][0-9]\)|\3.\2.\1|g' OutFile.txt
sed -i 's/null//g' OutFile.txt
sed -i 's/.$//' OutFile.txt
Informaticauser@server1 DEV $ cat template_bteq_help_field
cat: template_bteq_help_field: No such file or directory
Informaticauser@server1 DEV $ cat template_bteq_help_fields.txt
.logon dwsoat.dws.company.co.uk/Informaticauser,pasWtT04;
.SET WIDTH 65531;
.SET SEPARATOR '|' ALL;
.SET NULL AS "";
.SET FORMAT OFF;
.SET ECHOREQ OFF;
.SET FOOTING OFF;
.SET HEADING OFF;
.SET INDICDATA OFF;
.SET SPACE 0;
.SET RECORDMODE OFF;
.SET RETLIMIT 2147483407,2048;
.SET ECHOREQ OFF;
.SET TITLEDASHES OFF;
.EXPORT REPORT FILE=Table_details.txt
help table TS_72258_BASELDB.rupesh_cdc2;
Informaticauser@server1 DEV $ cat pre_upload_template1.txt
.logon dwsoat.dws.company.co.uk/Informaticauser,pasWtT04;
.import vartext ',' file ='csvfilewithfullpath'
.repeat *;
using
Informaticauser@server1 DEV $ cat bteq_help_fields.txt
.logon dwsoat.dws.company.co.uk/Informaticauser,pasWtT04;
.SET WIDTH 65531;
.SET SEPARATOR '|' ALL;
.SET NULL AS "";
.SET FORMAT OFF;
.SET ECHOREQ OFF;
.SET FOOTING OFF;
.SET HEADING OFF;
.SET INDICDATA OFF;
.SET SPACE 0;
.SET RECORDMODE OFF;
.SET RETLIMIT 2147483407,2048;
.SET ECHOREQ OFF;
.SET TITLEDASHES OFF;
.EXPORT REPORT FILE=Table_details.txt
help table TS_72258_BASELDB.rupesh_cdc2;
Informaticauser@server1 DEV $ cat oracleconnect.py
import cx_Oracle
import csv
import sys
table = raw_input('Enter tablename: ')
host = 'abjt.wload.company.co.uk'
port = 7869
SERVICE_NAME = 'TPTCVS.wload.company.co.uk'
login = 'RO_USER'
passwrd = 'Reet'
SID = 'TPTCVS.wload.company.co.uk'
dsn = cx_Oracle.makedsn(host, port, SID).replace('SID','SERVICE_NAME')
con = cx_Oracle.connect(login, passwrd, dsn)

cur = con.cursor()

cur.execute('select * from %s' %(table))
row = cur.fetchall()
#print row
c = csv.writer(open("oracle_output.csv","wb"), delimiter="|")
c.writerows(row)
con.close()
Informaticauser@server1 DEV $ cat hdtl.sh
echo "3 parameter required for this script"
echo "1st Parameter Business date DDMMYYYY: $1"
echo "2nd Parameter Chopped File name : $2"
echo "Customer File Name: $3"
#. ./header.sh $1
Intial=00FDLH0100
#Business_Date=$1
End=01
echo "$Intial $1$End" >header_$1.dat

#. ./trailer.sh $1 $2
IntTr=99FDLH010
#BUS_DATE=$1
TEMP_NUM=2
REC_COUNT_1=`cat ./$2| wc -l`
REC_COUNT_2=2
REC_COUNT_3=`expr $REC_COUNT_1 + $REC_COUNT_2`
#echo $REC_COUNT_1
#echo $REC_COUNT_3
REC_COUNT_4=00000000
#REC_COUNT=`expr $REC_COUNT_3 + $REC_COUNT_4`
REC_COUNT=`printf "%010d" $REC_COUNT_3`
#echo $REC_COUNT
#echo "$REC_COUNT$REC_COUNT_4"
echo "99FDLH010$1$REC_COUNT$REC_COUNT_4" >trailer_$1.dat

#. ./complete_file.sh $1 $2 $3
sed -i 's/|/|ÇÞ|/g' $2 >$3_Temp_$(date +'%m%d%y')
sed -i 's|\([0-9][0-9][0-9][0-9]\)-\([0-1][0-9]\)-\([0-3][0-9]\)|\3.\2.\1|g' $3_Temp_$(date +'%m%d%y')
sed -i 's/null//g' $3_Temp_$(date +'%m%d%y')
cat header_$1.dat>>FINAL_RDFP_$3_$1.dat
cat $3_Temp_$(date +'%m%d%y')>>FINAL_RDFP_$3_$1.dat
cat trailer_$1.dat>>FINAL_RDFP_$3_$1.dat

rm -f  header_$1.dat trailer_$1.dat $3_Temp_$(date +'%m%d%y')

Informaticauser@server1 DEV $

==========================================================================================================
Informaticauser@server1 DEV $ cd CODE
Informaticauser@server1 DEV $ ll
total 32
-rwxrwxrwx 1 Informaticauser informatica   99 Oct  5 08:10 Query_append2.txt
-rwxrwxrwx 1 Informaticauser informatica   71 Oct  5 08:10 Query_append.txt
drwxrwxrwx 2 Informaticauser informatica 4096 Oct  5 12:36 BKP
-rwxrwxrwx 1 Informaticauser informatica  311 Oct 15 05:00 td_pre.txt
-rwxrwxrwx 1 Informaticauser informatica  410 Oct 15 05:00 td2.txt
-rwxrwxrwx 1 Informaticauser informatica  408 Oct 15 05:00 td.txt
-rwxrwxrwx 1 Informaticauser informatica 4259 Oct 15 05:00 Data_setup
Informaticauser@server1 DEV $ pwd
/home/Informaticauser/FRAMEWORK_PACK/CODE
Informaticauser@server1 DEV $ cat Query_append2.txt
.EXPORT REPORT FILE=TS_72258_BASELDB.sample_alok1.txt
select * from TS_72258_BASELDB.sample_alok1;
Informaticauser@server1 DEV $ cat Query_append.txt
.EXPORT REPORT FILE=DLY0APD.TVFPKAG.txt
select * from DLY0APD.TVFPKAG;
Informaticauser@server1 DEV $ cat td_pre.txt
.logon dwsoat.dws.company.co.uk/Informaticauser,pasWtT04;
.SET WIDTH 65531;
.SET SEPARATOR '|' ALL;
.SET NULL AS "";
.SET FORMAT OFF;
.SET ECHOREQ OFF;
.SET FOOTING OFF;
.SET HEADING OFF;
.SET INDICDATA OFF;
.SET SPACE 0;
.SET RECORDMODE OFF;
.SET RETLIMIT 2147483407,2048;
.SET ECHOREQ OFF;
.SET TITLEDASHES OFF;
Informaticauser@server1 DEV $ cat td2.txt
.logon dwsoat.dws.company.co.uk/Informaticauser,pasWtT04;
.SET WIDTH 65531;
.SET SEPARATOR '|' ALL;
.SET NULL AS "";
.SET FORMAT OFF;
.SET ECHOREQ OFF;
.SET FOOTING OFF;
.SET HEADING OFF;
.SET INDICDATA OFF;
.SET SPACE 0;
.SET RECORDMODE OFF;
.SET RETLIMIT 2147483407,2048;
.SET ECHOREQ OFF;
.SET TITLEDASHES OFF;
.EXPORT REPORT FILE=TS_72258_BASELDB.sample_alok1.txt
select * from TS_72258_BASELDB.sample_alok1;
Informaticauser@server1 DEV $ cat td.txt
.logon dwsoat.dws.company.co.uk/Informaticauser,pasWtT04;
.SET WIDTH 65531;
.SET SEPARATOR '|' ALL;
.SET NULL AS "";
.SET FORMAT OFF;
.SET ECHOREQ OFF;
.SET FOOTING OFF;
.SET HEADING OFF;
.SET INDICDATA OFF;
.SET SPACE 0;
.SET RECORDMODE OFF;
.SET RETLIMIT 2147483407,2048;
.SET ECHOREQ OFF;
.SET TITLEDASHES OFF;
.EXPORT REPORT FILE=TS_72258_BASELDB.sample_alok.txt
select * from TS_72258_BASELDB.sample_alok;
Informaticauser@server1 DEV $ cat Data_setup
#!/usr/bin/perl
print "** In case of any issues get in touch with Alok Nath (Alok.nath\@company.com)**\n\n";
print "Choose one of the below options:\n 1 for Teradata table unload\n 2 for db2 table unload \n 0 to exit\n Enter the option: ";
my $useroption = <STDIN>;
chomp $useroption;
 if ($useroption eq 0){
    exit 0;
    }
elsif($useroption ==1){
        print "Please enter the databaseame.tablename \n";
        my $filename = <STDIN>;
        print "$filename";
        chop($filename);
        $val1=".EXPORT REPORT FILE=$filename".".txt\n";
        $val2="select * from $filename\;\n";
        print $val1;
        print "$val2";
        my $path=`pwd`;
        chomp $path;
        open(WR1,">$path/CODE/Query_append.txt");
        print  WR1 ("$val1");
        print WR1  ("$val2");
        close WR1;
        $cmd0="cat $path/CODE/td_pre.txt $path/CODE/Query_append.txt> td.txt";
        system ("$cmd0");
        $cmd1="rm -rf $path/CODE/$filename.txt";
        $cmd2="bteq<td.txt";
        system ("$cmd1 && $cmd2");
        $cmd3="perl -i -pe 's/(?<=\|) +|^ +//g' $filename.txt";
        system ("$cmd3");
        `rm td.txt Query_append.txt`;
        print "Table has been sucessfully unloaded as $filename.txt\n";

    }
elsif($useroption ==2){
        print "Please enter the creator.tablename \n";
        my $NAME = <STDIN>;
        chomp $NAME;
        $cmd1=". /home/db2client/v97fp5/sqllib/db2profile";
        $cmd2="db2 connect to RDSA user userid1234567 using n###17";
        $dte="DATE";
        $cmd4="awk 'NR > 7 { print }' db2table_structure.txt |head -n -1 | cut -d ' ' -f5>db2_datatype.txt";
        $cmd5="awk 'NR > 7 { print }' db2table_structure.txt |head -n -1 | cut -c 37-67 |awk '{\$1=\$1}{ print }'>db2_column.txt";
        $cmd3="db2 \"describe output select * from $NAME\">db2table_structure.txt";
        system ("$cmd1 && $cmd2 && $cmd3" );
        system ("$cmd4");
        system ("$cmd5");
        $cmd6="rm -rf pre_query1_db2.txt";
        system ("$cmd6");
        open my $fh1, '<', 'db2_column.txt';
        open my $fh2, '<', 'db2_datatype.txt';
        while (!eof($fh1) && !eof($fh2)) {
        chomp(my $line1 = <$fh1>);
        chomp(my $line2 = <$fh2>);
        $line_data=$line2 eq 'DATE' ? "CHAR($line1".",EUR)"." " : "$line1"." ";
        open (WR2,">>pre_query1_db2.txt");
        print WR2 "$line_data\n";
        close WR2;
        }
        $cmd10="cat db2_column.txt| wc -l >count.txt";
        system ("$cmd10");
        $count=`cat count.txt`;
        print "$count\n";
        for (my $i=1; $i<=$count;) {
        $filePath1= "pre_query1_db2.txt";
        $lineWanted1 = $i;
        open my $fh4, '<', 'pre_query1_db2.txt';
        my $line1;
        while( <$fh4> ) {
                if( $. == $lineWanted1 ) {
                $line1 = $_;
                last;
          }
       }
        #print $line1;
        chomp ($line1);
        $cmd9="db2 \"Select TRIM(".$line1.") from $NAME\">unload1_temp$i.txt";
        system ("$cmd1 && $cmd2 && $cmd9 ");
        sleep 10;
        $cmd10="awk 'NR > 3 { print }' unload1_temp$i.txt | head -n -3 > unload_temp$i.txt";
        open (WR3,">>unload_filenames_sequence1.txt");
        print WR3 "unload_temp$i.txt\n";
        close WR3;
        #system ("$cmd10");
        $i++;
        }
        for (my $j=1; $j<=$count;) {
        $cmd10="awk 'NR > 3 { print }' unload1_temp$j.txt | head -n -3 > unload_temp$j.txt";
        system ("$cmd10");
        $j++;
        }
        $cmd12= "cat unload_filenames_sequence1.txt | xargs > unload_filenames_sequence.txt";
        system ("$cmd12");
        $file_paste = `cat unload_filenames_sequence.txt`;
        chomp $file_paste;

        $cmd11="paste $file_paste | sed 's/ //g' | sed 's/\\t/|/g' | sed 's/|-|/||/g' > final_$NAME.txt";
        system ("$cmd11");
        $cmd15="sed -i 's/|-/| /g' final_$NAME.txt";
        $cmd16="sed -i 's/-|/ |/g' final_$NAME.txt";
        system ("$cmd15");
        system ("$cmd16");
        $cmd13="rm -rf *unload*_temp*";
        system (" $cmd13 ");
        `rm pre_query1_db2.txt db2table_structure.txt db2_datatype.txt db2_column.txt count.txt`;
        $cmd14="rm -rf unload_filenames_sequence1.txt unload_filenames_sequence.txt";
        system ("$cmd14");

}
else {
     print "Incorrect option give correct option\n";
     my $useroption = <STDIN>;
     }




Informaticauser@server1 DEV $ cd BKP
Informaticauser@server1 DEV $ pwd
/home/Informaticauser/FRAMEWORK_PACK/CODE/BKP
Informaticauser@server1 DEV $ ll
total 28
-rwxrwxrwx 1 Informaticauser informatica  320 Oct  5 12:36 td_pre.txt
-rwxrwxrwx 1 Informaticauser informatica  419 Oct  5 12:36 td2.txt
-rwxrwxrwx 1 Informaticauser informatica  417 Oct  5 12:36 td.txt
-rwxrwxrwx 1 Informaticauser informatica   99 Oct  5 12:36 Query_append2.txt
-rwxrwxrwx 1 Informaticauser informatica   71 Oct  5 12:36 Query_append.txt
-rwxrwxrwx 1 Informaticauser informatica 4274 Oct  5 12:36 Data_setup
Informaticauser@server1 DEV $ cat td_pre.txt
.logon dwsoat.dws.company.co.uk/Teradataid,Teradatapassword;
.SET WIDTH 65531;
.SET SEPARATOR '|' ALL;
.SET NULL AS "";
.SET FORMAT OFF;
.SET ECHOREQ OFF;
.SET FOOTING OFF;
.SET HEADING OFF;
.SET INDICDATA OFF;
.SET SPACE 0;
.SET RECORDMODE OFF;
.SET RETLIMIT 2147483407,2048;
.SET ECHOREQ OFF;
.SET TITLEDASHES OFF;
Informaticauser@server1 DEV $ cat td2.txt
.logon dwsoat.dws.company.co.uk/Teradataid,Teradatapassword;
.SET WIDTH 65531;
.SET SEPARATOR '|' ALL;
.SET NULL AS "";
.SET FORMAT OFF;
.SET ECHOREQ OFF;
.SET FOOTING OFF;
.SET HEADING OFF;
.SET INDICDATA OFF;
.SET SPACE 0;
.SET RECORDMODE OFF;
.SET RETLIMIT 2147483407,2048;
.SET ECHOREQ OFF;
.SET TITLEDASHES OFF;
.EXPORT REPORT FILE=TS_72258_BASELDB.sample_alok1.txt
select * from TS_72258_BASELDB.sample_alok1;
Informaticauser@server1 DEV $ cat td.txt
.logon dwsoat.dws.company.co.uk/Teradataid,Teradatapassword;
.SET WIDTH 65531;
.SET SEPARATOR '|' ALL;
.SET NULL AS "";
.SET FORMAT OFF;
.SET ECHOREQ OFF;
.SET FOOTING OFF;
.SET HEADING OFF;
.SET INDICDATA OFF;
.SET SPACE 0;
.SET RECORDMODE OFF;
.SET RETLIMIT 2147483407,2048;
.SET ECHOREQ OFF;
.SET TITLEDASHES OFF;
.EXPORT REPORT FILE=TS_72258_BASELDB.sample_alok.txt
select * from TS_72258_BASELDB.sample_alok;
Informaticauser@server1 DEV $ cat Query_append2.txt
.EXPORT REPORT FILE=TS_72258_BASELDB.sample_alok1.txt
select * from TS_72258_BASELDB.sample_alok1;
Informaticauser@server1 DEV $ cat Query_append.txt
.EXPORT REPORT FILE=DLY0APD.TVFPKAG.txt
select * from DLY0APD.TVFPKAG;
Informaticauser@server1 DEV $ cat Data_setup
#!/usr/bin/perl
print "** In case of any issues get in touch with Alok Nath (Alok.nath\@company.com)**\n\n";
print "Choose one of the below options:\n 1 for Teradata table unload\n 2 for db2 table unload \n 0 to exit\n Enter the option: ";
my $useroption = <STDIN>;
chomp $useroption;
 if ($useroption eq 0){
    exit 0;
    }
elsif($useroption ==1){
        print "Please enter the databaseame.tablename \n";
        my $filename = <STDIN>;
        print "$filename";
        chop($filename);
        $val1=".EXPORT REPORT FILE=$filename".".txt\n";
        $val2="select * from $filename\;\n";
        print $val1;
        print "$val2";
        my $path=`pwd`;
        chomp $path;
        open(WR1,">$path/CODE/Query_append.txt");
        print  WR1 ("$val1");
        print WR1  ("$val2");
        close WR1;
        $cmd0="cat $path/CODE/td_pre.txt $path/CODE/Query_append.txt> td.txt";
        system ("$cmd0");
        $cmd1="rm -rf $path/CODE/$filename.txt";
        $cmd2="bteq<td.txt";
        system ("$cmd1 && $cmd2");
        $cmd3="perl -i -pe 's/(?<=\|) +|^ +//g' $filename.txt";
        system ("$cmd3");
        `rm td.txt Query_append.txt`;
        print "Table has been sucessfully unloaded as $filename.txt\n";

    }
elsif($useroption ==2){
        print "Please enter the creator.tablename \n";
        my $NAME = <STDIN>;
        chomp $NAME;
        $cmd1=". /home/db2client/v97fp5/sqllib/db2profile";
        $cmd2="db2 connect to RDSA user mainframeid using mainframepassword";
        $dte="DATE";
        $cmd4="awk 'NR > 7 { print }' db2table_structure.txt |head -n -1 | cut -d ' ' -f5>db2_datatype.txt";
        $cmd5="awk 'NR > 7 { print }' db2table_structure.txt |head -n -1 | cut -c 37-67 |awk '{\$1=\$1}{ print }'>db2_column.txt";
        $cmd3="db2 \"describe output select * from $NAME\">db2table_structure.txt";
        system ("$cmd1 && $cmd2 && $cmd3" );
        system ("$cmd4");
        system ("$cmd5");
        $cmd6="rm -rf pre_query1_db2.txt";
        system ("$cmd6");
        open my $fh1, '<', 'db2_column.txt';
        open my $fh2, '<', 'db2_datatype.txt';
        while (!eof($fh1) && !eof($fh2)) {
        chomp(my $line1 = <$fh1>);
        chomp(my $line2 = <$fh2>);
        $line_data=$line2 eq 'DATE' ? "CHAR($line1".",EUR)"." " : "$line1"." ";
        open (WR2,">>pre_query1_db2.txt");
        print WR2 "$line_data\n";
        close WR2;
        }
        $cmd10="cat db2_column.txt| wc -l >count.txt";
        system ("$cmd10");
        $count=`cat count.txt`;
        print "$count\n";
        for (my $i=1; $i<=$count;) {
        $filePath1= "pre_query1_db2.txt";
        $lineWanted1 = $i;
        open my $fh4, '<', 'pre_query1_db2.txt';
        my $line1;
        while( <$fh4> ) {
                if( $. == $lineWanted1 ) {
                $line1 = $_;
                last;
          }
       }
        #print $line1;
        chomp ($line1);
        $cmd9="db2 \"Select TRIM(".$line1.") from $NAME\">unload1_temp$i.txt";
        system ("$cmd1 && $cmd2 && $cmd9 ");
        sleep 10;
        $cmd10="awk 'NR > 3 { print }' unload1_temp$i.txt | head -n -3 > unload_temp$i.txt";
        open (WR3,">>unload_filenames_sequence1.txt");
        print WR3 "unload_temp$i.txt\n";
        close WR3;
        #system ("$cmd10");
        $i++;
        }
        for (my $j=1; $j<=$count;) {
        $cmd10="awk 'NR > 3 { print }' unload1_temp$j.txt | head -n -3 > unload_temp$j.txt";
        system ("$cmd10");
        $j++;
        }
        $cmd12= "cat unload_filenames_sequence1.txt | xargs > unload_filenames_sequence.txt";
        system ("$cmd12");
        $file_paste = `cat unload_filenames_sequence.txt`;
        chomp $file_paste;

        $cmd11="paste $file_paste | sed 's/ //g' | sed 's/\\t/|/g' | sed 's/|-|/||/g' > final_$NAME.txt";
        system ("$cmd11");
        $cmd15="sed -i 's/|-/| /g' final_$NAME.txt";
        $cmd16="sed -i 's/-|/ |/g' final_$NAME.txt";
        system ("$cmd15");
        system ("$cmd16");
        $cmd13="rm -rf *unload*_temp*";
        system (" $cmd13 ");
        `rm pre_query1_db2.txt db2table_structure.txt db2_datatype.txt db2_column.txt count.txt`;
        $cmd14="rm -rf unload_filenames_sequence1.txt unload_filenames_sequence.txt";
        system ("$cmd14");

}
else {
     print "Incorrect option give correct option\n";
     my $useroption = <STDIN>;
     }




Informaticauser@server1 DEV $

============================================================================================================================
Informaticauser@server1 DEV $ cd BKP
Informaticauser@server1 DEV $ pwd
/home/Informaticauser/FRAMEWORK_PACK/BKP
Informaticauser@server1 DEV $ ll
total 128
-rwxrwxrwx 1 Informaticauser informatica  754 Oct  5 10:02 xargvinput.pl
-rwxrwxrwx 1 Informaticauser informatica  418 Oct  5 10:02 txttocsv.py
-rwxrwxrwx 1 Informaticauser informatica 1152 Oct  5 10:02 template_color_coding_excel.py
-rwxrwxrwx 1 Informaticauser informatica  397 Oct  5 10:02 template_bteq_help_fields.txt
-rwxrwxrwx 1 Informaticauser informatica   24 Oct  5 10:02 run_trigger.sh
-rwxrwxrwx 1 Informaticauser informatica  691 Oct  5 10:02 process_final_upload.pl
-rwxrwxrwx 1 Informaticauser informatica   82 Oct  5 10:02 pre_upload_template2.txt
-rwxrwxrwx 1 Informaticauser informatica  128 Oct  5 10:02 pre_upload_template1.txt
-rwxrwxrwx 1 Informaticauser informatica  513 Oct  5 10:02 oracleconnect.py
-rwxrwxrwx 1 Informaticauser informatica  450 Oct  5 10:02 delinsert.pl
-rwxrwxrwx 1 Informaticauser informatica  483 Oct  5 10:02 csvtoxls.py
-rwxrwxrwx 1 Informaticauser informatica  361 Oct  5 10:02 csvtotxt.py
-rwxrwxrwx 1 Informaticauser informatica  306 Oct  5 10:02 countcolumn.py
-rwxrwxrwx 1 Informaticauser informatica  672 Oct  5 10:02 compare
-rwxrwxrwx 1 Informaticauser informatica  124 Oct  5 10:02 colorcoding.sh
-rwxrwxrwx 1 Informaticauser informatica 1134 Oct  5 10:02 color_coding_excel.py
-rwxrwxrwx 1 Informaticauser informatica 1464 Oct  5 10:02 bteq_upload.wrapper.txt
-rwxrwxrwx 1 Informaticauser informatica  397 Oct  5 10:02 bteq_help_fields.txt
-rwxrwxrwx 1 Informaticauser informatica  323 Oct  5 10:02 bteq_field_position.wrapper.txt
-rwxrwxrwx 1 Informaticauser informatica  115 Oct  5 10:04 oraclerun.sh
-rwxrwxrwx 1 Informaticauser informatica  110 Oct  5 10:05 csvtoxls.sh
-rwxrwxrwx 1 Informaticauser informatica  113 Oct  5 10:05 count.sh
-rwxrwxrwx 1 Informaticauser informatica  391 Oct  5 12:25 bteq_perl_trigger.pl
-rwxrwxrwx 1 Informaticauser informatica  513 Oct  5 13:38 oracleconnect1.py
-rwxrwxrwx 1 Informaticauser informatica  115 Oct  5 13:38 oraclerun1.sh
-rwxrwxrwx 1 Informaticauser informatica 1919 Oct  6 10:45 menu.sh
-rwxrwxrwx 1 Informaticauser informatica  643 Oct  6 11:17 cleanup.sh
-rwxrwxrwx 1 Informaticauser informatica  988 Oct  6 11:45 HDR_TLRADD.sh
-rwxrwxrwx 1 Informaticauser informatica 4860 Oct  6 12:16 trigger
-rwxrwxrwx 1 Informaticauser informatica 7217 Oct  6 13:48 id_password_setup.pl
Informaticauser@server1 DEV $ cat xargvinput.pl
#!/usr/bin/perl
use strict;
use warnings;
print "Enter the filename you want to drop column from, ensure have a backup of old file\n";
my $filename = <STDIN>;
chomp $filename;
print "Enter teh number of columns you want to replace\n";
my $limit = <STDIN>;
chomp $limit;
my @arr1;
while (my @arr < $limit) {
   print "Enter the position number\n";
    my $num = <STDIN>;
    chomp $num;
    push @arr, $num;
push(@arr1, @arr);
$limit--;
}
open(WR,"> drop_columns.txt");
print WR join ",", @arr1;
close WR;
print join ",", @arr1;
print "\n";
my $del_col=`cat drop_columns.txt`;
chomp $del_col;
my $cmd1 ="cut --complement -f $del_col -d '|' $filename > $filename.chopped_file.txt";
system(" $cmd1 ");
print " result fiel is $filename.chopped_file.txt\n";

Informaticauser@server1 DEV $ cat txttocsv.py
#!/usr/bin/tpython

import os
import sys
import csv

sourcefile = sys.argv[1]
targetfile = sys.argv[2]
ofile = open(targetfile, "ab")
writer = csv.writer(ofile, dialect='excel', delimiter=',')
list=[]
file_reader = open(sourcefile, "r")
for row in file_reader:
        row=row.replace("|",",")
        row=row.strip("\n")
        list.append(row)
        writer.writerows([x.split(',') for x in list])
        list=[]
file_reader.close()
ofile.close()
Informaticauser@server1 DEV $ cat template_color_coding_excel.py
import csv
import sys
from xlwt import Workbook, easyxf
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
cell = easyxf('pattern: pattern solid, fore_colour red')
for row_number in range(0,rowrange,2):
    for column_number in range(columnrange):
        with open('Difference_csv.csv', 'rb') as f:
         rows = list(csv.reader(f))
         if rows[row_number][column_number] == rows[row_number+1][column_number]:
             text1 = rows[row_number][column_number]
             text2 = rows[row_number+1][column_number]
             print rows[row_number][column_number] + " matched " + rows[row_number+1][column_number]
             sheet1.write(row_number,column_number,text1)
             sheet1.write(row_number+1,column_number,text2)
         else:
            print rows[row_number][column_number] + " notmatched " + rows[row_number+1][column_number]
            text3 = rows[row_number][column_number]
            text4 = rows[row_number+1][column_number]
            sheet1.write(row_number,column_number,text3,cell)
            sheet1.write(row_number+1,column_number,text4,cell)
book.save('Compare_result.xls')
Informaticauser@server1 DEV $ cat template_bteq_help_fields.txt
.logon dwsoat.dws.company.co.uk/Teradataid,Teradatapassword;
.SET WIDTH 65531;
.SET SEPARATOR '|' ALL;
.SET NULL AS "";
.SET FORMAT OFF;
.SET ECHOREQ OFF;
.SET FOOTING OFF;
.SET HEADING OFF;
.SET INDICDATA OFF;
.SET SPACE 0;
.SET RECORDMODE OFF;
.SET RETLIMIT 2147483407,2048;
.SET ECHOREQ OFF;
.SET TITLEDASHES OFF;
.EXPORT REPORT FILE=Table_details.txt
help table TS_72258_BASELDB.rupesh_cdc2;
Informaticauser@server1 DEV $ cat run_trigger.sh
sh menu.sh
perl trigger
Informaticauser@server1 DEV $ cat process_final_upload.pl
print "Please enter the fullpath of file eg /home/id/abc.csv \n";
my $filename_path = <STDIN>;
chop($filename_path);
$cmd2 ="sed -i 's#csvfilewithfullpath#$filename_path#g' final_upload.txt";
$fieldwithcomma =`cat pre_teradata_upld2.txt`;
$fieldwithcolon =`cat pre_teradata_upld1.txt`;
$databasetable =`cat database_table.txt`;
chomp ($fieldwithcomma);
chomp ($fieldwithcolon);
chomp ($databasetable);
$cmd3 ="sed -i 's#fieldwithcomma#$fieldwithcomma#g' final_upload.txt";
$cmd4 ="sed -i 's#fieldwithcolon#$fieldwithcolon#g' final_upload.txt";
$cmd5 ="sed -i 's#database.tablename#$databasetable#g' final_upload.txt";
system ("$cmd2");
system ("$cmd3");
system ("$cmd4");
system ("$cmd5");

Informaticauser@server1 DEV $ cat pre_upload_template2.txt

insert into database.tablename (fieldwithcomma)
values(fieldwithcolon);
.logoff;
Informaticauser@server1 DEV $ cat pre_upload_template1.txt
.logon dwsoat.dws.company.co.uk/Teradataid,Teradatapassword;
.import vartext ',' file ='csvfilewithfullpath'
.repeat *;
using
Informaticauser@server1 DEV $ cat oracleconnect.py
import cx_Oracle
import csv
import sys
table = raw_input('Enter tablename: ')
host = 'hostname'
port = portnumber
SERVICE_NAME = 'servicename'
login = 'Oracle_id'
passwrd = 'oracle_password'
SID = 'oracle_sid'
dsn = cx_Oracle.makedsn(host, port, SID).replace('SID','SERVICE_NAME')
con = cx_Oracle.connect(login, passwrd, dsn)

cur = con.cursor()

cur.execute('select * from %s' %(table))
row = cur.fetchall()
#print row
c = csv.writer(open("oracle_output.csv","wb"), delimiter="|")
c.writerows(row)
con.close()
Informaticauser@server1 DEV $ cat delinsert.pl
#!/usr/bin/perl
use strict;
use warnings;
print "Enter teh field position filename\n";
my $filename = <STDIN>;
print "Enter the unzip .gz text file to insert delimiter in \n";
my $convertfile = <STDIN>;
chomp $filename;
chomp $convertfile;
open(my $fh, '<:encoding(UTF-8)', $filename)
or die "Could not open file '$filename' $!";

while (my $row = <$fh>) {
chomp $row;
`sed -i 's/./&|/$row' $convertfile`;
print "$row\n";
}
#`cat datatest.txt`;
Informaticauser@server1 DEV $ cat csvtoxls.py
import csv, xlwt
import os
import sys
sourcefile = raw_input('Enter a sourcefile name:\n ')
targetfile = raw_input('Enter a targetfile name:\n ')

f = open("%s" % sourcefile, 'rb')
reader = csv.reader(f)

workbook = xlwt.Workbook()
sheet = workbook.add_sheet("Sheet 1")

#mystyle = xlwt.easyxf('pattern: pattern solid, fore_colour red')
for rowi, row in enumerate(reader):
    for coli, value in enumerate(row):
        sheet.write(rowi,coli,value)

workbook.save("%s" % targetfile)
Informaticauser@server1 DEV $ cat csvtotxt.py
#!/usr/bin/tpython

import os
import sys
import csv
sourcefile = sys.argv[1]
targetfile = sys.argv[2]
ofile = open(sourcefile, "rb")
for row in ofile:
        row = row.replace("\t", "|")
        row = row.strip()
        list.append(row)
ofile.close()
#print list
file_write= open(targetfile,"a")
for row in list:
        file_write.write(row)
        file_write.write("\n")
file_write.close()
Informaticauser@server1 DEV $ cat countcolumn.py
import sys
import os
import csv
with open('Difference_csv.csv', 'rb') as f:
    reader = csv.reader(f, delimiter=',', skipinitialspace=True)
    first_row = next(reader)
    num_cols = len(first_row)
    f1 = open( 'color_columns.txt', 'w' )
    f1.write("%d" % num_cols)
    f1.close()
    print num_cols
Informaticauser@server1 DEV $ cat compare
#!/usr/bin/perl -W
use strict;
use warnings;
my $f1 = $ARGV[0];
my $f2 = $ARGV[1];
my $outfile = "Difference_$ARGV[0].$ARGV[1]";
my %results = ();
open FILE1, "$f1" or die "Could not open file: $! \n";
while(my $line = <FILE1>){   $results{$line}=1;
}
close(FILE1);
open FILE2, "$f2" or die "Could not open file: $! \n";
while(my $line =<FILE2>) {
 $results{$line}++;
}
close(FILE2);
open (OUTFILE, ">$outfile") or die "Cannot open $outfile for writing \n";
foreach my $line (keys %results) { print OUTFILE $line if $results{$line} == 1;
}
close OUTFILE;
my $cmd6="cat $outfile | sort >$outfile.final.txt";
my $cmd7="rm -rf $outfile";
system (" $cmd6 && $cmd7 ");
Informaticauser@server1 DEV $ cat colorcoding.sh
alias python='/home/userid1234567/python/bin/python2.7'
export PATH=/home/userid1234567/python/bin:$PATH
python color_coding_excel.py
Informaticauser@server1 DEV $ cat color_coding_excel.py
import csv
import sys
from xlwt import Workbook, easyxf
book = Workbook()
sheet1 = book.add_sheet('Sheet 1')
cell = easyxf('pattern: pattern solid, fore_colour red')
for row_number in range(0,0,2):
    for column_number in range():
        with open('Difference_csv.csv', 'rb') as f:
         rows = list(csv.reader(f))
         if rows[row_number][column_number] == rows[row_number+1][column_number]:
             text1 = rows[row_number][column_number]
             text2 = rows[row_number+1][column_number]
             print rows[row_number][column_number] + " matched " + rows[row_number+1][column_number]
             sheet1.write(row_number,column_number,text1)
             sheet1.write(row_number+1,column_number,text2)
         else:
            print rows[row_number][column_number] + " notmatched " + rows[row_number+1][column_number]
            text3 = rows[row_number][column_number]
            text4 = rows[row_number+1][column_number]
            sheet1.write(row_number,column_number,text3,cell)
            sheet1.write(row_number+1,column_number,text4,cell)
book.save('Compare_result.xls')
Informaticauser@server1 DEV $ cat bteq_upload.wrapper.txt
rm -rf Table_details.txt
cp template_bteq_help_fields.txt bteq_help_fields.txt
perl bteq_perl_trigger.pl
bteq < bteq_help_fields.txt
cat Table_details.txt | cut -d '|' -f1 > teradata_fields.txt
cat Table_details.txt | cut -d '|' -f7 > teradata_field_length.txt
sed -i '1d' teradata_fields.txt teradata_field_length.txt
sed -i 's/4/40/g' teradata_field_length.txt
sed -i 's/ //g' teradata_field_length.txt teradata_fields.txt
cp teradata_fields.txt pre_teradata_fields.txt
cp teradata_fields.txt pre_teradata_fields1.txt
sed -i 's/$/ (varchar(/' teradata_fields.txt
paste teradata_fields.txt teradata_field_length.txt | sed 's/\t//g' >pre_query.txt
sed -i 's/$/)),/' pre_query.txt
sed -i '$s/,$//' pre_query.txt
sed -i 's/^/:/' pre_teradata_fields.txt
sed -i 's/$/,/' pre_teradata_fields.txt
sed -i '$s/,$//' pre_teradata_fields.txt
sed -i 's/$/,/' pre_teradata_fields1.txt
sed -i '$s/,$//' pre_teradata_fields1.txt
cat pre_teradata_fields.txt | xargs | sed 's/ //g' > pre_teradata_upld1.txt
cat pre_teradata_fields1.txt | xargs | sed 's/ //g' > pre_teradata_upld2.txt
rm -rf pre_teradata_fields.txt pre_teradata_fields1.txt teradata_fields.txt teradata_field_length.txt
cat pre_upload_template1.txt > final_upload.txt
cat pre_query.txt >> final_upload.txt
cat pre_upload_template2.txt >> final_upload.txt
perl process_final_upload.pl
rm -rf pre_teradata_upld1.txt pre_teradata_upld2.txt pre_query.txt database_table.txt Table_details.txt
#bteq < final_upload.txt
Informaticauser@server1 DEV $ cat bteq_help_fields.txt
.logon dwsoat.dws.company.co.uk/Teradataid,Teradatapassword;
.SET WIDTH 65531;
.SET SEPARATOR '|' ALL;
.SET NULL AS "";
.SET FORMAT OFF;
.SET ECHOREQ OFF;
.SET FOOTING OFF;
.SET HEADING OFF;
.SET INDICDATA OFF;
.SET SPACE 0;
.SET RECORDMODE OFF;
.SET RETLIMIT 2147483407,2048;
.SET ECHOREQ OFF;
.SET TITLEDASHES OFF;
.EXPORT REPORT FILE=Table_details.txt
help table TS_72258_BASELDB.rupesh_cdc2;
Informaticauser@server1 DEV $ cat bteq_field_position.wrapper.txt
rm -rf Table_details.txt
cp template_bteq_help_fields.txt bteq_help_fields.txt
perl bteq_perl_trigger.pl
bteq < bteq_help_fields.txt
cat Table_details.txt | cut -d '|' -f1 > teradata_fields.txt
sed -i '1d' teradata_fields.txt
sed -i 's/ //g' teradata_fields.txt
cat -n teradata_fields.txt > teradata_fields_positions.txt

Informaticauser@server1 DEV $ cat oraclerun.sh
alias python='/home/userid/python/bin/python2.7'
export PATH=/home/userid/python/bin:$PATH
python oracleconnect.py
Informaticauser@server1 DEV $ cat csvtoxls.sh
alias python='/home/userid/python/bin/python2.7'
export PATH=/home/userid/python/bin:$PATH
python csvtoxls.py
Informaticauser@server1 DEV $ cat count.sh
alias python='/home/userid/python/bin/python2.7'
export PATH=/home/userid/python/bin:$PATH
python countcolumn.py
Informaticauser@server1 DEV $ cat bteq_perl_trigger.pl
#!/usr/bin/perl
print "Please enter the database.tablename which you want to upload csv/file \n";
my $database_tablename = <STDIN>;
chop($database_tablename);
my $path=`pwd`;
chomp $path;
$cmd1 ="sed -i 's#TS_72258_BASELDB.rupesh_cdc2#$database_tablename#g' $path/bteq_help_fields.txt";
system ("$cmd1");
open(WR1,">$path/database_table.txt");
print  WR1 ("$database_tablename");
close WR1;
Informaticauser@server1 DEV $ cat oracleconnect1.py
import cx_Oracle
import csv
import sys
table = raw_input('Enter tablename: ')
host = 'hostname'
port = portnumber
SERVICE_NAME = 'servicename'
login = 'Oracle_id'
passwrd = 'oracle_password'
SID = 'oracle_sid'
dsn = cx_Oracle.makedsn(host, port, SID).replace('SID','SERVICE_NAME')
con = cx_Oracle.connect(login, passwrd, dsn)

cur = con.cursor()

cur.execute('select * from %s' %(table))
row = cur.fetchall()
#print row
c = csv.writer(open("oracle_output.csv","wb"), delimiter="|")
c.writerows(row)
con.close()
Informaticauser@server1 DEV $ cat oraclerun1.sh
alias python='/home/userid/python/bin/python2.7'
export PATH=/home/userid/python/bin:$PATH
python oracleconnect.py
Informaticauser@server1 DEV $ cat menu.sh
reset
echo -e "\e[1;32;40m\e[0m"
echo -e "\e[1;32;5m                         ==== DTAS (Datawarehouse Test Automation Suite) ====                          \e[0m"
echo -e "\e[1;32;40m\e[0m"
echo -e "\e[1;32;5m  File Comparision: \e[0m"
echo -e "\e[1;32;40m    For File comparison enter 1 \e[0m"
echo -e "\e[1;32;5m  File Transformations: \e[0m"
echo -e "\e[1;32;40m    For converting pipe delimited to CSV file enter 2 \e[0m"
echo -e "\e[1;32;40m    For converting pipe delimited to excel enter 3 \e[0m"
echo -e "\e[1;32;40m    For converting convert fixed width to pipe delimited enter 4 \e[0m"
echo -e "\e[1;32;5m  Data Unload/Upload utilities: \e[0m"
echo -e "\e[1;32;40m    For table (TERADATA/DB2) unload (DB2 unload tested on informatica 76/84 server only) enter 5 \e[0m"
echo -e "\e[1;32;40m    For load file in teradata using BTEQ enter 6 \e[0m"
echo -e "\e[1;32;40m    For Unloading data from Hive to pipe dilimited file (support JSON/PARQUET/AVRO/CSV formats) enter 7 \e[0m"
echo -e "\e[1;32;40m    For Oracle unload (tested on informatica 76/84 server only) enter 8 \e[0m"
echo -e "\e[1;32;40m    For Oracle unload from two databases enter 9 \e[0m"
echo -e "\e[1;32;40m    Load file in DB2/Oracle - future development ( In progress ) \e[0m"
echo -e "\e[1;32;5m  File Processing utilities: \e[0m"
echo -e "\e[1;32;40m    For getting linenumber in file  enter 10 \e[0m"
echo -e "\e[1;32;40m    For  dropping colums from file enter 11 \e[0m"
echo -e "\e[1;32;5m  Database Utilities: \e[0m"
echo -e "\e[1;32;40m    For Getting  position number and field details teradata enter 12 \e[0m"
echo -e "\e[1;32;40m    For Getting position number and field details hive enter 13 \e[0m"
echo -e "\e[1;32;40m    For Housekeeping enter 13 \e[0m"
echo -e "\e[1;32;40m    For exit enter 0 \e[0m"
echo -e "\e[1;32;5m                                                                                                       \e[0m"
Informaticauser@server1 DEV $ cat cleanup.sh
chmod 777 *
shopt -s extglob
rm  !(xargvinput.pl|BKP|CODE|txttocsv.py|trailer.sh|header.sh|complete_file.sh|Hed_Trl.sh|template_color_coding_excel.py|run_trigger.sh|process_final_upload.pl|pre_upload_template2.txt|delinsert.pl|csvtoxls.py|csvtotxt.py|countcolumn.py|compare|colorcoding.sh|color_coding_excel.py|cleanup.sh|bteq_upload.wrapper.txt|bteq_field_position.wrapper.txt|oraclerun.sh|csvtoxls.sh|count.sh|.profile|bteq_perl_trigger.pl|template_bteq_help_fields.txt|pre_upload_template1.txt|bteq_help_fields.txt|oracleconnect.py_bkp|oracleconnect.py|oraclerun1.sh|id_password_setup.pl|menu.sh|trigger|oracleconnect1.py)
shopt -u extglob
Informaticauser@server1 DEV $ cat HDR_TLRADD.sh
echo "3 parameter required for this script"
echo "1st Parameter Business date DDMMYYYY: $1"
echo "2nd Parameter Chopped File name : $2"
echo "Customer File Name: $3"
#. ./header.sh $1
Intial=00FDLH0100
#Business_Date=$1
End=01
echo "$Intial $1$End" >header_$1.dat

#. ./trailer.sh $1 $2
IntTr=99FDLH010
#BUS_DATE=$1
TEMP_NUM=2
REC_COUNT_1=`cat ./$2| wc -l`
REC_COUNT_2=2
REC_COUNT_3=`expr $REC_COUNT_1 + $REC_COUNT_2`
#echo $REC_COUNT_1
#echo $REC_COUNT_3
REC_COUNT_4=00000000
#REC_COUNT=`expr $REC_COUNT_3 + $REC_COUNT_4`
REC_COUNT=`printf "%010d" $REC_COUNT_3`
#echo $REC_COUNT
#echo "$REC_COUNT$REC_COUNT_4"
echo "99FDLH010$1$REC_COUNT$REC_COUNT_4" >trailer_$1.dat

#. ./complete_file

Gribouillis 1,391 Programming Explorer Team Colleague · Answer 2 · 2015-10-27T09:27:33+00:00

That's a lot of code in many languages. A good think to do is to upload it to github or some other forge. Don't forget to write the documentation !

Alok_3 0 Newbie Poster · Answer 3 · 2015-10-27T13:46:00+00:00

Created code in perl below:

#!/usr/bin/perl
    print "\n:";
    print "Enter the first filename to compare \n";
    my $filename = <STDIN>;
    chomp $filename;
    print "Enter the second filename to compare \n";
    my $filename1 = <STDIN>;
    chomp $filename1;
    $cmd_sort1 ="sort <$filename >a.$filename";
    $cmd_sort2 ="sort <$filename1 >b.$filename1";
    system (" $cmd_sort1 & $cmd_sort2 ");
    $count_file1 = `cat $filename |wc -l`;
    $count_file2 = `cat $filename1 | wc -l`;
    if ($count_file1 eq $count_file2)
    {
    $cmd4="perl compare a.$filename b.$filename1";
    system ("$cmd4");
    `rm a.$filename b.$filename1`;
    print "Difference will be in file Difference_a.$filename.b.$filename1.final.txt\n\n";


Create below file as well in same folder


compare
        #!/usr/bin/perl -W
    use strict;
    use warnings;
    my $f1 = $ARGV[0];
    my $f2 = $ARGV[1];
    my $outfile = "Difference_$ARGV[0].$ARGV[1]";
    my %results = ();
    open FILE1, "$f1" or die "Could not open file: $! \n";
    while(my $line = <FILE1>){   $results{$line}=1;
    }
    close(FILE1);
    open FILE2, "$f2" or die "Could not open file: $! \n";
    while(my $line =<FILE2>) {
     $results{$line}++;
    }
    close(FILE2);
    open (OUTFILE, ">$outfile") or die "Cannot open $outfile for writing \n";
    foreach my $line (keys %results) { print OUTFILE $line if $results{$line} == 1;
    }
    close OUTFILE;
    my $cmd6="cat $outfile | sort >$outfile.final.txt";
    my $cmd7="rm -rf $outfile";
    system (" $cmd6 && $cmd7 ");

Get difference of two text files in excel and highlighting the difference

Recommended Answers Collapse Answers

All 5 Replies

Recommended Answers