locked
Importing CSV file into SQL server using Powershell RRS feed

  • Question

  • Hi all,

    I'm trying to import a .csv file into SQL server using powershell. The .csv file is received on a weekly basis and has different number of fields each week(50, 60, 80, 110 etc). I created a staging table in SQL server with all the fields(170). How can I insert the data into SQL server using powershell.

    # Database variables
    #$arg1 = $args[0]
     
     #Write-Host  $arg1
    $sqlserver = "xxx" 
    $database = "yyy" 
    $table = "zzz" 
    # CSV variables 
    $csvfile = "C:aaa.csv" 
    $csvdelimiter = "," 
    $FirstRowColumnNames = $true
    Write-Host "Script started..." 
    #$elapsed = [System.Diagnostics.Stopwatch]::StartNew()  
    #[void][Reflection.Assembly]::LoadWithPartialName("System.Data") 
    #[void][Reflection.Assembly]::LoadWithPartialName("System.Data.SqlClient") 
    # 50k worked fastest and kept memory usage to a minimum 
    $batchsize = 500000000 
    # Build the sqlbulkcopy connection, and set the timeout to infinite 
    $connectionstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database;" 
    $bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock) 
    $bulkcopy.DestinationTableName = $table 
    $bulkcopy.bulkcopyTimeout = 0 
    $bulkcopy.batchsize = $batchsize
    $reader = New-Object System.IO.StreamReader($csvfile)  
    #$columns = (Get-Content $csvfile -First 1).Split($csvdelimiter)
    $headers = $reader.ReadLine()
    $columns = $headers.Split($csvdelimiter)
    $columnList = ""
    
    foreach ($column in $columns) {  
            $columnList = $columnList + "["+$column + "],"
        }
        $columnList = $columnList + "[raw_data]"
    $sqlColumnPart = "INSERT INTO dbo.aaa ($columnList ) VALUES "
    
    
    while (($line = $reader.ReadLine()) -ne $null)  { 
        $columns = $line.Split($csvdelimiter)
        $sqlDataPart = "( "
        foreach ($column in $columns) {  
            $sqlDataPart = $sqlDataPart + "'$column'" + ","
        }
        $sqlDataPart = $sqlDataPart + " '' )"
    
        $sqlStatement = $sqlColumnPart + $sqlDataPart
        Write-Host "$sqlStatement" 
    
    }

    Thursday, May 24, 2018 12:32 AM

All replies