title: Powershell function to import csv file to SQL Server database table
body: <p>I have created a PowerShell function that bulk copies data from a <code>.csv</code> file (first row is the header), and inserts the data in to a SQL Server database table. </p>
  
  <p>See my code:</p>
  
  <pre><code>function BulkCsvImport($sqlserver, $database, $table, $csvfile, $csvdelimiter, $firstrowcolumnnames) {
      Write-Host "Bulk Import 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 = 50000 
  
      # Build the sqlbulkcopy connection, and set the timeout to infinite 
      $connectionstring = "Data Source=$sqlserver;Integrated Security=true;Initial Catalog=$database;" 
  
      # Wipe the bulk insert table first
      Invoke-Sqlcmd -Query "TRUNCATE TABLE $table" -ServerInstance $sqlserver -Database $database
  
      $bulkcopy = New-Object Data.SqlClient.SqlBulkCopy($connectionstring, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock) 
      $bulkcopy.DestinationTableName = $table 
      $bulkcopy.bulkcopyTimeout = 0 
      $bulkcopy.batchsize = $batchsize 
  
      # Create the datatable, and autogenerate the columns. 
      $datatable = New-Object System.Data.DataTable 
  
      # Open the text file from disk 
      $reader = New-Object System.IO.StreamReader($csvfile) 
      $columns = (Get-Content $csvfile -First 1).Split($csvdelimiter) 
  
      if ($firstrowcolumnnames -eq $true) { $null = $reader.readLine() } 
  
      foreach ($column in $columns) {  
          $null = $datatable.Columns.Add() 
      } 
  
      # Read in the data, line by line 
      while (($line = $reader.ReadLine()) -ne $null)  { 
          $null = $datatable.Rows.Add($line.Split($csvdelimiter)) 
  
          $i++; 
          if (($i % $batchsize) -eq 0) {  
              $bulkcopy.WriteToServer($datatable)  
              Write-Host "$i rows have been inserted in $($elapsed.Elapsed.ToString())." 
              $datatable.Clear()  
          }  
      }  
  
      # Add in all the remaining rows since the last clear 
      if($datatable.Rows.Count -gt 0) { 
          $bulkcopy.WriteToServer($datatable) 
          $datatable.Clear() 
      } 
  
      # Clean Up 
      $reader.Close(); 
      $reader.Dispose() 
  
      $bulkcopy.Close(); 
      $bulkcopy.Dispose() 
  
      $datatable.Dispose() 
  
      Write-Host "Bulk Import Completed. $i rows have been inserted into the database." 
      # Write-Host "Total Elapsed Time: $($elapsed.Elapsed.ToString())" 
      # Sometimes the Garbage Collector takes too long to clear the huge datatable. 
      $i = 0
      [System.GC]::Collect()
  }
  </code></pre>
  
  <p>I am looking to modify the above though so that the column names in the <code>.csv</code> file match up with the column names in the SQL Server database table. They should be identical. At the moment the data is being imported in to the incorrect database columns.</p>
  
  <p>Could I get some assistance as what I need to do to modify the above function to achieve this?</p>