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

Ready for the cloud !!!

I just finished the implementation of the Deployment strategy of my current C# , MSSQL 2005 / 2008 project ... Thus one presentation layer to one database a full dev , test , qa , sox , fb and prod production deployment is fast ... really fast ; )

So far the configuration data is in an excel - e.g. the forking of the environments requires the addition of a new Excel row ...

So this is not too far away from a massive cloud roll-out - at the end all the needed configurations per a worker instance or whatever logical running thing could be retrieved in a tabular format ... This means just an additional template creation , and regeneration of the template ...

Btw. space matters in templates .. I learned it the hard way ..

I am the king of the universe ... Template Generator ... so f..cking what ...

START ===== TemplateGenerator.0.1.0.cmd

@echo off  

ECHO %0 > %0.log

ECHO %0.error.log >%0.error.log

set BaseDir=%cd%

echo BaseDir is %BaseDir%  1>>%0.log 2>>%0.error.log

ECHO.

set ExcelFileToParse="%BaseDir%\input\configs.xls"

echo ExcelFileToParse is %ExcelFileToParse%  1>>%0.log 2>>%0.error.log

echo.

set InputDir=%BaseDir%\input

echo InputDir is %InputDir%  1>>%0.log 2>>%0.error.log

echo.

::CONFIGURE THE CURRENT DIRECTORY AS THE OUTPUT DIR

set OutputDir=%BaseDir%\output

echo OutputDir is %Outputdir%  1>>%0.log 2>>%0.error.log

echo.

::set PerlScript=parseExcelToCsv.pl

set PerlScript="%BaseDir%\TemplateGenerator.0.1.0.pl"

echo PerlScript is %PerlScript%  1>>%0.log 2>>%0.error.log

ECHO.

::This is the ASCII number of the token delimiter to use in the csv file

set TokenDelimiterAsciiNumber=44

echo TokenDelimiterAsciiNumber is %TokenDelimiterAsciiNumber%  1>>%0.log 2>>%0.error.log

::This is the ASCII number of the token delimiter to use in the csv file

set RowEndAsciiNumber=13

echo RowEndAsciiNumber is %RowEndAsciiNumber% %RowEndAsciiNumber% 1>>%0.log 2>>%0.error.log

echo Action !!!  1>>%0.log 2>>%0.error.log

echo perl %PerlScript% %ExcelFileToParse% %OutputDir% %TokenDelimiterAsciiNumber% %RowEndAsciiNumber% %InputDir% 1>>%0.log 2>>%0.error.log

perl %PerlScript% %ExcelFileToParse% %OutputDir% %TokenDelimiterAsciiNumber% %RowEndAsciiNumber%   %InputDir% 1>>%0.log 2>>%0.error.log

::UNCOMMENT TO OPEN THE ERROR LOG AND THE RUN LOG FILES 

::%0.error.log

::%0.log

::debug pause

::SLEEP FOR 3 SECONDS

for /l %%i in (1,-1,0) do CLS&ECHO DONE !!! %%i&ping localhost -n 2 >NUL&CLS

END ================== TemplateGenerator.0.1.0.cmd

. 

START ===== TemplateGenerator.0.1.0.pl

use Spreadsheet::ParseExcel;

use strict;

package TemplateGenerator ; 

sub  main {

my $ExcelFileToParse = "$ARGV[0]" ; 

my $OutputDir="$ARGV[1]" ;

my $delimiter = chr($ARGV[2]);

my $rowEnd = chr($ARGV[3]);

my $InputDir = "$ARGV[4]" ;

my @arrayTemplateFiles = () ; 

my $arrayTemplateFiles = readDirGetArrayOfFiles ($InputDir); 

@arrayTemplateFiles = @$arrayTemplateFiles ; #dereference

# - foreach template file 

foreach my $templateFile (@arrayTemplateFiles ) {

print "working on  \$templateFile is $templateFile\n";

my $FileToPrint = buildFileToPrint ( $templateFile  , $OutputDir ) ; 

my $StrTemplateFile = readFileReturnString ( "$InputDir/$templateFile" ) ; 

ParseExcel ( $ExcelFileToParse  , $delimiter , $rowEnd, $StrTemplateFile , $FileToPrint) ; 

# -- parseExcel

# --- foreach row of the Excel file , read template file into strWork

# ---- foreach column in row - Find and Replace Col title from strWork

# ---- writeGeneratedFile with strWork

}    #eof foreach template file

} #eof sub main 

sub readDirGetArrayOfFiles     {

my $InputDir = shift ; 

# create a list of all *.pl files in

# the current directory

opendir(DIR, "$InputDir");

my @files = grep(/\.tmpl$/,readdir(DIR));

closedir(DIR);

# print all the filenames in our array

foreach my $templateFile (@files) {

print "\$templateFile is $templateFile\n";

}

return \@files; 

} #eof main

sub buildFileToPrint 

{

my $FileToPrint = shift ; 

my $OutputDir = shift ; 

$FileToPrint =~m/^.*(\\|\/)(.*)/; #  strip the remote path and keep the filename

$FileToPrint =~ s/^(.*)(\.)(.*)/$1/ ;     #strip the file extension

$FileToPrint =~ s/^.*(\\|\/)(.*)/$2/ ;                                 # strip the remote path and keep the filename

$FileToPrint = "$OutputDir/$FileToPrint"  ;        

return $FileToPrint ; 

} #eof sub BuildFileToPrint 

sub ParseExcel {

my $ExcelFileToParse = shift ; 

my $delimiter = shift ; 

my $rowEnd = shift ; 

my $strTemplate = shift ; 

my $FileTemplate = shift ; 

my $parser   = Spreadsheet::ParseExcel->new();

my $workbook = $parser->Parse("$ExcelFileToParse");

#foreach my $oWkS (@{$oBook->{Worksheet}}) {

foreach my $worksheet (@{$workbook->{Worksheet}}) {

#for my $worksheet ( $workbook->worksheets() ) {

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 + 1) .. $RowMax) {

my $strToReturn = $strTemplate ; 

my $FileToPrint = $FileTemplate ; 

my $col_min = $worksheet->{MinCol} ; 

my $col_max = $worksheet->{MaxCol} ; 

for my $col ( ($col_min + 1) .. $col_max ) {

my $cell = $worksheet->{Cells}[$row][$col]  ; 

next unless $cell;

print " Row, Col    = ($row, $col)\n";

print " Value = ", $cell->Value() ;                #The Value

my $cellValueToReplace = $worksheet->{Cells}[$RowMin][$col]  ; 

print "\$cellValueToReplace is $cellValueToReplace \n" ; 

my $StrToReplace  = '¤' . $cellValueToReplace->Value() . '¤' ;  

print "\$StrToReplace is $StrToReplace  \n" ; 

my $StrValueToReplaceWith = $cell->Value() ;

print " \$StrValueToReplaceWith  is $StrValueToReplaceWith  \n" ; 

$strToReturn =~ s/$StrToReplace/$StrValueToReplaceWith/g ; 

$FileToPrint =~ s/$StrToReplace/$StrValueToReplaceWith/g ; 

#print "\$strToReturn is $strToReturn \n" ; 

#$strToReturn .=  $cell->Value() . $delimiter       ;                #The Value

#print "Unformatted = ", $cell->Unformatted(), "\n";

}

chop ($strToReturn) ; #remove the latest delimiter

$strToReturn .= $rowEnd ;     #end the row    

print "\$FileToPrint  is $FileToPrint " ; 

printToFile ( $FileToPrint , $strToReturn ) ; 

} #eof row

} #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 

# Action !!!

main();

1 ; 

__END__ 

END ================== TemplateGenerator.0.1.0.pl

. 

START ===== TemplateGenerator.0.1.0.ReadMe.txt

This Instruction assumess  that use the following dir to extract the project 

BaseDir=E:\perl\sfw\csv

if you do not simply find E:\perl\sfw\csv and replace it with <<YourExtractDir>> in this document 

- Install strawberry perl from here: http://strawberryperl.com/ , add the folder containing the dmake.exe and perl.exe to your path 

- to install the OLE::Storage_Lite go to E:\Perl\sfw\csv\lib\OLE-Storage_Lite-0.19\OLE-Storage_Lite-0.19

run the following commands : 

perl Makefile.PL

dmake

dmake test

dmake install    

dmake clean     

- To install the SpreadSheet::ParseExcel module go to : 

cd E:\perl\sfw\csv\lib\Spreadsheet-ParseExcel-0.2603

