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.