Automating the updation of SQL server database

Welcome Forums General PowerShell Q&A Automating the updation of SQL server database

This topic contains 4 replies, has 2 voices, and was last updated by

 
Participant
2 years, 3 months ago.

  • Author
    Posts
  • #51044

    Participant
    Points: 1
    Rank: Member

    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.

  • #51081

    Participant
    Points: 1
    Rank: Member

    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

  • #51279

    Participant
    Points: 1
    Rank: Member

    Any Lead plzzz...

  • #51300

    Participant
    Points: 0
    Rank: Member

    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)
  • #51395

    Participant
    Points: 1
    Rank: Member

    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?

The topic ‘Automating the updation of SQL server database’ is closed to new replies.