BCP - 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

Transact-SQL (2000)

BCP

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-15 : 09:56:09

you can specify quoted fields( only char fields) in BCP?

Basically, I have to generate a text file looks like:

"Detail","SSS-A01 ","1110","A01","06-26","06/30/2006","N",265727,0,"Revenue"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 10:26:36

Yes, if you use a format file.

Peter Larsson
Helsingborg, Sweden

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-15 : 10:30:42

thanks... can you show me how to create a format file? or

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-15 : 11:15:48

see
http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

For your requirement it woul dbe easier to create the structure ion an SP and bcp it using queryout.

create s_FormatSP
as
select '"DETAIL"'
+ ',"' + coalesce(col1,'') + '"'
+ ',"' + coalesce(convert(varchar(20),col2),'') + '"'
+ ....
from tbl
go

exec master..xp_cmdshell 'bcp "exec mydb..s_FormatSP" queryout c:\myfile.txt -c'

==========================================
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.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-15 : 11:26:54

thank you

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-15 : 12:00:36

nr, what is the reson to use coalesce in your solution? can you explain to me?

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-08-15 : 13:24:15

Coalesce() is to handle NULL values. The way Nigel used it is to use the field contents if the field is not null, or an empty string if it is, surrounded by double quotes.

Ken

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-15 : 15:45:40

If one of the values s null then as we are concatenating strings it would end up with a null string which would be bcp'd out as a blank line.

==========================================
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.

jung1975
Aged Yak Warrior

503 Posts

Posted - 2006-08-15 : 16:12:57

thansk,
nr
here is the code(I've modified your code little bit to generate 20000 records each based on the unit_abbr ) to generate the text files,but it looks like it runs forever.. #a table has only about 80000 records..not sure why it takes forever... I checked the CPU time and it's a big number 2450141. I have an index on unit_abbr column in the GL_Export table.

Do you have any ideas why this is taking such a long time even though there is only 80000 records to process?

--Here is the code

select * from #a

select unit_abbr, num = count(*)
into #a
from GL_Export
group by unit_abbr
order by unit_abbr

declare @sql varchar(8000)
declare @unit varchar(10), @lastunit varchar(10), @num int, @nextunit varchar(10)
select @lastunit = ''
while @lastunit < (select max(unit_abbr) from #a)
begin
select @unit = min(unit_abbr) from #a where unit_abbr > @lastunit
select @num = num from #a where unit_abbr = @unit
select @nextunit = @unit, @lastunit = @unit
while @num < 20000
begin
select @lastunit = @nextunit
select @nextunit = unit_abbr, @num = @num + num from (select top 1 * from #a where unit_abbr > @nextunit order by unit_abbr) a
end
end
begin

select @sql = 'bcp "
select ''Batch'','''',''IC'',''PR'',''N''
union all
select ''\"''+DETAIL+''\"'''
+ ', ''\"''+coalesce(ltrim(rtrim(unit_abbr)),'''') + ''\"'''
+ ', ''\"''+coalesce(ltrim(rtrim(transaction_gl_code)),'''') + ''\"'''
+ ', coalesce(ltrim(rtrim(replace( convert(numeric(18,2),debit ), ''.00'', ''''))),'''')'
+ ', coalesce(ltrim(rtrim(replace( convert(numeric(18,2),credit ), ''.00'', ''''))),'''')'
+ ' from GL_Export unit between ''' + @unit + ''' and ''' + @lastunit + '''" queryout c:\GL_Export\' + @unit + '.txt -c -S TESTBOX -Usa -P sasa '

--select @sql
exec master..xp_cmdshell @sql

end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources