BULKI INSERT And Format File

Hi All,

I have to import data into my database. The data comes in csv files. They really are CSV: comma separated.

Because there are now two type of import files, I have to make two format files for BULK insert command. (In previous versions of my application there was only one import file, so the BULK INSERT Worked without fomrat file.)

The trouble is that it does not work . The files come in Unicode encoding (starting with FF FE).

I always get different errors as I change anything. The most annoying one:


Bulk load data conversion error (truncation) for row 1, column 2 (b).

It's annoying, because I don't have a clue what it means.

Some other are:
Unexpected EOF, Codepage not installed (when I specify CODEPAGE='1201'), Column too long, etc...

Can anyone help me, please? This is driving me crazy.

My test table:


CREATE TABLE [dbo].[aaa](

[ a] [nvarchar](1000) NULL,

[ b] [nvarchar](1000) NULL,

[ c] [nvarchar](1000) NULL

) ON [PRIMARY]

My BULK INSERT STATEMENT:


BULK INSERT [aaa]

FROM 'c:123.csv'

WITH(FORMATFILE = 'c:123.xml',)

My format file (123.xml):



<?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="NCharTerm" TERMINATOR=","/>

<FIELD ID="2" xsi:type="NCharTerm" TERMINATOR="," />

<FIELD ID="3" xsi:type="NCharTerm" TERMINATOR="
" />

</RECORD>

<ROW>

<COLUMN SOURCE="1" NAME="a" xsi:type="SQLNVARCHAR" />

<COLUMN SOURCE="2" NAME="b" xsi:type="SQLNVARCHAR" />

<COLUMN SOURCE="3" NAME="c" xsi:type="SQLNVARCHAR"/>

</ROW>

</BCPFORMAT>