none
bcp Export to CSV with headers

    Question

  •  

    I’m trying to find a way to export all data from a table in to a csv file with a header. I’m not concerned at this point with dynamic headers (that would be nice but at this point I’m not picky). I would just like to find a way to programmatically drop a table to a csv file.

    use tempdb

    go

    create view vw_csvexport

    as   

    select  

    '"' + convert(varchar(50), Name) + '"',

    '"' + convert(varchar(50), ID) + '"'

    from TestHeaderTable

    union all

    SELECT top 100 percent

    Name = '"' + name + '"',

    ID = '"' + convert(varchar(50), ID) + '"'

     

    FROM TestDataTable

    go

     

    DECLARE @bcpCommand varchar(2000)

     

    SET @bcpCommand = 'bcp tempdb..vw_export out

    c:\test002.txt -c -T -S SERVER'

     

    EXEC master..xp_cmdshell @bcpCommand

     

    I get this error when I run this query

    Create View or Function failed because no column name was specified for column 1.

     

    If I’m going about this the completely wrong direction let me know so I can get heading the right direction to get this done.

    Thursday, March 29, 2007 8:32 PM

Answers

  • I was seeing some weird results with bcp, so ignore my solution above.

     

    What I did instead was take advantage of the -r argument of bcp (-r specifies the row delimiter):

    BCP "select column_name from mydb.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='mytable'" queryout c:\temp1.csv -c -r, -T -Smyserver

     

    So this creates temp1.csv like "colname1,colname2,..." There will be an extra comma at the end, so it's not perfect.

     

    After this, I create another file that has a single new line and another file with the actual data.  Then I copy them together (e.g. copy /b file1.csv+file2.csv+file3.csv output.csv), then delete the files.

     

    Not exactly elegant, but it is pretty straightforward.

     

    Thursday, June 12, 2008 7:58 PM
  • While the Import/Export Wizard is nice, it is also an example of manual labor. In Express versions of SQL Server such created packages cannot be saved and automated. Therefore I wrote a simple BAT-script that takes any query and puts the results in a file, with a header row containing the column names. Feel free to use and modify as you like (no rights reserved).

    @ECHO OFF
    REM -------------------------------------------------------------------------------
    REM Generic script for exporting data to file from SQL Server using a SQL query.
    REM The resulting file will be tab separated with newline as the row delimiter.
    REM A log file is generated and kept in case of an error or when in debug mode.
    REM See command syntax for details.
    REM
    REM History:
    REM 20120327	Lars Rönnbäck	CREATED
    REM -------------------------------------------------------------------------------
    :constants
    SET myCodePage=ACP
    :variables
    SET theQuery=%~1
    SET theFile=%~2
    SET theServer=%~3
    SET theDebug=%~4
    SET /a aRandomNumber=%random%%%1000
    FOR /F "usebackq tokens=1-7* delims=.:/,- " %%a IN (`ECHO %DATE%_%TIME%`) DO (SET myStartTime=%%a%%b%%c%%d%%e%%f%%g)
    SET myColumnQuery="select top 0 * into [#columns_%myStartTime%_%aRandomNumber%] from (%theQuery%) q; select stuff((select char(9) + c.name from tempdb.sys.columns c where c.object_id = t.object_id order by c.column_id for XML path(''), type).value('.', 'varchar(max)'), 1,1,'') AS Header from tempdb.sys.tables t where t.name like '#columns_%myStartTime%_%aRandomNumber%%%'"
    SET myHeaderFile=%theFile%.%aRandomNumber%.header
    SET myDataFile=%theFile%.%aRandomNumber%.data
    SET myLogFile=%theFile%.%myStartTime%_%aRandomNumber%.log
    :checks
    IF "%theQuery%"=="" (
        GOTO syntax
    ) 
    IF "%theFile%"=="" (
        GOTO syntax
    ) 
    IF "%theServer%"=="" ( 
        SET theServer=%COMPUTERNAME%
    )
    :information
    ECHO Start Time:    %myStartTime% 		>> "%myLogFile%" 2>&1
    ECHO Random Number: %aRandomNumber% 	>> "%myLogFile%" 2>&1
    ECHO File:          %theFile% 			>> "%myLogFile%" 2>&1
    ECHO Server Name:   %theServer%			>> "%myLogFile%" 2>&1
    ECHO Query:								>> "%myLogFile%" 2>&1
    ECHO.									>> "%myLogFile%" 2>&1
    ECHO %theQuery% 						>> "%myLogFile%" 2>&1
    :export
    BCP %myColumnQuery% queryout "%myHeaderFile%" -T -S "%theServer%" -a 65535 -c -C %myCodePage% -q >> "%myLogFile%" 2>&1
    IF ERRORLEVEL 1 GOTO error
    BCP "%theQuery%" queryout "%myDataFile%" -T -S "%theServer%" -a 65535 -c -C %myCodePage% -q >> "%myLogFile%" 2>&1
    IF ERRORLEVEL 1 GOTO error
    ECHO. 					>> "%myLogFile%" 2>&1
    ECHO Merging files... 	>> "%myLogFile%" 2>&1
    ECHO. 					>> "%myLogFile%" 2>&1
    COPY /A "%myHeaderFile%" + "%myDataFile%" "%theFile%" /B /Y >> "%myLogFile%" 2>&1
    IF ERRORLEVEL 1 GOTO error
    :cleanup
    DEL "%myHeaderFile%" >NUL 2>&1
    IF ERRORLEVEL 1 GOTO error
    DEL "%myDataFile%" >NUL 2>&1
    IF ERRORLEVEL 1 GOTO error
    IF /I NOT [%theDebug%]==[Y] (
        DEL "%myLogFile%"
    )
    IF ERRORLEVEL 1 GOTO error
    GOTO end
    :error
    ECHO 
    ECHO ERROR: An export error has occured!
    IF NOT [%myLogFile: =%]==[] (
      ECHO Details can be found in:
      ECHO %myLogFile%
    )
    ECHO 
    EXIT /B 1
    :syntax
    ECHO.
    ECHO SYNTAX: %0 "sql query" "output file" [server] [Y]
    ECHO -------------------------------------------------------------------------------
    ECHO You must specify an SQL query and an output file name in which the results of  
    ECHO the query will be stored. Specifying a server is optional and defaults to the  
    ECHO server you are executing on. If a fourth argument is given as Y a log file of
    ECHO the command outputs will be saved in the same folder as the output file.
    ECHO -------------------------------------------------------------------------------
    :end
    REM This is the end.
    EDIT: Fix for US date format.

    Lars Rönnbäck -- http://www.anchormodeling.com -- An Agile Modeling Technique for Evolving Information

    Wednesday, March 28, 2012 9:13 AM
  • Thanks jerichar99. I've adapted this solution to fix the extra comma at the end problem:

    For more info see http://stackoverflow.com/questions/1355876/export-table-to-file-with-column-headers-column-names-using-the-bcp-utility-an

    BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from my_db_name.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='my_table_name'; select @colnames;" queryout HeadersOnly.csv -c -T -Smy_server_name
    
    BCP my_db_name.dbo.my_table_name out TableDataWithoutHeaders.csv -c -t, -T -Smy_server_name
    
    copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv
    
    del HeadersOnly.csv
    del TableDataWithoutHeaders.csv

    Saturday, March 17, 2012 11:10 PM
  • Consider using the SSIS Import/Export Wizard, header line is just a checkmark option:

    http://www.sqlusa.com/bestpractices/ssis-wizard/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Sunday, March 18, 2012 6:51 AM
    Moderator

