BCP uploads of data with missing values

I have two problems with the BCP utility:
1. I have a data file that sometimes does not have a value for some of the
fields. I am looking at uploading that data into SQL Server 2000 using BCP.
If it happens to be the last column in the row that doesn't have any data,
BCP complains about that line with the following error:
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
If I add spaces at the end of the line to match the line length with the
definition file that I use to import the data, the error goes away. What ca
n
I do to keep this error from being recorded? BCP quits after a preset numbe
r
of errors, unless I was to add the -m maxerrors parameter. I'd rather not d
o
that as I could miss "real" errors.
2. I want nulls to be inserted into the columns where there is nothing
specified in the data file. Because the data file is in a fixed width
format, I suspect BCP thinks that the value for that type of column is a
bunch of spaces. How can I force BCP to insert nulls instead?
Thanks,
FrancoisFrancois,
I'm don't know if this can be done with BCP, however the last
time I checked, the Jet driver handled ragged-right source data
without raising errors. Jet may not have the best reputation around,
but it may help you out here.
See http://groups.google.co.uk/groups?q...+kass+sqlserver
for details.
Steve Kass
Drew University
Francois wrote:

>I have two problems with the BCP utility:
>1. I have a data file that sometimes does not have a value for some of the
>fields. I am looking at uploading that data into SQL Server 2000 using BCP
.
>If it happens to be the last column in the row that doesn't have any data,
>BCP complains about that line with the following error:
>SQLState = 22001, NativeError = 0
>Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
>If I add spaces at the end of the line to match the line length with the
>definition file that I use to import the data, the error goes away. What c
an
>I do to keep this error from being recorded? BCP quits after a preset numb
er
>of errors, unless I was to add the -m maxerrors parameter. I'd rather not
do
>that as I could miss "real" errors.
>2. I want nulls to be inserted into the columns where there is nothing
>specified in the data file. Because the data file is in a fixed width
>format, I suspect BCP thinks that the value for that type of column is a
>bunch of spaces. How can I force BCP to insert nulls instead?
>Thanks,
>Francois
>