May 25, 2004 at 9:26 am
Hi,
I am trying to bcp in data, but the data we bcp out from some other system seems to have some problem. Please see the complete script to reproduce the error i am getting. Any Help is greatly appreciated.
/*Error Msg:
Server: Msg 4827, Level 16, State 1, Line 1
Could not bulk insert. Invalid column terminator for column number 1 in format file 'C:\Test.fmt'.
*/
Create Table TempOut(col1 VARCHAR(500), col2 int, col3 int)
go
INSERT INTO TempOut(col1, col2, col3) Values(' Account Id: 00000 Page: 1 of 1 ',1,11)
INSERT INTO TempOut(col1, col2, col3) Values(' ',1,12)
INSERT INTO TempOut(col1, col2, col3) Values(' ',1,13)
INSERT INTO TempOut(col1, col2, col3) Values(' ',1,14)
INSERT INTO TempOut(col1, col2, col3) Values(' SUE TEMPLETON Invoice Number: 00000',1,15)
INSERT INTO TempOut(col1, col2, col3) Values(' 1111 LEMANS Invoice Date: 10/10/97 ',1,16)
INSERT INTO TempOut(col1, col2, col3) Values(' CARROLLTON, NC 75006 ',1,17)
INSERT INTO TempOut(col1, col2, col3) Values(' United States ',1,18)
INSERT INTO TempOut(col1, col2, col3) Values(' ',1,19)
go
select * from TempOut
go
exec master..xp_cmdshell 'bcp DBName.dbo.tempout OUT C:\tempout.txt -c -q -t"~~" /S(servername)'
--select @@servername
go
Create Table TempIn(col1 VARCHAR(500), col2 int, col3 int)
go
BULK INSERT dbo.TempIn FROM 'C:\tempout.txt'
WITH (FORMATFILE='C:\Test.fmt')
/*
Test.Fmt
7.0
3
1 SQLCHAR 0 255 '~~' 1 col1
2 SQLCHAR 0 4 '~~' 2 col2
3 SQLCHAR 0 4 '\n' 3 col3
*/
May 26, 2004 at 3:19 am
Doesn't like single quotes around the terminator, use double quotes (").
Chris.
May 26, 2004 at 4:25 pm
you are right, " worked
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply