DJWalker
asked on
BUL:K INSERT OF UNICODE FILE
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.
Anybody any ideas?
ASKER
Thanks for a quick reply.
I have tried your suggestion, but it makes no difference.
One of my tables does have a char column in it, but apart from the NULL terminator, there are no characters in the file greater than 127 or less than 32.
The row and column are the first row and the first column being inserted, the column being a single digit in one case.
The unicode and ASCII data are both the same, ie ASCII and unicode versions of the same file so I think the data itself should be OK.
could you either post the first lines of the file here, or send it to me?
What i suspect is that the value in the file is longer than what is defined in the database...
CHeers
What i suspect is that the value in the file is longer than what is defined in the database...
CHeers
ASKER
I'll send the file by e-mail.
Thanks.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help.
CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]
Specifies the code page of the data in the data file. CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values greater than 127 or less than 32.
CODEPAGE value Description
ACP Columns of char, varchar, or text data type are converted from the ANSI/Microsoft Windows® code page (ISO 1252) to the SQL Server code page.
OEM (default) Columns of char, varchar, or text data type are converted from the system OEM code page to the SQL Server code page.
RAW No conversion from one code page to another occurs; this is the fastest option.
code_page Specific code page number, for example, 850
Now, if the column in the table is shorter than the data to be inserted, this would explain the "truncation" error...
As the row/columns are indicated, I would look at the file to see which are the differences to the previous rows...
CHeers