bcp NULL data into table

i'm trying to use bcp to input some data into a table which has columns accepting null values.  i have tried using /c and i've also tried using /f with a format file modified to look like this:

1 SQLCHAR 0 0 "" 0 column1
2 SQLCHAR 0 10 "," 2 column2
3 SQLCHAR 0 0 "" 0 column3
4 SQLCHAR 0 10 "\n" 4 column4

etc. . . and i always get "attempt to bulk copy an oversized row. . ."

i have tried to made my data look like this:
12345467890,1234567890\n

i've tried (with different fmt file)
1234567890,,1234567890\n

1234567890,NULL,1234567890\n
1234567890,(null),1234567890\n

and nothing seems to work.  i have counted and recounted - i have the same number of data columns in my text file  as are on the server, and the NULL values are allowed. . .

what's going on?
LVL 2
Billy_PilgrimAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustavo Perez BuenrostroCommented:
Use -c argument of BCP utility. Your data must look as follows:

1234567890->->1234567890->
1234567890->->1234567890->

PD: "->" means tab ASCII character.

If you need a better approach post your table definition and some data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
simonsabinCommented:
If your data is in the format

1234567890,,1234567890
1234567890,,1234567890
1234567890,,1234567890

then using
BCP database..table in file /c /t, /r\n /SServer /UUser /PPassword /ddatabase


This will put NULLs in second column
 
0
Billy_PilgrimAuthor Commented:
i'm going to accept your answer, because you were right--

but my real problem was that i was creating my output file in C and i had fopen()'d the file in "wb" or binary mode by mistake, which left off the \r in front of \n. . .

and even though i explicitly told bcp /r\n it looked for /r\r\n. . .
0
Gustavo Perez BuenrostroCommented:
If you need something else, please give me more information and let me help you.

Don't award me points if I don't deserve them.
0
Billy_PilgrimAuthor Commented:
no - you had a fine answer.  and a correct one.  i just don't like rejecting a valid answer.  if it was incorrect i wouldn't have awarded. . .

i fixed my problem by fopen()ing the file in "wt" mode, which gave me the \r\n i needed instead of just the \n at the end of each record. . .

so thank you and for people in the future needing to know how to bcp NULL columns your answer will help them. . .
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.