Bulk Insert - Fixed Length File

[Jennifer]

I'm trying to do an insert using Bulk Insert with a fixed length file.
I'm using a format file.
I'm getting the following error message:
Cannot perform bulk insert. Invalid collation name for source column 16
in format file '\\wbhq.com\dfsdv\iDataInt\GOPFiles\GOPFormatFile. txt'.

Any suggestions are appreciated.

Thanks!
Jennifer
Format File Contents:
8.0
16
1 SQLCHAR 0 2 "" 0 Space ""
2 SQLCHAR 0 4 "" 1 YearID ""
3 SQLCHAR 0 2 "" 0 Space ""
4 SQLCHAR 0 2 "" 2 PeriodID ""
5 SQLCHAR 0 2 "" 3 CompanyID ""
6 SQLCHAR 0 1 "" 0 Dash ""
7 SQLCHAR 0 4 "" 0 Space ""
8 SQLCHAR 0 1 "" 0 Dash ""
9 SQLCHAR 0 4 "" 4 UnitID ""
10 SQLCHAR 0 1 "" 0 Dash ""
11 SQLCHAR 0 4 "" 5 AccountCode ""
12 SQLCHAR 0 5 "" 0 Space ""
13 SQLCHAR 0 1 "" 6 AccountType ""
14 SQLCHAR 0 29 "" 0 Space ""
15 SQLCHAR 0 16 "" 7 GLAmount ""
16 SQLCHAR 0 105 "\r\n" 0 Space ""

Bulk Insert Statement:
BULK INSERT FlatFile_GOP
FROM '\\wbhq.com\dfsdv\iDataInt\GOPFiles\GLPAM.GOP'
WITH
(
FORMATFILE =
'\\wbhq.com\dfsdv\iDataInt\GOPFiles\GOPFormatFile. txt'
)

Table Definition:
CREATE TABLE [dbo].[FlatFile_GOP] (
[YearID] [smallint] NOT NULL ,
[PeriodID] [smallint] NOT NULL ,
[CompanyID] [smallint] NOT NULL ,
[UnitID] [smallint] NOT NULL ,
[AccountCode] [int] NOT NULL ,
[AccountType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[GLBalance] [money] NOT NULL
) ON [PRIMARY]
GO
File Contents:
2007 210- -0002-3000 G
196395.10

2007 210- -0002-3700 B
1484.00

2007 210- -0002-3700 G
1571.13

2007 210- -0002-3800 B
157457.00

2007 210- -0002-3800 G
161577.73

[Erland Sommarskog]

Jennifer (J.**********@gmail.com) writes:

I'm trying to do an insert using Bulk Insert with a fixed length file.
I'm using a format file.
I'm getting the following error message:
Cannot perform bulk insert. Invalid collation name for source column 16
in format file '\\wbhq.com\dfsdv\iDataInt\GOPFiles\GOPFormatFile. txt'.

I was not able to repeat this. Then again, I was not able to import the
file successfully either, but the error message I got was:

Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 15
(GLBalance).
Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 2, column 15
(GLBalance).

which makes me think that the format file was interpreted OK. (And the
reason for the error is simply that your file was mashed in news transport,
and I did not really make any effort to resurrect it.)

Since column 16 is a column you are not even importing the message seems
funny to me. My prime suspicions that the contents of

'\\wbhq.com\dfsdv\iDataInt\GOPFiles\GOPFormatFile. txt'

is not what you think it is.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx