FORMATFILE help (BULK INSERT)

Spent 2 hours on this and cant for the life of me figure this simple parameter out.

I got a table with 4 columns, and a data file with 3 fields. The first db column has a default value so I want to exclude it from the bulk insert using a format file.

Table columns:

frequency (int) default is 0
mid (int)
score (smallint)
stamp (smalldatetime)

Data file format:

230934,23,2005-04-03

So I want to import the 3 fields as values for mid, score, and stamp… while letting frequency default to 0. I tried several variations of the following:

9.0
3
1 SQLCHAR 0 0 “,” 2 mid SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 0 “,” 3 score SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 “,” 4 stamp SQL_Latin1_General_CP1_CI_AS

I’ve gotten all sorts of error but the above generates:
“Cannot bulk load. Invalid column number in the format file “xxxxxxx.txt””

Please someone advise. Thanks.

what type of sql server are you using?

sorry… im using 2005

i found this page: http://msdn2.microsoft.com/en-us/library/ms191479.aspx

i think you need to replace the collation with “” on the numeric columns?

i’ve checked that page already… but have to admit, im not sure what column collation is.

so i tried your suggestion, but it didn’t work.

Hi, I have this problem, but I’m resolving…

The question is not something about the file; in my case, the problem is the time between the dinamic format-file generation and the execution of the Bulk Insert. Resolution: copy the format-file with another name -fix temporal name- in another path (fix temporal path, of course). Simply, but complicated to find. I have loaded today about 15000 little data-files, each of them with her format-file… without this problem!

Bye.