Export query result to csv file in SQL Server 2008

Question

How can I export a query result to a  csv file in SQL Server 2008

User · Answer

You could use QueryToDoc  http   www querytodoc com   It lets you write a query against a SQL database and export the results - after you pick the delimiter - to Excel  Word  HTML  or CSV

User · Answer

Using the native SQL Server Management Studio technique to export to CSV  as  8kb suggested  doesn t work if your values contain commas  because SSMS doesn t wrap values in double quotes   A more robust way that worked for me is to simply copy the results  click inside the grid and then CTRL-A  CTRL-C  and paste it into Excel   Then save as CSV file from Excel

User · Answer

If the database in question is local  the following is probably the most robust way to export a query result to a CSV file  that is  giving you the most control     Copy the query  In Object Explorer right-click on the database in question  Select  Tasks      Export Data     Configure your datasource  and click  Next   Choose  Flat File  or  Microsoft Excel  as destination  Specify a file path  If working with a flat file  configure as desired  If working with Microsoft Excel  select  Excel 2007   previous versions have a row limit at 64k  Select  Write a query to specify the data to transfer  Paste query from Step 1  Click next    review mappings    click next    select  run immediately     click  finish  twice       After going through this process exhaustively  I found the following to be the best option   PowerShell Script   dbname      YOUR DB NAME WITHOUT STARS     AttachmentPath    c   export csv   QueryFmt       YOUR QUERY WITHOUT STARS       Invoke-Sqlcmd   -ServerInstance   SERVER NAME WITHOUT STARS   -Database   dbname -Query  QueryFmt   Export-CSV  AttachmentPath -NoTypeInformation   Run PowerShell as Admin   amp   c  path to your ps1 file ps1

User · Answer

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  as  Slogmeister Extraordinaire Quoted is correct  One need to have 1 gt  File already present with columns 2 gt  One need to have Office installed Errors faced 1 Msg 7303  Level 16  State 1  Line 1 Cannot initialize the data source object of OLE DB provider  quot Microsoft ACE OLEDB 12 0 quot  for linked server  quot  null  quot   quot  64 bit http   download microsoft com download 2 4 3 24375141-E08D-4803-AB0E-10F2E3A07AAA AccessDatabaseEngine x64 exe 32 bit http   download microsoft com download f d 8 fd8c20d8-e38a-48b6-8691-542403b91da1 AccessDatabaseEngine exe 2 Msg 15281  Level 16  State 1  Line 1 SQL Server blocked access to STATEMENT  OpenRowset OpenDatasource  of component  Ad Hoc Distributed Queries  because this component is turned off as part of the security configuration for this server  A system administrator can enable the use of  Ad Hoc Distributed Queries  by using sp configure  For more information about enabling  Ad Hoc Distributed Queries   search for  Ad Hoc Distributed Queries  in SQL Server Books Online  EXEC sp configure  show advanced options   1 RECONFIGURE GO EXEC sp configure  ad hoc distributed queries   0 RECONFIGURE GO

User · Answer

Building on N S s answer  I have a PowerShell script that exports to a CSV file with the quote marks around the field and comma separated and it skips the header information in the file   add-pssnapin sqlserverprovidersnapin100 add-pssnapin sqlservercmdletsnapin100   qry      Select     From  tablename     Invoke-Sqlcmd -ServerInstance Server -Database DBName -Query  qry   convertto-CSV -notype   select -skip 1   gt   full path and filename csv    The first two lines enable the ability to use the Invoke-SqlCmd command-let

User · Answer

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 are 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

User · Answer

You can use PowerShell   AttachmentPath    CV File location   QueryFmt   Query   Invoke-Sqlcmd -ServerInstance Server -Database DBName -Query  QueryFmt   Export-CSV  AttachmentPath

User · Answer

Yes  all these are possible when you have the direct access to the servers  But what if you have only access to the server from a web   application server  Well  the situation was this with us a long back and the solution was SQL nbsp Server Export to CSV

User · Answer

I know this is a bit old  but here is a much easier way      Run your query with default settings  puts results in grid format  if your s is not in grid format  see below  Right click on grid results and click  Save Results As  and save it    If your results are not in grid format  right click where you write the query  hover  Results To  and click  Results To Grid   Be aware you do NOT capture the column headers   Good Luck

User · Answer

If you don t want to use Powershell  this answer is a variation on 8kb s great answer  The only difference is that instead of selecting CSV as the output format  select Tab Delimited  This way if there are commas in your data  it won t skip cells in Excel  Also  if you have Excel s default delimiter set to tabs  you can simply do a copy-all of the SSMS  query results  CTRL-A  CTRL-C  and paste into Excel  no need to save as a file and import to Excel     In SSMS Go to Tools   Options   Query Results   SQL Server   Results To Text Change output format on far right to Tab Delimited Click OK   Now you can execute your query  then do a CTRL-A to select all the results  then CTRL-C to copy to clipboard  then switch to Excel 2013  may work in 2007 too  not sure  and paste -- assuming Excel s default delimiter is set to tab   Image of SSMS Query Options Screen

User · Answer

Open SQL Server Management Studio Go to Tools   Options   Query Results   SQL Server   Results To Text On the far right  there is a drop down box called Output Format Choose Comma Delimited and click OK   Here s a full screen version of that image  below    This will show your query results as comma-delimited text    To save the results of a query to a file  Ctrl   Shift   F

User · Answer

MS Excel -  Data -  New Query -  From Database   follow the steps

User · Answer

One more method worth to mention here  SQLCMD -S SEVERNAME -E -Q  quot SELECT COLUMN FROM TABLE quot  -s  quot   quot  -o  quot c  test csv quot   NOTE  I don t see any network admin let you run powershell scripts

User · Answer

I hope 10 years isn t too late  I used this in Windows Scheduler   quot C  Program Files Microsoft SQL Server 110 Tools Binn SQLCMD EXE quot  -S BLRREPSRVR SQLEXPRESS -d Reporting DB -o  quot C  Reports CHP Gen csv quot  -Q  quot EXEC dbo CHP Generation Report quot  -W -w 999 -s  quot   quot   It opens sql command  exe and runs a script designed for sql command  The sql command script is very easy to use with a few google searches and trial and error  You can see that it picks a database  defines output location and executes a procedure  The procedure is just a query selecting which rows and columns to display from a table in the database

[sql-server-2008] Export query result to .csv file in SQL Server 2008

Examples related to sql-server-2008