We help IT Professionals succeed at work.

export SQL result to a CSV file

marrowyung
marrowyung asked
on
Dear all expertist,

Is there any way to export the result of a SQL query automatically to a CSV instead of on sceen?

In this case, I dont' want to have more than this step to make the CSV created.

DBA100.
Comment
Watch Question

Commented:
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
AnujSQL Server DBA
Top Expert 2011

Commented:
Also use import\Export wizard, your destination should be CSV file and source is the query.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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

Open 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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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
AnujSQL Server DBA
Top Expert 2011

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Qlemo:

So what I need to do is to replace:

"SELECT field1 from tbl


with my select query ?
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Yes, and you need to replace path ("Database") and filename, of course.

Example for sqlcmd, without header info and semicolon-separated:
sqlcmd -W -s ";" -h -1 -r1 -Q "set nocount on; select * from sys.tables" -S YourInstance -E -d YourDB

Open in new window

marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?
marrowyungSenior Technical architecture (Data)

Author

Commented:
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 ?

Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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
marrowyungSenior Technical architecture (Data)

Author

Commented:
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?



marrowyungSenior Technical architecture (Data)

Author

Commented:
How can I make the   sqlcmd  thing export the colume also ?
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Qlemo:

With  PowerShell, how can I do this?

DBA100.
marrowyungSenior Technical architecture (Data)

Author

Commented:
Qlemo:

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

DbA100.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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()

Open 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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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])
...

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

Open in new window

or modify it to any extend you like.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.



Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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"

Open 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()

Open 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.
marrowyungSenior Technical architecture (Data)

Author

Commented:
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.
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
Ah, that is something different then. You want to read the SQL from a file. The following code will allow for both.
<# Sql2Excel - generate Excel worksheet from SQL results #>

param(
  [String] $constr,		<# connection string;  ex. "server=Srv;database=master;Integrated Security=sspi" #>
  [String] $sqlCmd,		<# sql command;        ex. "SELECT top 10 name, type from sysobjects"            #>
  [String] $sqlFile,    <# sql cmd file;       ex. "C:\temp\mySql.sql"                                   #>
  [String] $xlsFile		<# XLS file to create; ex. "c:\temp\test"                                        #>
)
<# Note: The sqlFile parameter, if provided, will overrule sqlCmd parameter #>
if ($sqlFile)
{
	$sqlCmd = (Get-Content $sqlFile) -join " "
}


<# 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 #>
$set = new-object system.data.dataset

<# now doing the work #>
(new-object System.Data.SqlClient.SqlDataAdapter ($sqlCmd, $constr)).Fill($set) | out-null

$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($xlsFile)
$excel.Quit()

Open in new window

It needs to be called with parameter names, since either sqlCmd or sqlFile should be provided.
Examples for calling:
.\Sql2Excel -constr "server=Srv;database=master;Integrated Security=sspi" -sqlCmd "select top 10 name,type from sysobjects"  -xlsFile "C:\temp\test"

.\Sql2Excel -constr "server=Srv;database=master;Integrated Security=sspi" -sqlFile "C:\temp\test.sql" -xlsFile "C:\temp\test"

Open in new window

The first one uses an command provided on command line, the second a command provided in a text file. The text file contents can be formatted as you like, it just needs to build a valid SELECT statement.