BULK INSERT - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

BULK INSERT

Author

Topic

allend2010
Starting Member

28 Posts

Posted - 2003-03-07 : 10:09:53

Hello:

I am attempting to do a BULK INSERT with a large file into a temp table. The file contains multiple records with different formatting so there is no field delimiter however, each record in the text file is exactly 250 bytes long. What I am doing in my stored procedure is:

CREATE TABLE #Records (Records VARCHAR(250))

BULK INSERT #Records FROM 'C:\RecordFile.txt'
WITH
(
DATAFILETYPE = 'char',
ROWTERMINATOR = '\n'
)

SELECT * FROM #Records

When I try to execute, I get the following error messages:

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.

I have verifed that the text file is ansi so I use 'char' and I have ensured that the linefeed (\n) is the proper control character for the file. Even when I try to use other control charcters such as \r or a combination of \r\n I get the same error.

Any help you can provide would be greatly apprecieated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-07 : 10:13:44

Did you get this file from a Unix/*nix source? They don't use the same line terminator character as Windows/DOS. Try \r instead of \n and see if that works.

Failing that, try making the column larger than 250, say 500. If the line terminator is there it will parse and insert correctly, if it's not there then you'll get an error anyway and you'll have to correct the file itself.

allend2010
Starting Member

28 Posts

Posted - 2003-03-07 : 10:33:21

Thanks for your input. The file was created in a unix environment and was ftp'd to us so I tried using both \r and \n and \r\n which don't seem to work. If were to use VB I can pick up the control character with vbLf so I finds it there. Hmmm...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-07 : 10:36:51

Try this:

BULK INSERT #Records FROM 'C:\RecordFile.txt'
WITH
(
DATAFILETYPE = 'char',
ROWTERMINATOR = char(10)
)

If that doesn't work:

DECLARE @rowterm varchar(1)
SET @rowterm=char(10)
BULK INSERT #Records FROM 'C:\RecordFile.txt'
WITH
(
DATAFILETYPE = 'char',
ROWTERMINATOR = @rowterm
)

And if THAT doesn't work...DTS.

allend2010
Starting Member

28 Posts

Posted - 2003-03-07 : 11:01:16

Thanks again for your help. I added the code below which actually executes without any errors when I change 'char' to 'widechar' but it doesn't actually input any data :(. Any last suggestions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources