Automating the updation of SQL server database

PowerShell Help
#1

Hi Team,

I am trying to update the SQL server database using an excel file. My application is, I use to get updated data every weekend in excel sheet. Till now I have been updating my SQL server database using SQL server import and export wizard manually every weekend. I want my code to pick the updated sheet and update my database automatically. Kindly suggest the best possible way.

I tried several codes, but no luck. Recent one that I am trying is:

set-psdebug -strict
$ErrorActionPreference = “stop”
$ExcelFilePath= ‘C:\Master Aug 17 2016.xlsx’
$Worksheet=‘MASTER’
$DataRange= ‘’
$Header= $true
$ColumnNames=’*’
$DestinationTable=‘MASTER’
$Destinationinstance=‘SQLEXPRESS’
$Destinationdatabase=‘final’
$DestinationWindowsSecurity=$true
$DestinationUserID=’’
$DeleteContentsOfTableBeforeCopy=$false
$PrecisionForNumericData=1

if (!(Test-Path $ExcelFilePath))
{
Write-Error “Can’t find ‘$($ExcelFilePath)’. Sorry, can’t proceed because of this”
exit
}

try {
$Connection = New-Object system.data.odbc.odbcconnection
$TheConnectionString = ‘Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=’+$ExcelFilePath+’; Extended Properties=“READONLY=TRUE; HDR=’+”$(if ($Header){‘YES’}else{‘NO’})"+’"’
$Connection.ConnectionString=$TheConnectionString
$Connection.Open()
}
catch
{
$ex = $_.Exception
Write-Error “whilst opening connection to $ExcelFilePath using ‘$($TheConnectionString)’ : $($ex.Message). Sorry, can’t proceed because of this”
exit
}
try {
$Query = New-Object system.data.odbc.odbccommand
$Query.Connection = $connection
$Query.CommandText = ‘Select’ +$columnNames+’ from [’+$Worksheet+$DataRange+’]’

$Reader = $Query.ExecuteReader([System.Data.CommandBehavior]::SequentialAccess)
}
catch
{
$ex = $_.Exception
Write-Host “whilst making the query ‘$($Query.CommandText)’ $ex.Message Sorry, but we can’t proceed because of this!”
Exit;
}

$columns=$reader.GetSchemaTable()|select columnName, datatype
if ($DeleteContentsOfTableBeforeCopy) {$deletionScript=“ELSE DELETE from $DestinationTable “} else {$deletionScript=’’}
$CreateScript=@”
IF NOT EXISTS
(select TABLE_NAME from information_schema.tables
where TABLE_NAME like ‘$DestinationTable’)
CREATE TABLE $DestinationTable (
“@
$CreateScript+=$columns| foreach-object{$datatype=”$($.dataType)"; "`n`t[$($.columnName.Trim())] $(switch($dataType){ ‘double’{“numeric(18,$PrecisionForNumericData)”} ‘boolean’{‘int’} ‘decimal’{‘Money’} ‘datetime’{‘DateTime’}default {‘NVARCHAR(MAX)’}}),”}
$CreateScript=$CreateScript.Substring(0,$CreateScript.Length-1)+“`n`t)`n $deletionScript”

try {
$SqlCommand = new-object (‘Data.SqlClient.SqlCommand’) $CreateScript, $DestinationConnectionString;
$SqlCommand.Connection.Open();
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host “Message: $($event.Message)”};
$SqlCommand.Connection.add_InfoMessage($handler);
$success=$SqlCommand.ExecuteNonQuery();
$bulkCopy = new-object (“Data.SqlClient.SqlBulkCopy”) $DestinationConnectionString
$bulkCopy.DestinationTableName = $DestinationTable
$bulkCopy.BatchSize = 5000
$bulkcopy.NotifyAfter=200
$bulkCopy.BulkCopyTimeout = 0
$objectEvent= Register-ObjectEvent $bulkcopy SqlRowsCopied -Action {write-host "Copied $($eventArgs.RowsCopied) rows "}
$bulkCopy.WriteToServer($reader)
}
catch{
$ex = $_.Exception
Write-Error “Whilst doing the bulk copy ‘$($Query.CommandText)’ $ex.Message Sorry, but we can’t proceed because of this!”
}

Thanks in advance.

#2

It Worked for me.

I modified the script as;

$serverName = “DC2012\SQLEXPRESS”;
$databaseName = “final” ;
$tableName = “master” ;
$filepath = “C:\Master Aug 17 2016.xlsx”;

$Excel = New-Object -ComObject Excel.Application
$Workbook = $Excel.Workbooks.Open($filepath)
$Worksheet = $Workbook.Worksheets.Item(1)
$startRow = 1

#create System.DataTable

$dt = new-object “System.Data.DataTable”
[void]$dt.Columns.Add(“vertical”, [System.Type]::GetType(“System.String”))
[void]$dt.Columns.Add(“Emp Name”, [System.Type]::GetType(“System.String”))
Do {
$ColValues1 = $Worksheet.Cells.Item($startRow, 1).Value()
$ColValues2 = $Worksheet.Cells.Item($startRow, 2).Value()
$startRow++
$dt.Rows.Add($ColValues1,$ColValues2)
}
While ($Worksheet.Cells.Item($startRow,1).Value() -ne $null)

$Excel.Quit()

#connect to SQL Server and import the system.data.table
$SQLServerConnection = “Data Source=$serverName;Integrated Security=true;Initial Catalog=$databaseName;”
$bulkCopy = new-object (“Data.SqlClient.SqlBulkCopy”) $SQLServerConnection
$bulkCopy.DestinationTableName = $tableName
$bulkCopy.WriteToServer($dt)

I checked with only 2 columns. Kindly help me in copying full data from excel instead of copying data of only 2 columns.

Thanks again

#3

Any Lead plzzz…

#4

This is where you are selecting your columns, you would just need to add the additional columns that you need

Do {
$ColValues1 = $Worksheet.Cells.Item($startRow, 1).Value()
$ColValues2 = $Worksheet.Cells.Item($startRow, 2).Value()
$startRow++
$dt.Rows.Add($ColValues1,$ColValues2)
}
While ($Worksheet.Cells.Item($startRow,1).Value() -ne $null)

For Example:

Do {
$ColValues1 = $Worksheet.Cells.Item($startRow, 1).Value()
$ColValues2 = $Worksheet.Cells.Item($startRow, 2).Value()
$ColValues3 = $Worksheet.Cells.Item($startRow, 3).Value()
$ColValues4 = $Worksheet.Cells.Item($startRow, 4).Value()
$startRow++
$dt.Rows.Add($ColValues1,$ColValues2,$ColValues3,$ColValues4)
}
While ($Worksheet.Cells.Item($startRow,1).Value() -ne $null)
#5

Thanks Curtis for the Reply.
But, I don’t want to add each columns manually. There are hundreds of columns.
So, any way to put it into any loop?