Posted by Jennifer on 01/10/07 22:16

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

[Back to original message]