Bulk Insert Fails !

Hello

I am trying to execute a BULK INSERT ... this is my code
BULK INSERT myTableSQL.dbo.[Daily_ss] FROM 'c:\daily_ss.txt'
WITH (
DATAFILETYPE='native',
FIELDTERMINATOR = 'char(9)',
ROWTERMINATOR = '\n'
)

But I get an Error

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 1. Make sure the field terminator and row terminator are
specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
The statement has been terminated.

1. The FIELDTERMINATOR in my file is the tab ...is it correct that i
declare it as char(9)?

2. the date format in the text file are dd/mm/yyyy will i have a
problem with this? Because SQL uses mm/dd/yyyy

3. I used this with DATAFILETYPE='char' and i get the same error

Please HELP :)
ThanksHi savvaschr,

I guess you are right at you 1st doubt as you have specified 'char(9)' and
not actual tab. So I guess you need to paste actual tab charecter inthe
quotes, also make sure that you have dropped indexes. I am a bit doubtful
about the 2nd # as it shouldn't be a problem.

Please let us know the solution.

Best regards,
Tushar.|||Will the data fit in the column?
Is your column varchar(50) and the data is greater than 50 characters?

http://sqlservercode.blogspot.com/|||I copy the 'tab' character from the file and i put it as FIELDSEPARETOR
but it still dont work and i 've tested it with another file and table
with no date fields.

So maybe is the tab character OR is the indexes of the table . Shall i
remove the indexes?
And if i remove them in order for the BULK INSERT to work and I have
to add them again whats the purpose of BULK INSERTing them instead of
adding row by row ?|||<savvaschr@.nodalsoft.com.cy> wrote:

> Hello
> I am trying to execute a BULK INSERT ... this is my code
> BULK INSERT myTableSQL.dbo.[Daily_ss] FROM 'c:\daily_ss.txt'
> WITH (
> DATAFILETYPE='native',
> FIELDTERMINATOR = 'char(9)',
> ROWTERMINATOR = '\n'
> )
> But I get an Error
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 1. Make sure the field terminator and row terminator are
> specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give
> any information about the error.
> The statement has been terminated.
> 1. The FIELDTERMINATOR in my file is the tab ...is it correct that i
> declare it as char(9)?
> 2. the date format in the text file are dd/mm/yyyy will i have a
> problem with this? Because SQL uses mm/dd/yyyy
> 3. I used this with DATAFILETYPE='char' and i get the same error
> Please HELP :)
> Thanks

I believe the command you want is...

BULK INSERT myTableSQL.dbo.[Daily_ss] FROM 'c:\daily_ss.txt'
WITH (
DATAFILETYPE='char',
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)

Craig|||i Use the above and i got an error

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1,
column 22. Make sure the field terminator and row terminator are
specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give
any information about the error.
The statement has been terminated.

I am openign a unix file and read from it ...is there a chance that the
'\n' is not the RowTerminator ? What posible character except '\n'
might be?

Thanks a lot
Savvas|||(savvaschr@.nodalsoft.com.cy) writes:
> i Use the above and i got an error
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 22. Make sure the field terminator and row terminator are
> specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give
> any information about the error.
> The statement has been terminated.
>
> I am openign a unix file and read from it ...is there a chance that the
> '\n' is not the RowTerminator ? What posible character except '\n'
> might be?

\n as terminator has always been problematic, because \n in the format
specification is interpreted as \r\n. I can't recall that I have ever
been able to get it to work.

A work around is to open the file from Windows with an editor, and make
sure that when you save again that lines are terminated with \r\n.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||<savvaschr@.nodalsoft.com.cy> wrote:

>i Use the above and i got an error
> Server: Msg 4866, Level 17, State 66, Line 1
> Bulk Insert fails. Column is too long in the data file for row 1,
> column 22. Make sure the field terminator and row terminator are
> specified correctly.
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'STREAM' reported an error. The provider did not give
> any information about the error.
> The statement has been terminated.
>
> I am openign a unix file and read from it ...is there a chance that the
> '\n' is not the RowTerminator ? What posible character except '\n'
> might be?
> Thanks a lot
> Savvas

As Erland already pointed out, the most likely culprit is the fact that your
file is from a Unix server. This is definitely the problem if the records
in your file have 22 fields. As far as I can tell, you have 2 options:

1. Do as Erland suggested and "fix" the file before you import it... either
with a text editior or little utility program you write (please see below).

2. See if the code running on the Unix host can be changed to use \r\n
instead of \n.

Craig|||Ok Guys
I forced unix to use chr(13) at the ena of line and now its ok
but in date fields i am geting an error saying:

code page 737 doesnt exist

I have tried both dd/mm/yyyy and mm/dd/yyyy formats and i still take
the same error|||(savvaschr@.nodalsoft.com.cy) writes:
> Ok Guys
> I forced unix to use chr(13) at the ena of line and now its ok
> but in date fields i am geting an error saying:
> code page 737 doesnt exist
> I have tried both dd/mm/yyyy and mm/dd/yyyy formats and i still take
> the same error

For date formats, you are best of using YYYY-MM-DD (or YYYYMMDD). However
that message, which I have never seen, is something different, and not
related to date format.

Going back to your first post, I see that you have specified
DATAFILETYPE='native'. Native here means that the data is the binary
form of the SQL Server data types. So a datetime value, should be an
8-bit binary value. I don't think your Unix system produces that.

Try DATAFILETYPE='character' instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||<savvaschr@.nodalsoft.com.cy> wrote:

> Ok Guys
> I forced unix to use chr(13) at the ena of line and now its ok
> but in date fields i am geting an error saying:
> code page 737 doesnt exist
> I have tried both dd/mm/yyyy and mm/dd/yyyy formats and i still take
> the same error

It's terrible to admit this, but I've been in a shop that only does American
English for years, so my experience with code pages is limited. However, I
would suggest using the format yyyymmdd. I believe this is guaranteed to be
interpreted correctly by SQL Server regardless of locale or codepage.

Good luck,

Craig|||OK

I forced put ^m as line terminator at unix files and i put the date
field format as
yyyy-mm-dd and its working ok

Thanks a lot to everybody
Savvas