run the following commands: 

perl Makefile.PL

dmake

dmake test

dmake install    

dmake clean     

- Now you have all the prerequisites to run the script :

- double click the E:\Perl\sfw\csv\parseExcelToCsv.2.0.cmd file if nothing happens check its contents ... 

END ================== TemplateGenerator.0.1.0.ReadMe.txt

how-to generate Select All from Table for MSSQL server with perl

:: START ===== Generate_SelectAll.1.1.cmd
:: File:Test.cmd

:: Set up the database name
set DbName=CAS_DEV

:: Set up the run folder where the perl script is executing
set RunFolder=D:\cas\sfw\perl

:: Set up the full path to the Perl script
set PerlScript=%RunFolder%\Generate_SelectAll.1.1.pl

:: Set up the input folder where the input file residues
set InputFolder=D:\cas\sfw\perl\input

:: Set up the full path the MetaFile
set InputMetaFile=%InputFolder%\%DbName%.txt

:: Set up the full path to Perl
set PerlBin=C:\Perl\bin\perl.exe

:: Set up the output folder
set FolderOutput=D:\cas\sfw\perl\output\SelectAll

:: remove the contents of the %FolderOutput%
rmdir /f %FolderOutput%

:: CREATE THE DIRECTORY IF IT DOES NOT EXIST
mkdir %FolderOutput%



:: Action !!!
%PerlBin% %PerlScript% %InputMetaFile% %FolderOutput%

::debug
pause

:: VersionHistory:
:: 1.0.20101001_213742 --- Yordan Georgiev -- Initial creation. Input meta data parsing and debugging
:: 1.1.20101001_221209 --- Yordan Georgiev --- Print Select All per table with Schema . OK


:: END ================== Generate_SelectAll.1.1.cmd


:: START ===== Generate_SelectAll.1.1.pl
use strict ;

package Test ;

my $FileInputForMetaData = $ARGV[0] ;
my $FolderOutput = $ARGV [1] ;


sub main
{
#read the input metadata file
my @ArrayInputFile = ReadInputMetaDataFile();
#FOREACH LINE OF THE FILE DO SOMETHING
my $OldTableName = '' ;
my $ColList = '' ;
foreach my $line ( @ArrayInputFile )
{
#debug print "$line" ;
my ( $DbName , $SchemaName , $TableName , $ColName ) = split("\t" , $line ) ;
chop ( $ColName ) ;
#debug print " \$DbName , \$SchemaName , \$TableName , \$ColName \n" ;
#debug print " $DbName , $SchemaName , $TableName , $ColName \n " ;
if ( $TableName eq $OldTableName )
{
#debug print "old Table " ;
$ColList = $ColList . ' , ' . "[$ColName]" ;
} #eof if
else
{
GenerateSql($DbName , $SchemaName , $OldTableName , $ColList);
#debug print "New Table ! \n " ;
$ColList= '' ;
$ColList = "[$ColName]" ;
} #eof else


$OldTableName = $TableName ;

} #eof foreach line
# END FOREACH LINE

}
# =========================================== eof sub main


sub ReadInputMetaDataFile
{
#READ ALL ROWS OF A FILE TO ALIST
open (FILEINPUTFORMETADATA, "<$FileInputForMetaData") || print "could not open the \$FileInputForMetaData $FileInputForMetaData!\n"; my @arrayInputFile = ;
close FILEINPUTFORMETADATA;
return @arrayInputFile ;
}
# =========================================== eof sub ReadInputMetaDataFile


sub GenerateSql
{
my $DbName = shift ;
my $SchemaName = shift ;
my $TableName = shift ;
my $ColList = shift ;
if ( trim($TableName) eq '' )
{ return ; }
#$ColList= substr( $ColList , 0 , length($ColList ) - 2 ) ;

my $SqlString =
"SELECT $ColList FROM $DbName\.$SchemaName\.[$TableName] ;\n " ;

my $FileOutput="$FolderOutput/$DbName\.$SchemaName\.$TableName\.SelectAll\.Script\.sql" ;

#READ ALL ROWS OF A FILE TO ALIST
open (FILEOUTPUT, ">$FileOutput") ||
print "could not open the \$FileOutput $FileOutput!\n";
print FILEOUTPUT $SqlString ;
close FILEOUTPUT ;

#debug print $SqlString ;

}
# =========================================== eof sub GenerateSql

