39

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.

53

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

OR

Use SQLCMD

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

OR

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.

  • 4
    +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. – gotqn Sep 3 '12 at 9:30
  • Any answer involving a GUI program is simply out of scope. This is the best solution from a DBA point of view! – EnzoR Mar 9 at 9:28
24

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.

10

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.
  • 1
    Best answer so far! – ajeh Jun 6 '16 at 14:11
  • 1
    great job including caveats – JJS Apr 26 '18 at 16:57
5

Low tech, but...

select
   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. – Nikhil Wagh Oct 24 '19 at 12:03
4

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='your.email@domain.com',
    @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! – Mashchax Apr 1 '19 at 10:40
  • 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. – Mashchax Apr 1 '19 at 10:48
1

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.

    Select
      Field1
     ,Field2
     ,Field3
    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
    
  • 3
    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. – Michael Green Nov 8 '16 at 22:14
0

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.

Adam

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"
-1

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)
    as
    BEGIN
        if IsNull(@sql,'') = '' or IsNull(@csvfile,'') = ''
        begin
            RAISERROR ('Invalid SQL/CsvFile values passed!; Aborting...', 0, 1) WITH NOWAIT
            return
        end
        DROP TABLE if exists global_temp_CSVtable;
        declare 
            @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)
        begin
            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
        end

        SELECT 
             @columnList = COALESCE(@columnList + ''', ''', '') + column_name 
            ,@columnList1 = COALESCE(@columnList1 + ', ', '') + column_name
        from information_schema.columns
        where table_name =  'global_temp_CSVtable'
        ORDER BY ORDINAL_POSITION;

        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;
    end
  • 2
    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) – George.Palacios Apr 5 '19 at 13:32
  • Yes, and could you also add an example as how you successfully use it? – Marcello Miorelli Sep 28 '19 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.