locked
inserting CSV file content into a MS SQL table RRS feed

  • General discussion

  • I am tasks with inserting content from various CSV files into a table in a MS SQL Database. Each day a new CSV file will arrive with a new file name and would like to reconize the new file based on it's time stamp and insert the contents into the MS SQL stagging table. Also, I will need to be able to import several files if we happen to miss a day and to insert the files during the instal population of data.

    I am intending on running the Powershell script in the MS SQL server as a job as a Administration user I have installed.

    The script I have so far is as follows:

    Get-ChildItem“\\hq\heidfs\data\oilsnd\Sunrise\AlarmsEvents\*.csv” | % {
    	Start-job-Name“$($_)” `
    				   –InitializationScript { IpmoFunctions-Force–DisableNameChecking } `
    				   –ScriptBlock {
    		$DataImport = Import-Csv-Path$args[0]
    		$DataTable=Out-DataTable–InputObject$DataImport
    		Write-DataTable–ServerInstance“PIPROD”`
    		-Database “AlarmEvents“`
    		–TableName “STAGGING“`
    		-Data $DataTable
    	} –ArgumentList $_.fullname
    }
    Get-Job | Wait-Job | Out-Null
    Remove-Job -State Completed
    

    I have not built the section to look for the existance of new CSV files, or the ablity to look if there are existing files and import them.

    Any help would be greatly appreciated.

    Tuesday, January 5, 2016 8:49 PM

All replies

  • I highly recommend using SSIS as it is designed to do exactly what you are trying to do.

    Aside from that advise we do not write or design scripts.  YOU will need to ask a specific question.

    I suggest looking ar SQLBulkCopy and scheduled tasks.

    Here is an example:

    # ==============================================================================================
    #
    # Microsoft PowerShell Source File -- Created with SAPIEN Technologies PrimalScript 2012
    #
    # NAME: Copy-CsvToSQLServer.ps1
    #
    # AUTHOR: jvierra , Designed Systems & Services
    # DATE  : 8/16/2013
    #
    # COMMENT: This is a template only
    #           2015-05-22 Fixed some issues
    #
    # ==============================================================================================
    Param (
    	[parameter(Mandatory = $true)]
    	[string]$CsvPath,
    	[parameter(Mandatory = $true)]
    	[string]$CsvFile,
    	[parameter(Mandatory = $true)]
    	[string]$TargetServer,
    	[parameter(Mandatory = $true)]
    	[string]$TargetDatabase,
    	[parameter(Mandatory = $true)]
    	[string]$TargetTable,
    	[switch]$Truncate
    	#$ColumnMappings
    )
    
    Begin {
    	
    	Function Get-CsvReader {
    		Param (
    			$csvPath,
    			$csvFile
    		)
    		
    		$csvFile = $csvFile.Replace('.', '#')
    		$connStr = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties="Text;HDR=Yes;FORMAT=Delimited"' -f $csvPath
    		$csvConnection = New-Object System.Data.OleDb.OleDbConnection($connStr)
    		$csvConnection.Open()
    		$csvCommand = $csvConnection.CreateCommand()
    		$csvCommand.CommandText = "select * from [$csvFile]"
    		$csvCommand.ExecuteReader()
    	}
    }
    
    Process {
    	Try {
    		$csvReader = Get-CsvReader -csvPath $svPAth -csvFile $csvFile
    		$targetConnStr = "Data Source=$ServerName;Initial Catalog=$DbName;Integrated Security=True;"
    		$bulkCopy = New-Object Data.SqlClient.SqlBulkCopy($targetConnStr, [System.Data.SqlClient.SqlBulkCopyOptions]::KeepIdentity)
    		$bulkCopy.DestinationTableName = $TargetTable
    		$bulkCopy.WriteToServer($csvReader)
    		Write-Host "Table $SrcTable in $SrcDatabase database on $SrcServer has been copied to table $DestTable in $DestDatabase database on $DestServer"
    	} Catch {
    		throw $_
    	} Finally {
    		$csvReader.Close()
    		$bulkCopy.Close()
    	}
    }
    


    Or this method:

    # Copy-SQLBulk.ps1
    # This shows how to use BulkCopy in PowerShell by uploading a spreadsheet to an MSSQLServer data table.
    
    $filepath = 'C:\scripts\All.xlsx'
    $excelConnection="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$filepath;Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1'"
    $sqlConnection='Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=issue;Data Source=OMEGA\SQLEXPRESS;'
    $excelQuery='select * from [SearchResult$]'
    $tablename='SearchResult'
    
    Try{
        $conn = New-Object System.Data.OleDb.OleDbConnection($excelConnection) 
        $conn.open()
        $cmd=$conn.CreateCommand()
        $cmd.CommandText=$excelQuery
        $rdr=$cmd.ExecuteReader()
        
        # create the BC object
        $sqlbc=[System.Data.SqlClient.SqlBulkCopy]$sqlConnection
        $sqlbc.DestinationTableName=$tableName
        
        # add all columns - you can add as few  as you like.
        for($i=0; $i -lt $rdr.FieldCount;$i++){
            $fname=$rdr.GetName($i)
            Write-Host $fname -ForegroundColor green
            [void]$sqlbc.ColumnMappings.Add($fname, $fname)
        }
        
        # write all of the data to the table
        $sqlbc.WriteToServer($rdr)
    }
    Catch{
        Write-Host "$_" -ForegroundColor red
    }
    
    $sqlbc.Close()
    $conn.Close()
    
    
    

    Or this even simpler method:

    $csvfile='.\MyCsv.csv'
    $sqlTable='MyDataTable'
    $DataSource='MyServer\MyInstance'
    $DataBase='db1'
    
    $ConnectionString ='Data Source={0}; Database={`}; Trusted_Connection=True;' -f $DataSource,$DataBase
    $csvDataTable = Import-CSV -Path $csvfile | Out-DataTable
    $bulkCopy = new-object Data.SqlClient.SqlBulkCopy($ConnectionString)
    $bulkCopy.DestinationTableName=$sqlTable
    $bulkCopy.WriteToServer($csvDataTable)
    
    


    \_(ツ)_/

    Tuesday, January 5, 2016 9:20 PM
  • I have never used SSIS, and didn't have the time to learn it over using something tried and true like a script.  I didn't know "The Scripting Guys" forum didn't write script.  Sorry to have troubled you all.
    Tuesday, January 5, 2016 10:31 PM
  • I have never used SSIS, and didn't have the time to learn it over using something tried and true like a script.  I didn't know "The Scripting Guys" forum didn't write script.  Sorry to have troubled you all.

    That is correct.  You will find that all of these forums are not free consulting forums.  I posted a lot of code that you can use but you will have to adapt it to your system,  We cannot do that for you.

    There are many consultants around your area I am sure.  You can contact them to help you do this.

    Here is the banner from the forum. I can help you understand what you can do: https://social.technet.microsoft.com/Forums/scriptcenter/en-US/c47b1bc2-f7fd-4d2e-8ff2-e8a81ce090d4/this-forum-is-for-scripting-questions-rather-than-script-requests?forum=ITCG


    \_(ツ)_/

    Tuesday, January 5, 2016 10:38 PM