export SQL result to a CSV file

WRITE YOUR QUERY IN THE QUERY ANALYZER
QUERY -> RESULTS TO -> RESULTS TO FILE
EXECUTE QUERY
SAVE FILE AS .xls

Here is the link...
http://www.sqlservercentral.com/Forums/Topic274312-8-1.aspx

Also use import\Export wizard, your destination should be CSV file and source is the query.

anujnb:

Where is the import/export wizard? YOu mean the import and export data application ?

The "Provide a Source query" windows can't understand my working query, funny !

Murali1984: What I want is, developer can do this directly from Query windows instead of clicking more steps.

DBA100.

In "Data Source" of the page "Choose a data source" page, what should I select if I parse  a query ?

It seems it doens't work.

DBA100.

Look into OPENROWSET or OPENDATASOURCE, and provide a Jet driver;

INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Hdr=Yes; Fmt=Delimited; Database=C:\Temp', sql.txt)
SELECT field1 from tbl

Select allOpen in new window

The delimiter used is defined in registry:
    HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format=Reg_SZ:"TabDelimited"
You can also use "Delimited(;)" to use semicoons instead of tabs.
I did not test, but I suppose the target file has to exist already, with the proper header info.

Other ways to do it (and that is how I do) is to use osql (2000) or sqlcmd (2005 and up) to generate a formatted string, and just redirect the output to a file. That gives you much more control over the layout.

Qlemo:

please see image, this is what I don't know which one to select.

"Other ways to do it (and that is how I do) is to use osql (2000) or sqlcmd (2005 and up) to generate a formatted string, and just redirect the output to a file. That gives you much more control over the layout."

Any example on doing this ?

DBA100.

data-source.JPG

you said you need to do this programmatic, so  @Qlemo said the right method, Openrowset.

If you want to do this by wizard then go with Import/Export Method. Select FlatFile Source and then select your CSV file in file section.

Qlemo:

So what I need to do is to replace:

"SELECT field1 from tbl
" 

with my select query ?

When I run this one :

INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Hdr=Yes; Fmt=Delimited; Database=C:\Temp', sql.txt)
< my query>

it shows error:

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

What is that mean ?

By this command:

sqlcmd -W -s ";" -h -1 -r1 -Q "set nocount on; select * from sys.tables" -S YourInstance -E -d YourDB

where can I specify the outfile name ?

In regard of the Jet driver error: You are using 64bit MSSQL, obviously. Jet drivers are available only on 32bit (by default). See this thread http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/33436d82-085c-43e4-b991-a2d0d701c8fc for a solution (downloading and using ACE drivers instead).

With sqlcmd you can either provide the output file with
  sqlcmd ...    -o YourOutFileName
or use file redirection:
  sqlcmd ....  > YourOutFileName

Qlemo:

When using ACE 2010, and I do this:

"INSERT OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Hdr=Yes; Fmt=Delimited; Database=C:\Temp', sql.txt)
<query>
go
"
then it shows that:
"Msg 7308, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
"
IT seems that the sqlcmd is better but it seems that it can't export chinese characters. any way to get ride of it?

How can I make the   sqlcmd  thing export the colume also ?

Chinese? Ouch. We will have to export Unicode then, which is done by adding the switch  -u   to the sqlcmd.
But what do you mean with http:#a37272120? Do you mean the column name? In that case just remove the  -h -1  , which is saying to not display any headers.

Qlemo:

it seems the separator is something else.

how to make the format UTF-8 and remove the double quote on each line of the result? excel can't understand the double quote.

DBA100.

Sorry, but sqlcmd is not able to export to UTF-8, only ANSI (using the current codepage) or Unicode.
I do not get any double quotes.

I've got the impression you would be much better off by using a direct import method, e.g. with  PowerShell, instead of creating a CSV and trying to import that.

Qlemo:

With  PowerShell, how can I do this?

DBA100.

Qlemo:

usually from your point of view, what tools you will use to open that output CSV other than excel ?

DbA100.

Method 1: Use Excel's ODBC capabilities for executing a query within Excel.
Method 2: Direct import into Excel via PowerShell or VBScript. The following is a generic script to do that:

<# Excel initialization stuff #>
$excel = New-Object -ComObject excel.application
$excel.visible=$true
$wb = $excel.Workbooks.Add()   		# empty, unnamed workbook

#   Delete all but one work sheet
$excel.DisplayAlerts = $false
for ($i = $wb.Worksheets.Count; $i -ge 2; --$i) {$wb.Worksheets.Item($i).Delete()}
$excel.DisplayAlerts = $true
$ws = $wb.Worksheets.Item(1)

<# MSSQL initialization stuff #>
$constr = "server=Srv;database=master;Integrated Security=sspi"
$cmd = "SELECT top 10 name, type from sysobjects"
$set = new-object system.data.dataset

<# now doing the work #>
(new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $constr)).Fill($set) | out-null
# $obj = $set.Tables[0] 	# | select-object @{n = 'Server'; e = {$svr}}, Version, SP 

