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.
Use BCP utility
bcp "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" queryout "D:\MyTable.csv" -c -t , -S SERVERNAME -T
-cargument 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.
-Tspecifies 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
SQLCMD -S SERVERNAME -E -Q "SELECT Col1,Col2,Col3 FROM MyDatabase.dbo.MyTable" -s "," -o "D:\MyData.csv"
Here is a link to an article. If you end up using this, you might want to append
Export-Csv $extractFileto 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.
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.
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:
- 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.
- 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.
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.
This can easily be done in a 3 step process:
Write your query as a
SELECT INTOquery. 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.
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
Once the Export is complete, execute the
DROP TABLEcommand 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) 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
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)
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"