# Automating the updation of SQL server database

**URL:** <https://forums.powershell.org/t/automating-the-updation-of-sql-server-database/7023>\
**Category:** PowerShell Help\
**Created:** [August 18, 2016, 10:27pm UTC](https://forums.powershell.org/t/automating-the-updation-of-sql-server-database/7023 "2016-08-18T22:27:23Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![amit-aman](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/amit-aman/32/320_2.png) [@amit-aman](https://forums.powershell.org/u/amit-aman)\
**Post date:** [August 18, 2016, 10:27pm UTC](https://forums.powershell.org/t/automating-the-updation-of-sql-server-database/7023/1 "2016-08-18T22:27:23Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![amit-aman](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/amit-aman/32/320_2.png) [@amit-aman](https://forums.powershell.org/u/amit-aman)\
**Post date:** [August 19, 2016, 1:03am UTC](https://forums.powershell.org/t/automating-the-updation-of-sql-server-database/7023/2 "2016-08-19T01:03:25Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![amit-aman](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/amit-aman/32/320_2.png) [@amit-aman](https://forums.powershell.org/u/amit-aman)\
**Post date:** [August 20, 2016, 12:05am UTC](https://forums.powershell.org/t/automating-the-updation-of-sql-server-database/7023/3 "2016-08-20T00:05:10Z")

</div>

Any Lead plzzz…

---

<div class="post-metadata">

**Author:** ![curtis-smith](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/curtis-smith/32/121_2.png) [@curtis-smith](https://forums.powershell.org/u/curtis-smith)\
**Post date:** [August 20, 2016, 12:42pm UTC](https://forums.powershell.org/t/automating-the-updation-of-sql-server-database/7023/4 "2016-08-20T12:42:46Z")

</div>

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)
```

---

<div class="post-metadata">

**Author:** ![amit-aman](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/amit-aman/32/320_2.png) [@amit-aman](https://forums.powershell.org/u/amit-aman)\
**Post date:** [August 21, 2016, 11:24pm UTC](https://forums.powershell.org/t/automating-the-updation-of-sql-server-database/7023/5 "2016-08-21T23:24:40Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:40pm UTC](https://forums.powershell.org/t/automating-the-updation-of-sql-server-database/7023/6 "2024-05-16T20:40:41Z")

</div>

Closed on May 16, 2024, 8:40pm UTC


