Make INSERT statement syntax from a csv file using perl

I need to read a csv file and insert the data from csv to oracle. I created below perl script which will read the csv file line by line and will create INSERT statement accordingly.

#!/usr/bin/perl

$filename = "test.csv";
open(INFILE, "<", $filename)
or die "Can't open $filename for reading: $!\n";
my(@lines) = <INFILE>;
my($line1) = $lines[0];
my($line2) = $lines[1];

print "$line1 \n";
print "$line2 \n";

close(INFILE);

my($tablename) = "mytable";
my($createquery) = "Create table " . $tablename . "(";

my(@var1) = split("\t", $line1);
my(@var2) = split("\t", $line2);
my($i) = 0;
for $item1 (@var1){
chomp($item1);
$createquery = $createquery . $item1 . " ";
$data = $var2[$i++];
print "$item1\t : $data\n";
if(checkData($data) eq "int"){
$createquery = $createquery . "number(10,0), ";
}
elsif(checkData($data) eq "float"){
$createquery = $createquery . "number(10,2), ";
}
else{
$createquery = $createquery . "varchar2(50), ";
}
}
$createquery = substr($createquery,0, -2);
$createquery = $createquery . ");";
print "$createquery\n";

$i = 0;
for $dataline (@lines){
if ( $i == 0){
$i = 1;
next;
}
chomp($dataline);
my(@dataitem) = split("\t", $dataline);
my($insertquery) = "INSERT INTO " . $tablename . " VALUES (";
print "$dataitem\n";
for $items (@dataitem){
if( (checkData($items) eq "int") || (checkData($items) eq "float") ){
$insertquery = $insertquery . $items .",";
}
else{
$insertquery = $insertquery . "\"" . $items . "\"" .",";
}
}
$insertquery = substr($insertquery, 0, -1);
$insertquery = $insertquery . ")";
print "$insertquery\n";
}

sub checkData(){
if(@_[0] =~ /^-?\d+$/){
return "int";
}
elsif(@_[0] =~ /^-?(?:\d+(?:\.\d*)?|\.\d+)$/){
return "float";
}
else{
return "string";
}
}

Make CREATE TABLE syntax from a csv file using perl

I have a csv file and i have to make the CREATE TABLE statement (which later will be used to create an Oracle table) by reading first and second line of the csv file. First line in the csv file is column headers and the second line is actual data. By reading the first line, i will create column name and by reading the second line, i'll decide the data type for the column.
Below is the perl script for the same: (i am assuming that data in the csv file is tab seperated)

#!/usr/bin/perl

$filename = "test.csv";
open(INFILE, "<", $filename) or die "Can't open $filename for reading: $!\n";
@lines = <INFILE>;
$line1 = $lines[0];
$line2 = $lines[1];

print "$line1 \n";
print "$line2 \n";

close(INFILE);

$tablename = "mytable";
$query = "Create table " . $tablename . "(";

@var1 = split("\t", $line1);
@var2 = split("\t", $line2);
$i = 0;
for $item1 (@var1){
chomp($item1);
$query = $query . $item1 . " ";
$data = $var2[$i++];
print "$item1\t : $data\n";
if($data =~ /^-?\d+$/){
$query = $query . "number(10,0), ";
}
elsif ($data =~ /^-?(?:\d+(?:\.\d*)?|\.\d+)$/){
$query = $query . "number(10,2), ";
}
else{
$query = $query . "varchar2(50), ";
}
}
$query = substr($query,0, -2);
$query = $query . ");";
print "$query\n";

Read data from a csv file using perl (when the column count is not known)

I need to read a csv file, and process each and every column data (i do not know the column count before processing the file)

#!/usr/local/bin/perl

if (scalar(@ARGV) < 1)
{
print "Please Specify the file name to be read \n";
exit
}

$file = $ARGV[0];

open FILE, "< $file" or die "Can't open test.csv : $!";
while(<FILE>){
chop;
my(@columns)= split("\t");
my($colnum)=$#columns;
# print "Number of Column in this row is : $colnum\n";
foreach $col(@columns){
print "$col \t";
}
print "\n";
}
close FILE;

Read data from a csv file using perl (when the column count is known)

I need to read a csv file, and process each and every column data (i know the column count before processing the file)

#!/usr/local/bin/perl

open FILE, "< test.csv" or die "Can't open test.csv : $!";
while(){
chomp;
($col1,$col2,$col3,$col4) = split ("\t");
print "$col1 \t";
print "$col2 \t";
print "$col3 \t";
print "$col4 \t";
print "\n";
}
close FILE;

Check datatype of a argument in perl

I need to pick few lines from a particular file and check the data type of values of a particular column. Following is the perl script for the same:

#!/usr/bin/perl

$filename = "test.csv";
open(INFILE, "<", $filename) or die "Can't open $filename for reading: $!\n";
@lines = <INFILE>;
$line1 = $lines[0];
$line2 = $lines[1];

print "$line1 \n";
print "$line2 \n";

close(INFILE);

@var1 = split("\t", $line2);
for $item (@var1){
if($item =~ /\D/){
print "$item\t : Has Non Digits\n";
}
elsif ($item =~ /^\d+$/){
print "$item\t : Whole Number\n";
}
elsif ($item =~ /^-?\d+$/){
print "$item\t : Integer\n";
}
elsif ($item =~ /^[+-]?\d+$/){
print "$item\t : +/- Integer\n";
}
elsif ($item =~ /^-?\d+\.?\d*$/){
print "$item\t : Real Number\n";
}
elsif ($item =~ /^-?(?:\d+(?:\.\d*)?|\.\d+)$/){
print "$item\t : Float Number\n";
}
else{
print "$item\t : NAN\n";
}
}

Print particular line/s from a file using perl

Method 1:

#!/usr/bin/perl

$filename = "test.csv";
open(INFILE, "<", $filename)
or die "Can't open $filename for reading: $!\n";

$line_number = 1;
while () {
$line = $_;
last if $. == $line_number;
}
if ($. != $line_number) {
die "Didn't find line $line_number in $filename\n";
}
print;

close(INFILE);



Method 2:

#!/usr/bin/perl

$filename = "test.csv";
open(INFILE, "<", $filename)
or die "Can't open $filename for reading: $!\n";
@lines = ;
$line1 = $lines[0];
$line2 = $lines[1];

print "$line1 \n";
print "$line2 \n";

close(INFILE);

Set Terminal Title

If you want the Terminal window title to be set automatically to the remote zone you have logged into, add this to your .bashrc file on the remote machine.


# Terminal settings
PS1="[\u@\h \W]# "

if [ -n "$PS1" ]; then
echo -n -e "\033]0;`hostname`\007"
fi

TNSNAMES.ORA file

TNSNAMES.ORA is a configuration file that defines databases addresses for establishing connections to the database. It resides in ORACLE HOME\NETWORK\ADMIN directory. Below is the sample entry in TNSNAMES.ORA

<addressname> =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(Host = <hostname>)(Port = <port>))
)
(CONNECT_DATA =
(SERVICE_NAME = <sid>)
)
)

Read data from a file in perl

Below is the sample perl script, if you want to read some data from a file and pass it to as a argument in your perl script.

In my case, I have some object ids in a file and for each id I have to run oocheck command.

#!/usr/local/bin/perl

open FILE, "testoids" or die $!;

while ()

{

chomp $_;

systemCmd("oocheck -id $_");

}

close(FILE);

sub systemCmd {

my($cmd) = @_;

print("$cmd\n");

(system($cmd) == 0) || die ("cmd $cmd failed");

}

Linux time command

The time command runs the specified program command with the given arguments. When command finishes, time writes a message to standard output giving timing statistics about this program run.

See below example:

[vishnu@solaris /mnt/usr/vishnu] time myProgram arg1

real 24m10.951s

user 6m2.390s

sys 0m15.705s

[vishnu@solaris /mnt/usr/vishnu]

Above command outputs the time taken by myProgram.

· real - Elapsed time from beginning to end of program

· user - time used by the program itself and any library subroutines it calls

· sys- time used by the system calls invoked by the program (directly or indirectly)