BULK INSERTing UNICODE data with format files

Hi,
I am trying to bulk insert data with UNICODE characters into a table using a
format file. I am using SQL Server 2000 with all the latest SPs.
When I try to bulk insert the data I get the following error.
"Bulk Insert: Unexpected end-of-file (EOF) encountered in data file."
What am I doing incorrectly?
Please help me out with this. What is the correct way to do this. My data
file will have UNICODE characters (for nchar, nvarchar sql types) and
would also have data for other types like (int, datetime etc). And I want to
use a format file.
Thanks in anticipation,
Nitin M
I have created the data file in the following way.
---
StreamWriter DataWriter = new
StreamWriter("data.txt",false,System.Text.Encoding.Unicode);
DataWriter.WriteLine("1/1/2005@.@.aa@.@.aaaa@.@.23@.@.-1.9879@.@.");
DataWriter.Close();
----
--
This is the definition of my table.
---
CREATE TABLE [dbo].[AllTypes] (
[mydate] [datetime] NULL ,
[mychar] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[myvarchar] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[myint] [int] NULL ,
[myreal] [real] NULL
) ON [PRIMARY]
---
The bulk insert query that I use
---
bulk insert alltypes from 'data.txt' with
(
datafiletype='widechar',
formatfile = 'bcp.format.txt',
tablock
)
---
My format file
---
8.0
5
1 SQLNCHAR 0 0 "@.@." 1 mydate ""
2 SQLNCHAR 0 0 "@.@." 2 mychar SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 0 "@.@." 3 myvarchar SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 0 "@.@." 4 myint ""
5 SQLNCHAR 0 0 "@.@.\r\n" 5 myreal ""
---Nitin M (nitin@.nowhere.com) writes:

> I am trying to bulk insert data with UNICODE characters into a table
> using a format file. I am using SQL Server 2000 with all the latest
> SPs.
> When I try to bulk insert the data I get the following error.
> "Bulk Insert: Unexpected end-of-file (EOF) encountered in data file."
> What am I doing incorrectly?
> Please help me out with this. What is the correct way to do this. My
> data file will have UNICODE characters (for nchar, nvarchar sql types)
> and would also have data for other types like (int, datetime etc). And I
> want to use a format file.
I only got half-way of solving this puzzle. You need to specify the
separators as Unicode as well. I tried this:
8.0
5
1 SQLNCHAR 0 0 "\0@.\0@." 1 mydate ""
2 SQLNCHAR 0 0 "\0@.\0@." 2 mychar SQL_Latin1_General_CP1_CI_AS
3 SQLNCHAR 0 0 "\0@.\0@." 3 myvarchar SQL_Latin1_General_CP1_CI_AS
4 SQLNCHAR 0 0 "\0@.\0@." 4 myint ""
5 SQLNCHAR 0 0 "\0@.\0@.\0\r\0\n" 5 myreal ""
This got me past the EOF error, but instead I got conversion errors for
the numeric values. You could make all columns characters columns, so
you see what BCP actually finds, and then maybe modify the separators
from this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Thanks a lot for looking into this.
Even I got around the problem by specifying separators as Unicode. The
reason you are getting conversion errors is due to the byte ordering of the
separators. Try the other byte order. It works for me. I am not getting any
conversion errors.
Is there no other cleaner way around this?
Thanks,
Nitin
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns973F74A723AEFYazorman@.127.0.0.1...
> Nitin M (nitin@.nowhere.com) writes:
>
> I only got half-way of solving this puzzle. You need to specify the
> separators as Unicode as well. I tried this:
> 8.0
> 5
> 1 SQLNCHAR 0 0 "\0@.\0@." 1 mydate ""
> 2 SQLNCHAR 0 0 "\0@.\0@." 2 mychar SQL_Latin1_General_CP1_CI_AS
> 3 SQLNCHAR 0 0 "\0@.\0@." 3 myvarchar SQL_Latin1_General_CP1_CI_AS
> 4 SQLNCHAR 0 0 "\0@.\0@." 4 myint ""
> 5 SQLNCHAR 0 0 "\0@.\0@.\0\r\0\n" 5 myreal ""
> This got me past the EOF error, but instead I got conversion errors for
> the numeric values. You could make all columns characters columns, so
> you see what BCP actually finds, and then maybe modify the separators
> from this.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Nitin M (nitin@.nowhere.com) writes:
> Thanks a lot for looking into this.
> Even I got around the problem by specifying separators as Unicode. The
> reason you are getting conversion errors is due to the byte ordering of
> the separators. Try the other byte order. It works for me. I am not
> getting any conversion errors.
Ah! Glad to hear that you where able to find it out yourself.

> Is there no other cleaner way around this?
The obvious idea would be to save the format file as Unicode, but that
does not work; you only get a message about unknown version. I tried in
SQL 2005 as well, but SQL 2005 appears to think that a Unicode file must
be an XML format file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx