BULK INSERT UNICODE

Darren Walker

unread,

Apr 30, 2002, 10:40:56 AM4/30/02

to

I am trying to use BULK INSERT to populate a table in a
SQL Server 2000 database from a unicode file.

I am using the following:

BULK INSERT tblTable FROM 'E:\File.txt'
WITH (
CHECK_CONSTRAINTS,
DATAFILETYPE = 'widechar',
FIELDTERMINATOR = '\0',
FIRSTROW = 2,
KEEPIDENTITY,
MAXERRORS = 0,
ROWTERMINATOR = '\0\n',
TABLOCK )

but I'm getting an error - "Bulk insert data conversion
error (truncation) for row 2, column 1 (fkColumnName)."

The same paramaters (except DATAFILETYPE) for BULK INSERT
work fine when the file is ASCII. I have tried
\0\0 for FIELDTERMINATOR and \0\0\n for ROWTERMINATOR but
this doesn't work.

My unicode file has been created by saving my ASCII file
as a 'Unicode text file' in Wordpad.

Anybody any ideas?

Darren Walker

unread,

May 13, 2002, 6:32:36 AM5/13/02

to

Hi Simon,

I can't use notepad because it destroys the null
terminators, whereas WordPad doesn't.

BULK INSERT will successfully import a comma terminated
file converted to Unicode in WordPad, it just won't do it
with the NULL terminated file.

The problem seems to be simply that BULK INSERT won't work
with null terminated unicode files.

I can provide the files, SQL statements and files to
enable you to reproduce the problem if you like. Would you
like me to email them to you?

Darren

>-----Original Message-----
>Hi Darren,
>
>My name is Simon Liao. Thank you for using Microsoft
support.
>
>If I understand this issue correctly, you got an error
when you tried to use BULK

>INSERT to populate a table in a SQL Server 2000 database

from a Unicode file.
>
>First, I would like you to try to convert the text file
in Notepad, not the WordPad.
>Try to convert the text file in Unicode format in Notepad
and then re-try the Bulk
>Insert, what is the result?
>
>If it does not resolve this issue, you may need to
provide me with the following
>items, thus I can reproduce the issue on my machine:
>
>1. The table tblTable schema.
>
>2. The text file e:\file.txt
>
>3. The reproduce steps if possible
>
>Thanks,
>
>Sincerely,
>
>Simon Liao
>Microsoft Support
>
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Get Secure! - www.microsoft.com/security
>
>
>.
>

Simon Liao[MS]

unread,

May 13, 2002, 11:44:55 PM5/13/02

to

Hi Darren,

Thanks for your update.

yes, it would be much appreciated if you could provide the files, SQL statements
and files, so that I can reproduce this issue on my side. However, you do not need
to send me directly, you can attach it to your post.

Look forward to hearing from you.

Darren Walker

unread,

May 14, 2002, 9:17:17 AM5/14/02

to

Simon,

To reporoduce the problem, create the following table:

CREATE TABLE [tblTable] (
[Column1] [int] NOT NULL ,
[Column2] [Decimal] (18,2) NOT NULL ,
[Column3] [char] (2) COLLATE Latin1_General_BIN
NOT NULL ,
[Column4] [datetime] NOT NULL)

From the following BULK INSERT statements, the first 2
will succeed, the 3rd will fail.

-- ASCII file, null terminators
BULK INSERT tblTable FROM 'E:\tblTable.txt'
WITH (
CODEPAGE = 'ACP',
CHECK_CONSTRAINTS,

FIELDTERMINATOR = '\0',
FIRSTROW = 2,
KEEPIDENTITY,
MAXERRORS = 0,
ROWTERMINATOR = '\0\n',
TABLOCK )

-- Unicode file, comma terminators
BULK INSERT tblTable FROM 'E:\tblTable_uni2.txt'
WITH (
DATAFILETYPE = 'widechar',
CODEPAGE = 'ACP',
CHECK_CONSTRAINTS,
FIELDTERMINATOR = '\,',

FIRSTROW = 2,
KEEPIDENTITY,
MAXERRORS = 0,

ROWTERMINATOR = '\,\n',
TABLOCK )

-- Unicode file, null terminators
BULK INSERT tblTable FROM 'E:\tblTable_uni.txt'
WITH (
DATAFILETYPE = 'widechar',
CODEPAGE = 'ACP',
CHECK_CONSTRAINTS,

FIELDTERMINATOR = '\0',
FIRSTROW = 2,
KEEPIDENTITY,
MAXERRORS = 0,
ROWTERMINATOR = '\0\n',
TABLOCK )

Kind regards,

Darren

>.
>

BULKINS.zip

Simon Liao[MS]

unread,

May 16, 2002, 6:05:05 AM5/16/02

to

Hi Darren,

Just wanted to let you know that I can reproduce this issue on my side with
the files you kindly provided and need more research on it. I will get back to
you as soon as possible.

Ed

unread,

May 17, 2002, 2:49:33 PM5/17/02

to

Hi Simon,
My name is Ed. I have a very similar problem w/ BCP and
Unicode. I have posted a message, Titled "Bcp Unicode
Problem" in the SqlServer.programming section.
Posted 5-17-02 9:46 am