sub trim
{
my $string = shift ;
$string =~ s/^\s+//;
$string =~ s/\s+$//;
return $string ;
}

# =========================================== eof sub trim

# Action !!!
main() ;

1 ;

__END__

# VersionHistory:
# 1.1.20101001_222618 --- Yordan Georgiev --- Generates Select All with full [] column list from Table
:: END ================== Generate_SelectAll.1.1.pl

perl generate dates ,

source:http://www.dbforums.com/perl-dbi/1609392-generating-dates-perl-cocatenating-database-field.html

use strict ;
use POSIX qw(strftime);
use Time::Local 'timelocal_nocheck';

# here are your two passed in arguments
my $startDate = '2010-09-23';
my $daysToAdd = '360';

$startDate =~ /(\d+)-(\d+)-(\d+)/;
my ($mday,$mon,$year) = ($3,$2,$1);

# convert to epoch time
my $date = timelocal_nocheck(0,0,0,
$mday-1,
$mon-1,
$year
);
# make the new filenames
for (1..$daysToAdd) {
my $NewDate = strftime('%Y%m%d',0,0,0,
(localtime($date+=86400))[3,4,5]
);
my $RunTimeStart = "$NewDate" . "000000" ;
my $RunTimeStop = "$NewDate" . "235959" ;
print "$RunTimeStart---$RunTimeStop\n" ;
} #eof for

How-to generate insert statements from a table without including the Primary Key's

-- ORIGINAL SOURCE:
-- Generating INSERT statements in SQL Server - BY Sumit Amar
- I just turned it into stored procedure and added the meta data query to not include the PK's
-- Thanks to Sumit Amar and Enjoy !!!

create PROCEDURE [dbo].[procUtils_InsertGenerator]
(
@domain_user varchar(50),
@tableName varchar(100)
)


as

--Declare a cursor to retrieve column specific information for the specified table
DECLARE cursCol CURSOR FAST_FORWARD FOR


--old takes the PK's SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName

/* NEW without PK's */
select object_name(c.object_id) "TABLE_NAME", c.name "COLUMN_NAME", s.name "DATA_TYPE"
from sys.columns c
join sys.systypes s on (s.xtype = c.system_type_id)
where object_name(c.object_id) in (select name from sys.tables where name not like 'sysdiagrams')
AND object_name(c.object_id) in (select name from sys.tables where [name]=@tableName ) and c.is_identity=0




OPEN cursCol
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement
DECLARE @dataType nvarchar(1000) --data types returned for respective columns
DECLARE @IDENTITY_STRING nvarchar ( 100 )
SET @IDENTITY_STRING = ' '
select @IDENTITY_STRING
SET @string='INSERT '+@tableName+'('
SET @stringData=''

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType

IF @@fetch_status<>0
begin
print 'Table '+@tableName+' not found, processing skipped.'
close curscol
deallocate curscol
return
END

WHILE @@FETCH_STATUS=0
BEGIN
IF @dataType in ('varchar','char','nchar','nvarchar')
BEGIN
--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+'
END
ELSE
if @dataType in ('text','ntext') --if the datatype is text or something else
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+'
END
ELSE
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly
BEGIN
SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
END
ELSE
IF @dataType='datetime'
BEGIN
--SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+'
--SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
--SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+'
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
-- 'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations
END
ELSE
IF @dataType='image'
BEGIN
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+'
END
ELSE --presuming the data type is int,bit,numeric,decimal
BEGIN
--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+'
--SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'
END

SET @string=@string+@colName+','

FETCH NEXT FROM cursCol INTO @tableName , @colName,@dataType
END
DECLARE @Query nvarchar(4000)

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName
exec sp_executesql @query
--select @query

CLOSE cursCol
DEALLOCATE cursCol


/*

use MyDataBase
go

DECLARE @RC int
DECLARE @domain_user varchar(50)
DECLARE @tableName varchar(100)

-- TODO: Set parameter values here.
set @domain_user='myDomainUser'
set @tableName = 'MyTableName'

EXECUTE @RC = [POC].[dbo].[procUtils_InsertGenerator]
@domain_user
,@tableName

*/