bcp no terminator - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

bcp no terminator

Author

Topic

bb
Starting Member

2 Posts

Posted - 2004-07-04 : 16:12:24

i want to import multiple text files in 1 table. i want that the whole text of 1 file is inserted in 1 cell (so no row/fieldterminator)

i already tried

-T -c -r\0 -t\0

but that doesn't work.

i also tried 'false' terminators

-T -c -r "xxrt" -t "xfdr"

but that doesn't work either.

can anyone help me?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-07-04 : 16:38:24

Should work.
Are you sure you're not being confused by the crlf in the imported data? It will appear on multple lines in query analyser and look like seperate rows. Look at the rowcount.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-07-04 : 16:58:54

Or are you inserting into a text column?
if so use bulk insert
bulk insert ##a from 'c:\bcp.txt' with (fieldterminator='\0',rowterminator='\0')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bb
Starting Member

2 Posts

Posted - 2004-07-05 : 14:30:09

indeed, i'm inserting into a text column. i use following stored procedure (i found in on the internet) to import all the files in a directory in a table with 1 column (ntext-16).

CREATE procedure usp_ImportMultipleFilesBCP @servername varchar(128),
@DatabaseName varchar(128), @filepath varchar(500), @pattern varchar(100),
@TableName varchar(128)
as
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
--select * from #y
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @Query ='bcp "'+ @databasename+'.dbo.'+@Tablename + '" in "'+ @Filepath+@Filename+'" -S' + @servername + ' -T -c -r\0 -t\0'
set @Query = 'MASTER.DBO.xp_cmdshell '+ "'"+ @query +"'"
--print @query
EXEC ( @query)
insert into logtable (query) select @query
end
drop table #y
GO

i execute the procedure with:

Exec usp_ImportMultipleFilesBCP 'SQL','DB','c:\Myimport\','*.*','Table'

if you say i have to use:

bulk insert ##a from 'c:\bcp.txt' with (fieldterminator='\0',rowterminator='\0')

which code has to be in "bcp.txt"?

or could you tell me how to adjust the code i use? i'm new to these things

barbara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources