export all table to excel with header using bcp

  • 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 error caused dup response

  • 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 error caused dup response

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

  • 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!:-)

  • 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

  • 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!

  • 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?????

  • "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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Hi,

    This is an Error

    Msg 213, Level 16, State 1, Line 5

    Insert Error: Column name or number of supplied values does not match table definition.

    I want to copy all the rows from Sql table(table contains 50 fields) to Temp table and then extracting the records to Execl through BCP..

    Extractiong data to EXcel i will do..but how to copy data from 1 table to another without giving any fileld name ?? i dnt know.

    Thanks a lot ..if i get some solution.

    Its urgent

    Deepika

  • the error's pretty clear. the temp table you are inserting into doesn't match your select *.

    either create a table explicitly for your select *, or you have to identify the columns.

    are you reusing that ##global table for a second table?

    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!

  • SELECT *

    INTO ##temp

    FROM [Table name]

    _____________
    Code for TallyGenerator

  • Hi..

    Its' Working

    Thanks..

  • This script rocks, thanks to the blogger

    <a href="http://dotnetkeeda.blogspot.in/2009/09/bcp-command-to-export-data-to-excel.html"> BCP export to excel</a>

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply