Writing select result to a csv file

  • We need to write the SELECT query results to a csv file. How can it be done using T-SQL in SQL Server 2008 r2? I know that it can be done in SSIS, but for some reasons, we don't have this option.

    I tried to use the suggested proc in the article below, but when I run the proc, SQL complains that can't run sys.sp_OACreate and sys.sp_OADestroy which are called in this proc.

    Do you know how we can turn on these components or know any better way to write to a file using T-SQL?

    Thanks in advance.

  • RK Kuppala

    RK Kuppala Correct answer

    8 years ago

    Use BCP utility

    bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T

    The -c argument specifies character output, as opposed to SQL's native binary format; this defaults to tab-separated values, but -t , changes the field terminator to commas. -T specifies Windows authentication ("trusted connection"), otherwise use -U MyUserName -P MyPassword.

    This doesn't export column headers by default. You need to use a UNION ALL for headers


    Use SQLCMD

    SQLCMD -S SERVERNAME -E -Q "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable"
    -s "," -o "D:\MyData.csv" 


    Use Powershell

    Here is a link to an article. If you end up using this, you might want to append -notype after Export-Csv $extractFile to get rid of the unnecessary column on the output file.

    +1 For BCP utility - it is very easy for use, it is the fastest tool that I have ever used for export/import information and there are a lot of options. I will advice you to add "> FileName.log" at the end of the statement - this will create a log file.

    Any answer involving a GUI program is simply out of scope. This is the best solution from a DBA point of view!

  • Adding to the previous answer, which helps you automate the action, if you need it only from time to time you can do it in Management Studio, simply right click on the header - Save results as -> choose a .csv file.

    Or if you want to do that for each select statement that you run, you can change the output direction of the results to a file. Use Tools -> Options -> Query Results - Results to file.

    Another way, that can be automated easily, and makes use of SSIS, is by using Management Studio's Export Data feature. Right click on the database -> Tasks -> Export data. There is a wizard with lots of options there. You should choose the source database, the destination and other options. For the destination make sure it's "Flat file", browse and choose .csv type, choose whatever formatting you need and at the end you'll obtain an SSIS package that can be saved locally and repeated on need.

  • Use T-SQL

    INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Text;Database=D:\;HDR=YES;FMT=Delimited','SELECT * FROM [FileName.csv]')
    SELECT Field1, Field2, Field3 FROM DatabaseName

    But, there's a couple of caveats:

    1. You need to have the Microsoft.ACE.OLEDB.12.0 provider available. The Jet 4.0 provider will work, too, but it's ancient, so I used this one instead.
    2. The .CSV file will have to exist already. If you're using headers (HDR=YES), make sure the first line of the .CSV file is a delimited list of all the fields.

    Best answer so far!

    great job including caveats

  • Low tech, but...

       col1 + ','
    +  cast(col2 as varchar(10)) + ','
    +  col3
    from mytable;

    MySQL treats `col1 + ',' + cast(col2 as varchar(10)) + ',' + col3` as a column name, and prints NULL all over the place.

  • It looks like you already have an acceptable solution, but if you have db mail set up you could potentially do something like this:

    EXEC msdb.dbo.sp_send_dbmail
        @recipients='[email protected]',
        @subject='CSV Extract', 
        @body='See attachment',
        @query ='SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable',
        @attach_query_result_as_file = 1,
        @query_attachment_filename = 'CSV_Extract.txt',
        @query_result_separator = ',',
        @query_result_header = 1

    If you are emailing the file somewhere anyway, it might save you a few steps.

    Just what I was looking for, kudos to you!

    Although you will need to add @profile_name parameter if you have no default private profile for your user nor public profile for the database.

  • This can easily be done in a 3 step process:

    1. Write your query as a SELECT INTO query. This will essentially export your query results into a table.

      INTO dbo.LogTableName         --Table where the query Results get logged into.
      From Table

      Note, this table cannot exist when the query runs, as this type of query wants to create the table as part of the execution.

    2. Then use SSIS to export the results of that table to .csv. The SSIS Export wizard lets you pick your delimiter, etc. This is really handy for using a | delimiter in instances where the result set contains commas.

      Right click on DB Name > Tasks > Export Data

    3. Once the Export is complete, execute the DROP TABLE command to cleanup your log table.

      Drop Table dbo.LogTableName

    Hi Don. I added some formatting to your answer. You can click on 'edit' in the lower-left to see what's changed and click on the "edited" xx minutes" bit to see a full history if you want to roll back. Although workable, your answer doesn't add much over what's already been written and is unlikely to attract much attention.

  • This procedure will generate a CSV file with the output of the query you pass as a parameter. The 2nd parameter is your CSV file name, make sure you pass the file name with a valid writable path.

    Here is the script to create the procedure:

    CREATE procedure [dbo].[usp_create_csv_file_from_query]
        @sql Nvarchar(MAX),
        @csvfile Nvarchar(200)
            if IsNull(@sql,'') = '' or IsNull(@csvfile,'') = ''
                RAISERROR ('Invalid SQL/CsvFile values passed!; Aborting...', 0, 1) WITH NOWAIT
            DROP TABLE if exists global_temp_CSVtable;
                @columnList varchar(4000), 
                @columnList1 varchar(4000),
                @sqlcmd varchar(4000),
                @dos_cmd nvarchar(4000)
            set @sqlcmd = replace(@sql, 'from', 'into global_temp_CSVtable from')
            EXECUTE (@sqlcmd); 
            declare @cols table (i int identity, colname varchar(100))
            insert into @cols
            select column_name
            from information_schema.COLUMNS
            where TABLE_NAME = 'global_temp_CSVtable'
            declare @i int, @maxi int
            select @i = 1, @maxi = MAX(i) from @cols
            while(@i <= @maxi)
                select @sql = 'alter table global_temp_CSVtable alter column [' + colname + '] VARCHAR(max) NULL'
                from @cols
                where i = @i
                exec sp_executesql @sql
                select @i = @i + 1
                 @columnList = COALESCE(@columnList + ''', ''', '') + column_name 
                ,@columnList1 = COALESCE(@columnList1 + ', ', '') + column_name
            from information_schema.columns
            where table_name =  'global_temp_CSVtable'
            SELECT @columnList = '''' + @columnList + '''';
            SELECT @dos_cmd='BCP "SELECT ' + @columnList + 
                            ' UNION ALL ' +
                            'SELECT * from ' + db_name() + '..global_temp_CSVtable" ' + 
                            'QUERYOUT ' + @csvfile + 
                            ' -c -t, -T'
            exec master.dbo.xp_cmdshell @dos_cmd, No_output 
            DROP TABLE if exists global_temp_CSVtable;

    code only answers aren't seen as acceptable here - could you add some commentary on your answer and why it works? (code comments would probably help here too)

    Yes, and could you also add an example as how you successfully use it?

  • I use MS Access DoCMD to export CSV's from SQL Server. It is able to do it with a single command. The access database is only used to enable the access command. It performs well, and as an added benefit (not sure why) it creates a schema.ini for the table exported.


    CMD="SELECT * INTO [Text;HDR=Yes;DATABASE=C:\].[Results.txt]  
    FROM [ODBC;DSN=SQL2017;Description=SQL2017;UID=.;Trusted_Connection=Yes;APP=Microsoft Office 2013;WSID=LAPTOP-XYZ;DATABASE=TempDB_DataDump_1].Results "
     Set appAccess = CreateObject("Access.Application") 
                    appAccess.OpenCurrentDatabase "anyAccessdatabase.mdb" 
                       ' appAccess.DoCmd.SetWarnings 0
                        appAccess.DoCmd.runsql "CMD"

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM