How do you tell which field SQL 2k5 BCP is complaining about in a failed import?

I am getting the error Error - [Microsoft][SQL Native Client] String data, right truncation about 10 times before the import fails out on the outpu log. The error is a bit ambigous to which field in which it is related. Normaly this wouldnt' be a problem if the dataset was in truly in 3rd normal form. 开发者_如何学Python However, the dataset has over 125+ fields with over 100,000 records as a tab delimited txt file. In this case, it will be quite an undertaking to find the offending data. How do you tell which field SQL 2k5 BCP is complaining about in a failed import? If I could find out which field is erroring out, this will greatly help narrow down the problem.

bcp db.dbo.table in "dataset.txt" -f "fmt.table.txt" -S instancename -U user -P password -o log.txt

Yes, theres a bigger story, but am limmiting it to this specific problem for simplicity sake. Otherwise going further will require another question to be asked. :)

---

Add an empty collation to the format file. i.e.

112 SQLCHAR 0 1 "\t" 112 CREDITREPORTADDRINDICATOR   ""

I import files with nothing between the row terminators to get null. Make sure consecutive tabs are included.

---

The problem was found on the 112th field where the field was defined as char(1) and where the data that was extracted was literally "" giving a length of 2 all the way down each record. I did this by importing the data as tab-delimited file into Excel setting each field as text (crucial given that Excel automatically converts date fields to a numeric value) and set the text qualifier from " to none (another conversion feature Excel does) and set it to sheet 1. I check the field length of every datum in the dataset on the second sheet using =len(Sheet1!A1), =len(Sheet1!A2), =len(Sheet1!A3), etc (thank god for auto fill) and pasted the shema information on the third sheet to compare the max length of each field =max(Sheet2!A1:A126324). The base problem lies in the way the data was extracted.

This is how I did it, but I get the feeling this is far from a good way of doing it....