Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

export all table to excel with header using bcp Expand / Collapse
Author
Message
Posted Wednesday, December 3, 2008 5:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 7, 2009 3:10 AM
Points: 1, 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
Post #612717
Posted Tuesday, May 5, 2009 5:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 8, 2010 5:24 PM
Points: 3, 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

Post #710750
Posted Tuesday, May 5, 2009 5:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 8, 2010 5:24 PM
Points: 3, Visits: 16
post error caused dup response
Post #710751
Posted Tuesday, May 5, 2009 5:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 8, 2010 5:24 PM
Points: 3, Visits: 16
post error caused dup response
Post #710752
Posted Friday, May 21, 2010 10:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, September 26, 2010 10:23 PM
Points: 21, Visits: 44
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.
Post #926370
Posted Friday, May 21, 2010 11:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, September 26, 2010 10:23 PM
Points: 21, Visits: 44
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!
Post #926376
Posted Monday, July 11, 2011 4:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, December 29, 2012 1:46 AM
Points: 23, 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
Post #1139637
Posted Monday, July 11, 2011 7:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 8:17 PM
Points: 12,881, Visits: 31,820
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1139750
Posted Wednesday, July 13, 2011 12:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, December 29, 2012 1:46 AM
Points: 23, 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?????
Post #1140747
Posted Wednesday, July 13, 2011 12:41 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:39 PM
Points: 7,038, Visits: 12,951
"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
Post #1140758
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse