tags:programs, tools, utilities, windows
use Spreadsheet::ParseExcel;
use strict;
use warnings ;
package ExcelToSqlInsert;
my $VERSION = 0.2.0;
my $DebugLevel = 4 ;
my $ExcelFileToParse = "$ARGV[0]" ; my $OutputDir="$ARGV[1]" ;sub main {logMsg ( "$0 MAIN START " ) ; my $delimiter = chr($ARGV[2]);
my $rowEnd = chr($ARGV[3]);
my $InputDir = "$ARGV[4]" ;my $FileToPrint = $OutputDir . "/" . "output.sql" ;
logMsg ( "\$ExcelFileToParse is $ExcelFileToParse" ) ; ParseExcel ( $ExcelFileToParse , $delimiter ) ;
logMsg ( "$0 MAIN STOP " ) ; } #eof sub main sub ParseExcel {my $ExcelFileToParse = shift ;
my $delimiter = shift ;
my $rowEnd = "\n"; my $strToReturn = "" ; my $parser = Spreadsheet::ParseExcel->new();
my $workbook = $parser->Parse("$ExcelFileToParse");foreach my $worksheet (@{$workbook->{Worksheet}}) {my $workSheetName = $worksheet->{'Name'} ; my $FileToPrint = ToUnixDir("$OutputDir" . '/' . "$workSheetName" . "\.TableInsert\.sql") ;
$strToReturn .= "SET NOCOUNT ON;SET XACT_ABORT ON;
GO
TRUNCATE TABLE [dbo].[" . $workSheetName . "] ; SET IDENTITY_INSERT [dbo].[" . $workSheetName . "] ON;BEGIN TRANSACTION;
INSERT INTO [dbo].[" . $workSheetName . "] ( \n" ; my $RowMin = $worksheet->{MinRow} ; my $RowMax= $worksheet->{MaxRow} ; # my ( $RowMin, $RowMax) = $worksheet->row_range();# my ( $col_min, $col_max ) = $worksheet->col_range();for my $row ( ($RowMin ) .. $RowMax) {my $col_min = $worksheet->{MinCol} ; my $col_max = $worksheet->{MaxCol} ; $strToReturn .= ") \n SELECT " if ( $row == 1) ;
$strToReturn .= "\n UNION ALL SELECT " if ( $row >1) ;
my $tokenStart = ();
$tokenStart = '\'' if ( $row>0) ;
$tokenStart = '[' if ( $row == 0) ;
my $tokenStop = () ;
$tokenStop = '\'' if ( $row>0) ;
$tokenStop = ']' if ( $row == 0) ;
for my $col ( ($col_min ) .. $col_max ) {my $cell = $worksheet->{Cells}[$row][$col] ; next unless $cell;
logMsg( " Row, Col = ($row, $col)\n" ) ; logMsg ( " Value = ", $cell->Value() ); my $cellValueToReplace = $worksheet->{Cells}[$RowMin][$col] ; print "\$cellValueToReplace is $cellValueToReplace \n" ; my $StrValueToReplaceWith = $cell->Value() ;
print " \$StrValueToReplaceWith is $StrValueToReplaceWith \n" ; my $token = $cell->Value() ;
$token =~ s/\'/\'\'/g ; #replace ' with '' to escape sql
#NULL is a key word should not be tokenized in MSSQL sqlif ( $token eq 'NULL')
{$strToReturn .= $token . ' , '; }
else {$strToReturn .= $tokenStart . $token . $tokenStop . ' , ' ; }
#print "\$strToReturn is $strToReturn \n" ; #$strToReturn .= $cell->Value() . $delimiter ; #The Value#print "Unformatted = ", $cell->Unformatted(), "\n";} #eof columnchop ($strToReturn) ; #remove the latest delimiterchop ($strToReturn) ; #remove the latest delimiter$strToReturn .= $rowEnd ; #end the row print "\$FileToPrint is $FileToPrint " ; } #eof row$strToReturn .=
"\n COMMIT;RAISERROR (N\'[dbo].[" . $workSheetName . "]: Insert Batch: 1.....Done!\', 10, 1) WITH NOWAIT;GO
SET IDENTITY_INSERT [dbo].[" . $workSheetName . "] OFF;SELECT \'SELECT TOP 5 * FROM [dbo].[" . $workSheetName . "] ORDER BY 1 DESC ; \' SELECT TOP 5 * FROM [dbo].[" . $workSheetName . "] ORDER BY 1 DESC ; " ;
printToFile ( $FileToPrint , $strToReturn ) ;
$strToReturn = '' ; } #eof worksheet} #eof subsub printToFile {my $FileOutput = shift ;
my $StringToPrint = shift ;
#READ ALL ROWS OF A FILE TO ALIST open (FILEOUTPUT, ">$FileOutput") || print "could not open the \$FileOutput $FileOutput!\n"; print FILEOUTPUT $StringToPrint ;
close FILEOUTPUT ;
#debug $strToReturn .= $StringToPrint; }
# =========================================== eof sub printToFilesub trim
{$_[0]=~s/^\s+//;
$_[0]=~s/\s+$//;
return $_[0];}
sub readFileReturnString {my $fileToRead = shift ;
print " readFileReturnString \$fileToRead is $fileToRead \n" ; my $string = ();
{local $/=undef;open FILE, "$fileToRead " or print "Couldn't open \$fileToRead $fileToRead : $!";
$string = <FILE>;
close FILE;
}
return $string ; } #eof sub readFileReturnString # =========================================== eof sub trim #log a message according to the DebugLevel# use: logMsg ("msg");sub logMsg
{my $msg = shift || "empty msg" ; my $importance = shift || 0 ;
my $CurrentPerlScriptName = ToUnixDir("$0" );my $niceMonth = GetANiceMonth ();
my $logFile = "$CurrentPerlScriptName" . '_' . "$niceMonth" . '.log' ;
$msg = GetANiceTime () .' --- ' . $msg . " \n" ;
if (defined $importance && $importance == 1 ) {$msg = "\n============================================================ \n" . $msg ; $msg = $msg . "============================================================ \n" ;}
#debug nothingif ( $DebugLevel == 0 ) { return ; }
#just print the messageif ( $DebugLevel == 1 ) { print "$msg"; }
#print the message in if ( $DebugLevel == 2 ) {#READ ALL ROWS OF A FILE TO ALIST open (LOG, ">> $logFile") || print "could not open the \$logFile $logFile !!!\n";
print LOG $msg ;
close LOG;
}
#print the message in if ( $DebugLevel == 3 ) {#READ ALL ROWS OF A FILE TO ALIST open (LOG, ">> $logFile") || print "could not open the \$logFile $logFile !!!\n";
print LOG $msg ;
close LOG;
print $msg ;
} #eof if ( $DebugLevel == 3 ) } #eof sub logMsg #log a message according to the DebugLevelsub logErrorMsg
{my $errorMsg = shift ;
my $importance = shift ;
my $CurrentPerlScriptName = "$0" ; my $niceMonth = GetANiceMonth ();
my $errorLogFile = "$CurrentPerlScriptName" . "_" . "$niceMonth" . '.error.log' ;
$errorMsg = GetANiceTime () .' --- ' . $errorMsg . " \n" ;
if ( $importance == 1 ) {$errorMsg = "\n============================================================ \n" . $errorMsg ; $errorMsg= $errorMsg. "============================================================ \n" ;}
#debug nothingif ( $DebugLevel == 0 ) { return ; }
#just print the messageif ( $DebugLevel == 1 ) { print $errorMsg ; } #print the message in a error log fileif ( $DebugLevel == 2 ) {#READ ALL ROWS OF A FILE TO ALIST open (ERRLOG, ">> $errorLogFile") || print "could not open the \$errorLogFile $errorLogFile !!!\n";
print ERRLOG $errorMsg ;
close ERRLOG;
}
#print the message in if ( $DebugLevel == 3 ) {#READ ALL ROWS OF A FILE TO ALIST open (ERRLOG, ">> $errorLogFile") || print "could not open the \$errorLogFile $errorLogFile !!!\n";
print ERRLOG $errorMsg ;
close ERRLOG;
print $errorMsg ;
} #eof if ( $DebugLevel == 3 ) } #eof sub logErrorMsg #GET A NICE TIME sub GetANiceTime {# Purpose: returns the time in yyyymmdd-format my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
#---- change 'month'- and 'year'-values to correct format ---- $min = "0$min" if ($min < 10);
$hour = "0$hour" if ($hour < 10);
$mon = $mon + 1;
$mon = "0$mon" if ($mon < 10);
$year = $year + 1900;
$mday = "0$mday" if ($mday < 10);
return "$year\.$mon\.$mday" . "-" . "$hour\:$min\:$sec";
} #eof sub GetANiceTimesub GetANiceMonth {# Purpose: returns the time in yyyymmdd-format my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
#---- change 'month'- and 'year'-values to correct format ---- $mon = $mon + 1;
$mon = "0$mon" if ($mon < 10);
$year = $year + 1900;
return "$year\.$mon" ;
} #eof sub GetANiceTimesub ToUnixDir {my $dir = shift || ""; #replace all the \ s with / s$dir =~ s/\\/\//g ;
return $dir ; } #eof sub ToUnixDir# =========================================== eof sub trim # Action !!!main();
1 ;
__END__
=head1 NAME
ExcelToSqlInsert -
=head1 DESCRIPTION
This script generates files based on templates and a simple Excel input file.=head1 README
for how-to use this script check the ReadMe.txt
=head1 PREREQUISITES
SpreadSheet::ParseExcel
=head1 COREQUISITES
CGI
=pod OSNAMES
any ( Windows 7 only tested )
=pod SCRIPT CATEGORIES
configurations
=cut
tags:programs, tools, utilities, windows
Posts
All Comments
