CODEWITHSUNDEEP
sql-server
user3183055
user3183055

Reputation: 83

"BULK LOAD DATA CONVERSION ERROR for csv file

I am trying to import .csv file but i am getting "BULK LOAD DATA CONVERSION ERROR" for last column. File looks like:

"123456","123","001","0.00"

I have tried below rowterminator:

ROW TERMINATOR = "\"\r\n"

Nothing is working. Any ideas on what is causing this record to have this error? Thanks

Upvotes: 2

Views: 603

Answers (1)

Utkarsh
Utkarsh

Reputation: 589

As per given example below, remove the quotes in your csv and use the terminator as "\r\n".

Always use format xml when doing bulk insert. It provides several advantages such as validation of data files etc.

The format file maps the fields of the data file to the columns of the table. You can use a non-XML or XML format file to bulk import data when using a bcp command or a BULK INSERT or INSERT or Transact-SQL command

Considering the input file given by you, suppose you have a table as given below :

CREATE TABLE myTestFormatFiles (
  Col1 smallint,
  Col2 nvarchar(50),
  Col3 nvarchar(50),
  Col4 nvarchar(50)
);

Your sample Data File will be as follows : 

10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4

Sample format XML file will be :

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="7"/>
    <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
    <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="100" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  </RECORD>
  <ROW>
   <COLUMN SOURCE="1" NAME="Col1" xsi:type="SQLSMALLINT"/>
   <COLUMN SOURCE="2" NAME="Col2" xsi:type="SQLNVARCHAR"/>
   <COLUMN SOURCE="3" NAME="Col3" xsi:type="SQLNVARCHAR"/>
   <COLUMN SOURCE="4" NAME="Col4" xsi:type="SQLNVARCHAR"/>
  </ROW>
</BCPFORMAT>

If you are unfamiliar with format files, check XML Format Files (SQL Server).

Example is illustrated here

Upvotes: 1

Related Questions