BULK INSERT with format file - 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 2005 Forums

Transact-SQL (2005)

BULK INSERT with format file

Author

Topic

bjoerns
Posting Yak Master

154 Posts

Posted - 2009-02-02 : 06:00:40

Hi there,

does anyone have a sample format file for BULK INSERTing from a data file with fixed column length, please?

Thanks,
Bjoern

AvanthaSiriwardana
Yak Posting Veteran

78 Posts

Posted - 2009-02-02 : 06:20:39

http://doc.ddart.net/mssql/sql70/ba-bz_9.htm

Avantha Siriwardana
Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

bjoerns
Posting Yak Master

154 Posts

Posted - 2009-02-19 : 10:25:56

I'm having trouble even with a simple example file.

BULK INSERT FFTest
   FROM 'C:\Temp\FFTest.txt'
   WITH (FORMATFILE = 'C:\Temp\FFTest1.fmt')
-- WITH (FORMATFILE = 'C:\Temp\FFTest2.fmt')

gives me an error:

The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.

These are the files:

FFTest.txt
==========
1234567890123456
PPPPPPPPRNBQKBNR
RNBQKBNRPPPPPPPP

FFTest1.fmt
===========
9.0
2
1       SQLCHAR       2       8       ""   1     Col1         SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       2       8       ""   2     Col2         SQL_Latin1_General_CP1_CI_AS

FFtest2.fmt
===========
9.0
2
1       SQLCHAR       2       8       ""   1     Col1         SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       2       8       "\r\n"   2     Col2         SQL_Latin1_General_CP1_CI_AS

How can the 1st column be too long when I tell SQL Server its size is 8? Any ideas?

Thanks,
Bjoern

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources