BULK INSERT + format file: Skipping a Field??

[LFCfan]

I have a (fixed length) textfile I want to BULK INSERT into a SQL2000 table, using a formatfile.
The table definition is like : and the datafile is like:

MyTable MyFile.txt
----------- --------
column1 int Field1 length = 6
column2 int (computed column) Field2 length = 4
column3 int Field3 length = 6
…etc… …etc…

Because Column2 is computed, I want to ignore the incoming Field2
The format file is like

MyFormatFile.fmt
-----------------
order(file) length delim order(table)
------------- -------- ------- --------------
1 SQLCHAR 0 6 "" 1 column1 Latin1_General_BIN
2 SQLCHAR 0 4 "" 2 column2 Latin1_General_BIN
3 SQLCHAR 0 6 "" 3 column3 Latin1_General_BIN
...etc…

Having read the BOL chapter on format files with fewer/more fields and fields in different order, I have tried all manner of combinations of putting zeroes in the order(table) and length columns of the formatfile. Unfortunately, the BULK INSERT fails at row 1 of the data file because a column further along is picking up NULLs and doesn’t accept them.

(for info, the error is
Server: Msg 4869, Level 16, State 1, Line 1
Bulk Insert failed. Unexpected NULL value in data file row 1, column 26. Destination column (RunStatusClosed) is defined NOT NULL.
)

I know that the data this column is expecting is not null in the datafile, so I’m assuming that, while Field2 is being skipped, the BULK INSERT is ignoring the 4 byte length of it and hence pointing to the wrong field when picking up this alleged NULL value.

I thought the following would be okay, but it still does the same thing

order(file) length delim order(table)
------------- -------- ------- --------------
1 SQLCHAR 0 6 "" 1 column1 Latin1_General_BIN
2 SQLCHAR 0 4 "" 0 column2 Latin1_General_BIN
3 SQLCHAR 0 6 "" 3 column3 Latin1_General_BIN
...etc…


Is skipping a field in this way possible and does anyone know how to get it to work?
Or am I just being stupid? (always a possibility)

Any suggestions would be greatly appreciated

Maria

 

[LFCfan]

Right, I REALLY don't think this is possible after all!
But if anyone would like to prove me wrong, I'd love it if you could!

Thanks

Maria

 

[miq]

hi,

This should work.

1 SQLCHAR 0 6 "" 1 column1 latin...
2 SQLCHAR 0 0 "" 0 column2 latin... 3 SQLCHAR 0 6 "" 3 column3 Latin1_General_BIN
...etc…

The lenght of column should be set to zero.

Or am I just being stupid? (always a possibility) NOT AT ALL, Atleast not this time. LOL



bye
miq

 

[LFCfan]

Thanks miq,

Didn't work though - I did literally try all combinations of setting things to zero! I had to use a staging table in the end anyway, so that was problem solved (just added an extra column into the stg. table)

Thanks again,

Maria