$row = 1
$set.Tables[0] |
  % { 
    $col = 1
    foreach ($colname in $set.Tables[0].columns) {
      if ($row -eq 1) { 
        $ws.Cells.Item($row, $col).value2 = "$colname"
        $ws.Cells.Item($row, $col).Font.Bold = $true
      }
      $ws.Cells.Item($row+1, $col++).value2 = $_.$colname
    }
    $row++
  }

$ws.usedRange.EntireColumn.AutoFit() | Out-Null
# $wb.SaveAs("test")
# $excel.Quit()

Select allOpen in new window

Just provide the correct sql and connection info, and it will create an unnamed Excel sheet containing all data retrieved, with the column names in bold. The last lines are how to save and close the Excel workbook, but are commented out - remove the hash signed to let them execute.

In regard of processing or viewing CSV files: Well, meanwhile I tend to use PowerShell for reading them, too ;-). But else I mostly used text editors, since I was never satisfied with Excel's way of handling.

Qlemo:

So this means that I can simple copy, paste the above script and save it as vbs file, and modify the "$cmd = " row with the SQL query we want? anyway to pass in a .sql file from cmd after cscript commmand ?

$constr =  is the connection string ? Any way to pass this information as parameter?

So what I have to do is to open a powershell and then type cscript <vb script file with your script> and enter?

any way to input server name, sql query file we need the output file from at the end of this command ?

This method seem complicate than sqlcmd.


DBA100.

It is more reliable than sqlcmd and using indirect import into Excel,

$constr is the connection string, and you need to change it. Yes, you can provide that from commandline, if you like, but that needs some proper attribution.

$cmd is the sql cmd, and the same applies as for $constr.

This is a PowerShell script, so you need to put it into a .ps1 file, not VBS. I didn't tell you that clearly above. If you replace anything which you want to provide by command line with  $args[0]   (of course changing 0 to 1 for the second parameter, aso.), e.g.

...
$connstr = $args[0]
$cmd = $args[1]
....
$wb.SaveAs($args[2])
...

Select allOpen in new window

and call that file Sql2Excel.ps1, you can call it in PowerShell

.\Sql2Excel "server=Srv;database=master;Integrated Security=sspi" "SELECT top 10 name, type from sysobjects" "test.xls"

Select allOpen in new window

or modify it to any extend you like.

Qlemo:

by using this "sqlcmd -W -u -s ";" -r1 -i test.sql -S <SQL server\instance> -E -d cust_db -o c:\sql1.txt", it seems the output will be mess if the information for each colume is huge, any way to due with it?

so the orginal script seems to me that I can't just simply cut and paste and save it as it .ps1 file, then use your command ".\Sql2Excel "server=Srv;database=master;Integrated Security=sspi" "SELECT top 10 name, type from sysobjects" "test.xls""
 to output it, right?

Please clarify.

Gosh. Here is the cut&paste script, to put into a file in Sql2Excel.ps1, and then use the command line

.\Sql2Excel "server=Srv;database=master;Integrated Security=sspi" "SELECT top 10 name, type from sysobjects" "test.xls"

Select allOpen in new window

You might have to change .\ to point to the folder your .ps1 file is stored in.

<# Sql2Excel
   Parameters:
   1. connection string;  ex. "server=Srv;database=master;Integrated Security=sspi" 
   2. sql command;        ex. "SELECT top 10 name, type from sysobjects"
   3. XLS file to create; ex. "c:\temp\test.xls"
#>
<# Excel initialization stuff #>
$excel = New-Object -ComObject excel.application
$excel.visible=$true
$wb = $excel.Workbooks.Add()            # empty, unnamed workbook

#   Delete all but one work sheet
$excel.DisplayAlerts = $false
for ($i = $wb.Worksheets.Count; $i -ge 2; --$i) {$wb.Worksheets.Item($i).Delete()}
$excel.DisplayAlerts = $true
$ws = $wb.Worksheets.Item(1)

<# MSSQL initialization stuff #>
$constr = $args[0]
$cmd = $args[1]
$set = new-object system.data.dataset

<# now doing the work #>
(new-object System.Data.SqlClient.SqlDataAdapter ($cmd, $constr)).Fill($set) | out-null
# $obj = $set.Tables[0]         # | select-object @{n = 'Server'; e = {$svr}}, Version, SP 

$row = 1
$set.Tables[0] |
  % { 
    $col = 1
    foreach ($colname in $set.Tables[0].columns) {
      if ($row -eq 1) { 
        $ws.Cells.Item($row, $col).value2 = "$colname"
        $ws.Cells.Item($row, $col).Font.Bold = $true
      }
      $ws.Cells.Item($row+1, $col++).value2 = $_.$colname
    }
    $row++
  }

$ws.usedRange.EntireColumn.AutoFit() | Out-Null
$wb.SaveAs($args[2])
$excel.Quit()

Select allOpen in new window

Of course we could use named parameters instead of $args, but that does not change the technique.

And no, you cannot control sqlcmd in the way you would like.

Qlemo:

Thanks for that.

".\Sql2Excel "server=Srv;database=master;Integrated Security=sspi" "SELECT top 10 name, type from sysobjects" "test.xls" "

I knew that I can simple replace Srv and database= to the value I want. But the query part, if I save the query as a .sql file, how can I pass this paramater? my query can be very long. In way is better.

DBA100.