Bulk Insert - (0 row(s) affected) I am trying to import a .CSV file into a mssql table using bulk insert (Although I am open to other methods). I have a bulk insert sql statement but it says (0 row(s) affected) when I execute the the file. However, I have data in the file itself. 

Sample CSV File (Header + One Line below header)



.CSV Header


  changedate,deactivate,lockout,noemail,empno,orighire,salary,salut,fname,mi,lname,username,hstreet1,hstreet2,hcity,hstate,hcountry,hzip,busphone,busext,cellular,empemail,jobtitle,jobcode,jobgroup,orglevel1,orglevel2,orglevel3,orglevel4,orglevel5,company,perfdate,supervisor,misc1,misc2,misc3,misc4,active


.CSV Content


  2014-04-03 00:00:00,NO ,NO ,NO ,1133,2014-04-03 00:00:00,1111.1111,,test,test,test,user.test,111 test way,,Sacramento,CA,USA,11111,,,,test@test.org,test test,057,02,sss,261,SAC,,test,ttt,1911-11-11 00:00:00,1111,N,,test,0,NULL




.SQL

BULK
INSERT dbo.Archive
FROM 'C:\scripts\User.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\r\n',
FIRSTROW = 2
)
GO


I tried to achieve this through ps and this was the outcome and code:

# Database variables
$sqlserver = "dbserver"
$database = "db"
$table = "tb"

# CSV variables
$csvfile = "C:\scripts\User.csv"
$csvdelimiter = ","
$firstRowColumnNames = $true

################### No need to modify anything below ###################
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 = 50000

# 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

# 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 "Script complete. $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.
[System.GC]::Collect()


ERROR


  PS C:\scripts> C:\scripts\Import-CSVtoSQL.ps1
  Script started...
  Exception calling "WriteToServer" with "1" argument(s): "Received an invalid column length from the bcp 
  client for colid 38."
  At C:\scripts\Import-CSVtoSQL.ps1:51 char:2
  +     $bulkcopy.WriteToServer($datatable)
  +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
      + FullyQualifiedErrorId : SqlException
The original question had \n\r as the row terminator. On Windows, a row terminator is typically \r\n, with the carriage return first and the linefeed second (for obscure but interesting historical reasons).  Try that in your BULK INSERT command.

Next, I would next try loading the file in a hex editor to validate the exact content of the line breaks.  For example, if the data is Unicode you may need \0\r\0\n as a separator, and/or the DATAFILETYPE ='widechar' flag. Note 0x0A is decimal 10, 0x0D is decimal 13: the ASCII codes for line feed (\n) and carriage return (\r), respectively.