Automating the updation of SQL server database

This topic contains 4 replies, has 2 voices, and was last updated by Profile photo of amit aman amit aman 1 month ago.

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #51044
    Profile photo of amit aman
    amit aman
    Participant

    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
    Profile photo of amit aman
    amit aman
    Participant

    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
    Profile photo of amit aman
    amit aman
    Participant

    Any Lead plzzz...

    #51300
    Profile photo of Curtis Smith
    Curtis Smith
    Participant

    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)
    • This reply was modified 1 month, 1 week ago by Profile photo of Curtis Smith Curtis Smith.
    #51395
    Profile photo of amit aman
    amit aman
    Participant

    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?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.