"TThai" <tpthai@pepco.com> wrote in message
news:7fedd9b2.0410210736.11593f4c@posting.google.com...
> 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.com...
> > 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.com...
> > 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,