BCP column insert question

HI,
I'm trying to insert records to a table using bcp command. The
problem is the input file to the bcp is a text file that looks like
this:

Text file data:
1234 abc def ghi jkl mno

Expected result:
column1 1234
column2 abc def ghi jkl mno
column3 null
column4 N

My table has four columns (column1, column2, column3, column4). I
would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
column3 is blank and column4 is always 'N' as shown above. I setup the
column4 to be defaulted to 'N'. However, I'm getting an error
regarding string truncation because, I think, SQL server is trying to
insert the 'abc def ghi jkl mno' into different columns. So the
question is what can I do to tell the bcp utility that 'abc def ghi
jkl mno' belongs to column2?

Appreciated any help.

Thanks,
TeresaTeresa,

Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
because then if there is a comma in the data it won't hose your bulk insert.

Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
should map correctly once it is delimited.

Here is an example from the Transact SQL online:

BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
)
Hope this helps!

Barry

"TThai" <tpthai@.pepco.com> wrote in message
news:7fedd9b2.0410210736.11593f4c@.posting.google.c om...
> HI,
> I'm trying to insert records to a table using bcp command. The
> problem is the input file to the bcp is a text file that looks like
> this:
> Text file data:
> 1234 abc def ghi jkl mno
> Expected result:
> column1 1234
> column2 abc def ghi jkl mno
> column3 null
> column4 N
> My table has four columns (column1, column2, column3, column4). I
> would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
> column3 is blank and column4 is always 'N' as shown above. I setup the
> column4 to be defaulted to 'N'. However, I'm getting an error
> regarding string truncation because, I think, SQL server is trying to
> insert the 'abc def ghi jkl mno' into different columns. So the
> question is what can I do to tell the bcp utility that 'abc def ghi
> jkl mno' belongs to column2?
> Appreciated any help.
> Thanks,
> Teresa|||"Barry Young" <youngbar@.insightbb.com> wrote in message news:<gY_dd.226559$wV.94703@.attbi_s54>...
> Teresa,
> Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
> because then if there is a comma in the data it won't hose your bulk insert.
> Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
> should map correctly once it is delimited.
> Here is an example from the Transact SQL online:
> BULK INSERT Northwind.dbo.[Order Details]
> FROM 'f:\orders\lineitem.tbl'
> WITH
> (
> FIELDTERMINATOR = '|',
> )
> Hope this helps!
> Barry
> "TThai" <tpthai@.pepco.com> wrote in message
> news:7fedd9b2.0410210736.11593f4c@.posting.google.c om...
> > HI,
> > I'm trying to insert records to a table using bcp command. The
> > problem is the input file to the bcp is a text file that looks like
> > this:
> > Text file data:
> > 1234 abc def ghi jkl mno
> > Expected result:
> > column1 1234
> > column2 abc def ghi jkl mno
> > column3 null
> > column4 N
> > My table has four columns (column1, column2, column3, column4). I
> > would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
> > column3 is blank and column4 is always 'N' as shown above. I setup the
> > column4 to be defaulted to 'N'. However, I'm getting an error
> > regarding string truncation because, I think, SQL server is trying to
> > insert the 'abc def ghi jkl mno' into different columns. So the
> > question is what can I do to tell the bcp utility that 'abc def ghi
> > jkl mno' belongs to column2?
> > Appreciated any help.
> > Thanks,
> > Teresa

Hi Barry,
Appreciated your response. I'll try it and keep you posted. Have a good day.

Thanks,
Teresa|||"Barry Young" <youngbar@.insightbb.com> wrote in message news:<gY_dd.226559$wV.94703@.attbi_s54>...
> Teresa,
> Try delimiting your data with a , (comma) or (pipe) |. I use the pipe
> because then if there is a comma in the data it won't hose your bulk insert.
> Then you can use the WITH FIELDTERMINATOR = "|" in your Bulk Insert. It
> should map correctly once it is delimited.
> Here is an example from the Transact SQL online:
> BULK INSERT Northwind.dbo.[Order Details]
> FROM 'f:\orders\lineitem.tbl'
> WITH
> (
> FIELDTERMINATOR = '|',
> )
> Hope this helps!
Hi Barry,
I just tried and am getting an error 'Server: Msg 4860, Level 16,
State 1, Line 1
Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
there any preliminary setup that I have to do to recognize the file?
Here is what I executed in SQL analyzer.

BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
FROM 'C:\transactions.txt'
with
(FIELDTERMINATOR = '|')

Thanks,
Teresa

> Barry
> "TThai" <tpthai@.pepco.com> wrote in message
> news:7fedd9b2.0410210736.11593f4c@.posting.google.c om...
> > HI,
> > I'm trying to insert records to a table using bcp command. The
> > problem is the input file to the bcp is a text file that looks like
> > this:
> > Text file data:
> > 1234 abc def ghi jkl mno
> > Expected result:
> > column1 1234
> > column2 abc def ghi jkl mno
> > column3 null
> > column4 N
> > My table has four columns (column1, column2, column3, column4). I
> > would like 1234 to go to column1, 'abc def ghi jkl mno' go to column2,
> > column3 is blank and column4 is always 'N' as shown above. I setup the
> > column4 to be defaulted to 'N'. However, I'm getting an error
> > regarding string truncation because, I think, SQL server is trying to
> > insert the 'abc def ghi jkl mno' into different columns. So the
> > question is what can I do to tell the bcp utility that 'abc def ghi
> > jkl mno' belongs to column2?
> > Appreciated any help.
> > Thanks,
> > Teresa|||TThai (tpthai@.pepco.com) writes:
> Hi Barry,
> I just tried and am getting an error 'Server: Msg 4860, Level 16,
> State 1, Line 1
> Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
> there any preliminary setup that I have to do to recognize the file?
> Here is what I executed in SQL analyzer.
> BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
> FROM 'C:\transactions.txt'
> with
> (FIELDTERMINATOR = '|')

BULK INSERT operates on the server, so it is looking a C:\ at your server.
If your file is on a client machine, you are better off with BCP. You
can specify field terminator with the -t options. Since | is a meta-
character for the command shell, you need to quote it:

bcp db..tbl in yourfile.txt -c -t "|" -S ...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns958BF277E1611Yazorman@.127.0.0.1>...
> TThai (tpthai@.pepco.com) writes:
> > Hi Barry,
> > I just tried and am getting an error 'Server: Msg 4860, Level 16,
> > State 1, Line 1
> > Could not bulk insert. File 'C:\transactions.txt' does not exist.' Is
> > there any preliminary setup that I have to do to recognize the file?
> > Here is what I executed in SQL analyzer.
> > BULK INSERT xxx_TEST.DBO.TRANSACTION_CORRECTION
> > FROM 'C:\transactions.txt'
> > with
> > (FIELDTERMINATOR = '|')
> BULK INSERT operates on the server, so it is looking a C:\ at your server.
> If your file is on a client machine, you are better off with BCP. You
> can specify field terminator with the -t options. Since | is a meta-
> character for the command shell, you need to quote it:
> bcp db..tbl in yourfile.txt -c -t "|" -S ...

Thank you very much. It worked!

Teresa