title: Bulk insert from csv file - Ignore rows with errors - SQL Server
body: <p>I am trying to import data from a csv file to SQL Server. There are thousands of entries in the csv file and we have a lot of rows with incorrect data in it.</p>
  
  <p>Some of the rows in the CSV File are:</p>
  
  <pre><code>`"ID"|"EmpID"|"FName"|"LName"|"Gender"|"DateOfBirth"
  "1"|"90043041961"|"ABCD"|"TEST"|"F"|"1848-05-05 00:00:00.000"
  "1"|"10010161961"|"XYZ"|"TEST"|"F"|"1888-12-12 00:00:00.000"
  .
  .
  ..
  ..
  ....
  "4"|"75101141821PPKKLL"|"LLKK"|"F"|"1925-09-09 00:00:00.000"|""
  "4"|"32041401961UUYYTT"|"PPLL"|"M"|"1920-01-01 00:00:00.000"|""
  .
  .....
  "25"|"00468132034"|"FGTT"|"OOOO"|"F"|"1922-11-11 00:00:00.000"
  "25"|"00468132034"|"KKKK"|"PPPP"|"F"|"1922-11-11 00:00:00.000"
  </code></pre>
  
  <p>Creating the TestTable and trying to insert data (from csv file) into it:</p>
  
  <pre><code>create table TestTable
  (
       ID varchar(5),
       EmpID varchar(25),
       FName varchar(25),
       LName varchar(25),
       Gender varchar(5),
       DateOfirthB varchar(30)
  );
  </code></pre>
  
  <p>I am using the following script to import data from csv file to the <code>TestTable</code> in SQL Server:</p>
  
  <pre><code>bulk insert TestTable
  from 'C:\TestData.csv'
  with 
     (firstrow = 2,
      DATAFILETYPE='char',
      FIELDTERMINATOR= '"|"',
      ROWTERMINATOR = '\n',
      ERRORFILE ='C:\ImportErrors.csv',
      MAXERRORS = 0,
      TABLOCK
     );
  </code></pre>
  
  <p>Errors:</p>
  
  <blockquote>
    <p>Msg 4863, Level 16, State 1, Line 1<br>
    Bulk load data conversion error (truncation) for row 32763, column 5 (Gender).</p>
    
    <p>Msg 4863, Level 16, State 1, Line 1<br>
    Bulk load data conversion error (truncation) for row 32764, column 5 (Gender).</p>
  </blockquote>
  
  <p>Is there any way to ignore the rows (in the csv file) which can not be added for some or other reason and insert the one's which have the correct syntax?</p>
  
  <p>Thanks</p>
  
  <p>PS: I can not use SSIS. Only allowed to use SQL</p>