Would it be possible for you to take a look and let me
know what you think?

Thanks
Ed

>-----Original Message-----
>Hi Darren,
>
>My name is Simon Liao. Thank you for using Microsoft
support.
>
>If I understand this issue correctly, you got an error

when you tried to use BULK

>INSERT to populate a table in a SQL Server 2000 database

from a Unicode file.
>
>First, I would like you to try to convert the text file
in Notepad, not the WordPad.
>Try to convert the text file in Unicode format in Notepad
and then re-try the Bulk
>Insert, what is the result?
>
>If it does not resolve this issue, you may need to
provide me with the following
>items, thus I can reproduce the issue on my machine:
>
>1. The table tblTable schema.
>
>2. The text file e:\file.txt
>
>3. The reproduce steps if possible
>
>Thanks,
>

>Sincerely,
>
>Simon Liao
>Microsoft Support
>
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Get Secure! - www.microsoft.com/security
>
>

>.
>

Simon Liao[MS]

unread,

May 21, 2002, 5:11:18 AM5/21/02

to

Hi Darren,

Sorry that I have not been able to response sooner.

After carefully researching on this error, it seems SQL Server does not support null as the terminator.

Based on SQL Server Books online (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dtsppropfl_2n09.asp), Typical values are
",", ";", tab and "|". Since it does not say that null value cannot be acted as the filed terminator, I tried to find some official documents for you, but
failed.

I know this isn't exactly what you wanted to hear, but I hope this helps some.

--- Simon Liao

Simon Liao[MS]

unread,

May 21, 2002, 5:25:24 AM5/21/02

to

Hi Ed,

I have put my opinions on the thread you mentioned in your post: Titled "Bcp

Unicode Problem" in the SqlServer.programming section.

Hope that helps!

--- Simon Liao

Darren Walker

unread,

May 22, 2002, 5:37:46 AM5/22/02

to

Hmmm.

Based on SQL Server Books Online, SQL Server DOES support
a null terminator...

http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/adminsql/ad_impt_bcp_0fqq.asp

I'll have to pre-filter my data and use tab as a
terminator to work around the bug. :0)

Thanks for your help anyway.

Darren

>.
>

Sten Madsen

unread,

Jun 9, 2002, 2:33:40 PM6/9/02

to

I've got a problem very similar to yours, but only if I use a format
file to control the load of my SQL table. It doesn't works for flat
files in unicode format but it does for ansi format. I also tried to
generate the format file by BCP-utility, but with no different result.

I DO, however, have success by using bulk insert on flat unicode
datafiles the way you do. I will guess the problem is the terminators.
Try with FIELDTERMINATOR = ',' or '\t' and ROWTERMINATOR = '\n'.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Darren Walker

unread,

Jun 10, 2002, 4:24:58 AM6/10/02

to

I originally posted this question on the MSDN newsgroups
(http://msdn.microsoft.com/newsgroups/default.asp).

Eventually I decided to use /t as a terminator (as you suggest) but this
meant filtering the data beforehand, something we didn't have to do when
using /0.

Sten Madsen

unread,

Jun 11, 2002, 2:35:38 PM6/11/02

to

I haven't been able to find an useful explanation what is the problems
nature. So I did my own little research which may be useful for others.
I will wellcome any further comments.

-------------------------------------

I) Unicode file created with notepad or wordpad (windows XP)
has the following properties::

1) 2-bytes header FF FE
2) 2-bytes field terminator, f.eks 09 00 for tab
3) 4-bytes end of line terminator, for example 0D 00 0A 00
4) all other characters are 2-bytes unicode

It can by loaded into SQL server database with

bcp testDB..test in c:\data\test.txt -w

or

BULK INSERT testDB..test
FROM 'c:\projects\test.txt'
WITH (FIRSTROW = 1,
DATAFILETYPE = 'widechar')

GO

I didn't find it possible to use format files with BULK INSERT of
Notepad created unicode files.

------------------------------------

II) Unicode file created with

bcp testDB..test out c:\data\test.txt

where I answered the prompts with nvarchar,0,10,\t and
nvarchar,0,10,\n (the unicode file had two text fields of 10 char)

has the following properties:
1) no header
2) 1-byte field terminator 09 for tab
3) 2-bytes end of line terminator 0D 0A
4) all other characters are 2-bytes unicode

It can not be loaded into an SQL server database with

bcp testDB..test in c:\data\test.txt -w

but it can by using a format file

bcp testDB..test in c:\data\test.txt -fc:\data\test.fmt

or

bcp testDB..test in c:\data\test.txt

where you answer the prompts, as you did exporting the data from the SQL
server.

With BULK INSERT data can be loaded with

BULK INSERT testDB..test
FROM 'c:\data\test.txt'
WITH (FORMATFILE = 'c:\projects\test.fmt',
FIRSTROW = 1,
DATAFILETYPE = 'char')
GO

NOTICE that DATAFILETYPE = 'char'!!!!

The format filen test.fmt looked like this
8.0
2
1 SQLNCHAR 0 10 "\t" 1 tekst1
SQL_Latin1_General_CP1_CI_AS
2 SQLNCHAR 0 10 "\r\n" 2 tekst2
SQL_Latin1_General_CP1_CI_AS