All replies

  • Try the following...

     

    xp_cmdshell 'osql /S<ServerName> /E /Q"set nocount on select top 10  Convert(Varchar(50),name) as name, id from sysobjects" /oc:\test.txt'

     

    Friday, March 30, 2007 4:39 AM
  • Hi,

    For your view try this and you will be alright:

    create view vw_csvexport

    as   

    select  

    '"' + convert(varchar(50), Name) + '"' as vwNAME,

    '"' + convert(varchar(50), ID) + '"' as vwID

    from TestHeaderTable

    union all

    SELECT top 100 percent

    Name = '"' + name + '"',

    ID = '"' + convert(varchar(50), ID) + '"'

     

    FROM TestDataTable

    go


    Actually in the view your returning table should always have specific column name, and since you had not specify any, you had the error.


    Cheers


    Sunday, April 08, 2007 9:53 AM
  •  

    I would also like to create a csv, but with headers and footers that are dynamic. 

    The headers must look like:

     

    Filename (21 characters alphanumeric)

    Filetype (6 characters alphanumeric)

    Filedate (8 characters numeric -- ex 05252008)

     

    query data ....

    ...

    ...

     

    Footer:

    Filename (21 characters alphanumeric)

    Filetype (6 characters alphanumeric)

    Filedate (8 characters numeric -- ex 05252008)

    TotalRecords (9 characters numeric)

     

    The file date and the total number of records lines are dynamic.  Please help somebody!

     

    Friday, May 02, 2008 5:35 PM
  • I didn't see any good solutions to this on the web, so I thought I would post my solution.

     

     

    create PROCEDURE [dbo].[sp_getcolumnsascolumns]

    @table_name as varchar(50)

    as

    declare @temp varchar(1000)

    select @temp=(case when @temp is null then '' else @temp+',' end)+''''+column_name+'''' from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@table_name

    exec('select '+@temp)

     

    Since the information_schema view contains the column names (as rows), it simply a matter of transposing the results (from rows to columns).  So the proc simply populates @temp with a dynamic sql select statement, something like "select 'colname1','colname2',..." (the case statement is for the beginning comma)

     

    Thus, if you do something like

    exec sp_getcolumnsascolumns 'vw_programconfig'

    it will return one row, where each column is the row name of vw_programconfig

     

    I'm not 100% of the best solution from there, but I decided to bcp the data to fileA, the headers to fileB, and copy them together to fileC, which will be the final csv file.  I did this for two reasons:

    1) you cannot union two tables together that don't have matching data types (the headers will be chars, and the table you select may not contain all chars)

    2) regardless, there is no guarantee the header will show up at the top of the file, unless you do some sort of order by

     

    -Jamie

    Wednesday, June 11, 2008 1:01 AM
  • I was seeing some weird results with bcp, so ignore my solution above.

     

    What I did instead was take advantage of the -r argument of bcp (-r specifies the row delimiter):

    BCP "select column_name from mydb.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='mytable'" queryout c:\temp1.csv -c -r, -T -Smyserver

     

    So this creates temp1.csv like "colname1,colname2,..." There will be an extra comma at the end, so it's not perfect.

     

    After this, I create another file that has a single new line and another file with the actual data.  Then I copy them together (e.g. copy /b file1.csv+file2.csv+file3.csv output.csv), then delete the files.

     

    Not exactly elegant, but it is pretty straightforward.

     

    Thursday, June 12, 2008 7:58 PM
  • Thanks.  Nice, simple solution that offers what Microsoft should have provided in the first place.
    Thursday, September 30, 2010 6:44 PM
  • Thanks jerichar99. I've adapted this solution to fix the extra comma at the end problem:

    For more info see http://stackoverflow.com/questions/1355876/export-table-to-file-with-column-headers-column-names-using-the-bcp-utility-an

    BCP "DECLARE @colnames VARCHAR(max);SELECT @colnames = COALESCE(@colnames + ',', '') + column_name from my_db_name.INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='my_table_name'; select @colnames;" queryout HeadersOnly.csv -c -T -Smy_server_name
    
    BCP my_db_name.dbo.my_table_name out TableDataWithoutHeaders.csv -c -t, -T -Smy_server_name
    
    copy /b HeadersOnly.csv+TableDataWithoutHeaders.csv TableData.csv
    
    del HeadersOnly.csv
    del TableDataWithoutHeaders.csv

    Saturday, March 17, 2012 11:10 PM
  • Consider using the SSIS Import/Export Wizard, header line is just a checkmark option:

    http://www.sqlusa.com/bestpractices/ssis-wizard/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Sunday, March 18, 2012 6:51 AM
    Moderator
  • While the Import/Export Wizard is nice, it is also an example of manual labor. In Express versions of SQL Server such created packages cannot be saved and automated. Therefore I wrote a simple BAT-script that takes any query and puts the results in a file, with a header row containing the column names. Feel free to use and modify as you like (no rights reserved).

    @ECHO OFF
    REM -------------------------------------------------------------------------------
    REM Generic script for exporting data to file from SQL Server using a SQL query.
    REM The resulting file will be tab separated with newline as the row delimiter.
    REM A log file is generated and kept in case of an error or when in debug mode.
    REM See command syntax for details.
    REM
    REM History:
    REM 20120327	Lars Rönnbäck	CREATED
    REM -------------------------------------------------------------------------------
    :constants
    SET myCodePage=ACP
    :variables
    SET theQuery=%~1
    SET theFile=%~2
    SET theServer=%~3
    SET theDebug=%~4
    SET /a aRandomNumber=%random%%%1000
    FOR /F "usebackq tokens=1-7* delims=.:/,- " %%a IN (`ECHO %DATE%_%TIME%`) DO (SET myStartTime=%%a%%b%%c%%d%%e%%f%%g)
    SET myColumnQuery="select top 0 * into [#columns_%myStartTime%_%aRandomNumber%] from (%theQuery%) q; select stuff((select char(9) + c.name from tempdb.sys.columns c where c.object_id = t.object_id order by c.column_id for XML path(''), type).value('.', 'varchar(max)'), 1,1,'') AS Header from tempdb.sys.tables t where t.name like '#columns_%myStartTime%_%aRandomNumber%%%'"
    SET myHeaderFile=%theFile%.%aRandomNumber%.header
    SET myDataFile=%theFile%.%aRandomNumber%.data
    SET myLogFile=%theFile%.%myStartTime%_%aRandomNumber%.log
    :checks
    IF "%theQuery%"=="" (
        GOTO syntax
    ) 
    IF "%theFile%"=="" (
        GOTO syntax
    ) 
    IF "%theServer%"=="" ( 
        SET theServer=%COMPUTERNAME%
    )
    :information
    ECHO Start Time:    %myStartTime% 		>> "%myLogFile%" 2>&1
    ECHO Random Number: %aRandomNumber% 	>> "%myLogFile%" 2>&1
    ECHO File:          %theFile% 			>> "%myLogFile%" 2>&1
    ECHO Server Name:   %theServer%			>> "%myLogFile%" 2>&1
    ECHO Query:								>> "%myLogFile%" 2>&1
    ECHO.									>> "%myLogFile%" 2>&1
    ECHO %theQuery% 						>> "%myLogFile%" 2>&1
    :export
    BCP %myColumnQuery% queryout "%myHeaderFile%" -T -S "%theServer%" -a 65535 -c -C %myCodePage% -q >> "%myLogFile%" 2>&1
    IF ERRORLEVEL 1 GOTO error
    BCP "%theQuery%" queryout "%myDataFile%" -T -S "%theServer%" -a 65535 -c -C %myCodePage% -q >> "%myLogFile%" 2>&1
    IF ERRORLEVEL 1 GOTO error
    ECHO. 					>> "%myLogFile%" 2>&1
    ECHO Merging files... 	>> "%myLogFile%" 2>&1
    ECHO. 					>> "%myLogFile%" 2>&1
    COPY /A "%myHeaderFile%" + "%myDataFile%" "%theFile%" /B /Y >> "%myLogFile%" 2>&1
    IF ERRORLEVEL 1 GOTO error
    :cleanup
    DEL "%myHeaderFile%" >NUL 2>&1
    IF ERRORLEVEL 1 GOTO error
    DEL "%myDataFile%" >NUL 2>&1
    IF ERRORLEVEL 1 GOTO error
    IF /I NOT [%theDebug%]==[Y] (
        DEL "%myLogFile%"
    )
    IF ERRORLEVEL 1 GOTO error
    GOTO end
    :error
    ECHO 
    ECHO ERROR: An export error has occured!
    IF NOT [%myLogFile: =%]==[] (
      ECHO Details can be found in:
      ECHO %myLogFile%
    )
    ECHO 
    EXIT /B 1
    :syntax
    ECHO.
    ECHO SYNTAX: %0 "sql query" "output file" [server] [Y]
    ECHO -------------------------------------------------------------------------------
    ECHO You must specify an SQL query and an output file name in which the results of  
    ECHO the query will be stored. Specifying a server is optional and defaults to the  
    ECHO server you are executing on. If a fourth argument is given as Y a log file of
    ECHO the command outputs will be saved in the same folder as the output file.
    ECHO -------------------------------------------------------------------------------
    :end
    REM This is the end.
    EDIT: Fix for US date format.

    Lars Rönnbäck -- http://www.anchormodeling.com -- An Agile Modeling Technique for Evolving Information

    Wednesday, March 28, 2012 9:13 AM