how-to generate mssql insert statements from Excel with perl

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 sql

if ( $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 column

chop ($strToReturn) ; #remove the latest delimiter

chop ($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 sub

sub 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 printToFile

sub 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 nothing

if ( $DebugLevel == 0 ) {     return ; } 

#just print the message

if ( $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 DebugLevel

sub 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 nothing

if ( $DebugLevel == 0 ) {     return ; } 

#just print the message

if ( $DebugLevel == 1 )  {         print $errorMsg ;     } 

#print the message in a error log file

if ( $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 GetANiceTime

sub 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 GetANiceTime

sub 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