SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


export all table to excel with header using bcp


export all table to excel with header using bcp

Author
Message
neeraj1401
neeraj1401
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 10
Hi,

I'm tring to export all table to xls file with header , I'm using the following code its working fine for tables who have less columns but i'm geeting some error for large table (having more then 100 columns).please sugesst .............

@columnConvert is taking only 4000 character .........i already defined its length to 8000


use master
go
if object_id('spExportData_n') is not null
drop proc spExportData_n
go
create proc spExportData_n
(
@dbName varchar(100) = 'master',
@sql varchar(8000) = '',
@fullFileName varchar(100) = ''
)
as
if @sql = '' or @fullFileName = ''
begin
select 0 as ReturnValue -- failure
return
end
-- if DB isn't passed in set it to master
select @dbName = 'use ' + @dbName + ';'
if object_id('##TempExportData') is not null
drop table ##TempExportData
if object_id('##TempExportData2') is not null
drop table ##TempExportData2
-- insert data into a global temp table
declare @columnNames varchar(8000), @columnConvert varchar(8000), @tempSQL varchar(8000)
select @tempSQL = left(@sql, charindex('from', @sql)-1) + ' into ##TempExportData ' +
substring(@sql, charindex('from', @sql)-1, len(@sql))
exec(@dbName + @tempSQL)
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
-- build 2 lists
-- 1. column names
-- 2. columns converted to nvarchar
SELECT @columnNames = COALESCE( @columnNames + ',', '') + column_name,
@columnConvert = COALESCE( @columnConvert + ',', '') + 'convert(nvarchar(4000),'
+ column_name + case when data_type in ('datetime', 'smalldatetime') then ',121'
when data_type in ('numeric', 'decimal') then ',128'
when data_type in ('float', 'real', 'money', 'smallmoney') then ',2'
when data_type in ('datetime', 'smalldatetime') then ',120'
else ''
end + ') as ' + column_name
FROM tempdb.INFORMATION_SCHEMA.Columns
WHERE table_name = '##TempExportData'
-- execute select query to insert data and column names into new temp table
SELECT @sql = 'select ' + @columnNames + ' into ##TempExportData2 from (select ' + @columnConvert + ', ''2'' as [temp##SortID] from ##TempExportData union all select ''' + replace(@columnNames, ',', ''', ''') + ''', ''1'') t order by [temp##SortID]'
exec (@sql)
-- build full BCP query
select @sql = 'bcp "' + @dbName + ' select * from ##TempExportData2" queryout "' + @fullFileName + '" -c -t, -T -Sserve -Usa -P'
-- execute BCP
Exec master..xp_cmdshell @sql
--print @sql
if @@error > 0
begin
select 0 as ReturnValue -- failure
return
end
drop table ##TempExportData
drop table ##TempExportData2
set @columnNames =' '
set @columnConvert =' '
set @tempSQL =' '
select 1 as ReturnValue -- success
AronsonA
AronsonA
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 16
I was here looking and working with this. Your problem is that the passed in string was being used within SP, but exceeded its max and ended up with miss formed syntax.
I changed it to (max) and this worked fine. I also had to declare a BCPSQL string as xp_cmdshell does not like (max).
Other than these 2 changes, this works really well to combine Header with Data.

create proc spExportData_n
(
@dbName varchar(100) = 'master',
@sql Nvarchar(max) = '',
@fullFileName varchar(100) = ''
)
as
...declare...
, @tempSQL varchar(8000)
...
Exec master..xp_cmdshell @tempSQL
AronsonA
AronsonA
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 16
post error caused dup response
AronsonA
AronsonA
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 16
post error caused dup response
hugh.hemington
hugh.hemington
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 46
This looks like it might be really useful. Has anyone managed to get it to work? All it does for me is generate a list of errors when I execute the stored procedure.

The errors include: Error = [Microsoft][SQL Native Client]Unable to resolve column level collations
and: BCP copy out failed

I'm assuming the argument @sql is a query string, and @fullFileName is the output file path and name.
For @sql I tried
' select col1, col2, col3 from source_table' Then I tried leaving off 'select' -- both failed.
I tried just the table name, and tried it with 'dbo.' -- nothing works.
I changed the syntax of the BCP to what works for me in other procedures, but can't get this to do anything, but that may be because the rest of the procedure has already failed.
hugh.hemington
hugh.hemington
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 46
I found the SCRIPT - By Samardeep Khera, 2009/10/01
Thanks Samardeep for noodling that one out. I really appreciate the hints. It worked first time out!:-)
deepika_goyal
deepika_goyal
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 117
Hi all.

I am trying to export data from Sql Server table using BCP utillty,
I want to add Column header to my Excel .

Can anyone guide me how i can do this..

Thanks
Deepika Goyal
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28387 Visits: 39963
if you have to do this via bcp and not SSIS, then there are two ways that i can think of.
one exports a global temp table, and the other uses the command line COPY command to append two files together.

--BCP to get headers
--Version 1: global temp table
--not i'm making sure my headcers are large enough for my data i insert later....
--otherwise CITYNAME wouldbe varchar(8), and I need maybe 100
SELECT
identity(int,1,1) AS BCPORDERID,
CONVERT(varchar(100),'CITYTBLKEY') AS CITYTBLKEY,
CONVERT(varchar(100),'CITYNAME') AS CITYNAME,
CONVERT(varchar(100),'COUNTYTBLKEY') AS COUNTYTBLKEY,
CONVERT(varchar(100),'COUNTYNAME') AS COUNTYNAME,
CONVERT(varchar(100),'COUNTYFIPS') AS COUNTYFIPS,
CONVERT(varchar(100),'STATETBLKEY') AS STATETBLKEY,
CONVERT(varchar(100),'STATECODE') AS STATECODE,
CONVERT(varchar(100),'CITYDESCRIP') AS CITYDESCRIP
INTO ##BCP_Results

INSERT INTO ##BCP_Results(CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP)
SELECT
CITYTBLKEY,
CITYNAME,
COUNTYTBLKEY,
COUNTYNAME,
COUNTYFIPS,
STATETBLKEY,
STATECODE,
CITYDESCRIP
FROM VW_CITYCOUNTYSTATE

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP FROM ##BCP_Results ORDER BY BCPORDERID" queryout MyFileName.txt -c -T'

--drop table ##BCP_Results



--Version TWO:
--export two files, then combine them together.

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT ''CITYTBLKEY'',''CITYNAME'',''COUNTYTBLKEY'',''COUNTYNAME'',''COUNTYFIPS'',''STATETBLKEY'',''STATECODE'',''CITYDESCRIP''" queryout MyFileNameT1.txt -c -T'
EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT CITYTBLKEY,CITYNAME,COUNTYTBLKEY,COUNTYNAME,COUNTYFIPS,STATETBLKEY,STATECODE,CITYDESCRIP FROM VW_CITYCOUNTYSTATE" queryout MyFileNameT2.txt -c -T'
set @sql ='copy c:\MyFileNameT.txt + c:\MyFileNameT2.txt c:\MyFileName.txt'
EXEC master..xp_cmdshell @sql



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

deepika_goyal
deepika_goyal
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 117
Thanks for this.. i will try this..
1 More thing:
i have one Sql Table and a temp table..
i want to extract whole data from Sql table to temp table without giving any
field name..
like INSERT INTO ##temp
SELECT * FROM [Table name]
But its not working.
any help?????
LutzM
LutzM
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10391 Visits: 13559
"is not working" is just not enough information...

Would you mind posting the error message?



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search