Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Sunita's awesome blog!
Browse by Tags · View All
T-SQL 1
ISNUMERIC 1
SQL Server; Import 1
Powershell 1

Archive · View All
October 2012 3

Exporting from SQL Server to CSV file using Powershell

Oct 20 2012 12:00AM by SunitaBeck   

Powershell's Export-Csv Cmdlet is an alternative to bcp command or SSIS for exporting data from SQL Server to a csv file. In its simplest form, the command would be like this:

Invoke-SqlCmd -Query "SELECT * FROM AdventureWorks.Sales.Currency" | Export-Csv C:\Temp\Test.csv
You will need to run this command from a Powershell window. If you started Powershell from SQL Server Management Studio (or sqlps.exe from the Tools\Binn folder) the command above should work and generate the csv file.

If you started Windows Powershell from windows (System32\WindowsPowershell folder), you will get an error message. In that case you will need to import the sqlps module into your shell environment for Invoke-SqlCmd to work.   
Import-module "sqlps" -DisableNameChecking
Invoke-SqlCmd -Query "SELECT * FROM AdventureWorks.Sales.Currency" | Export-Csv C:\Temp\Test.csv
The first line in the exported CSV file will be the type information of the data that was exported. (Something like #TYPE System.Data.DataRow).  More likely than not, you don't want the type information in your CSV file. To remove the type information, use the "-NoTypeInformation" parameter.
Invoke-SqlCmd -Query "SELECT * FROM AdventureWorks.Sales.Currency" |
Export-Csv -NoType C:\Temp\Test.csv
You will notice that in the CSV file each column is in double-quotes - which is the standard and recommended method to avoid confusion between commas that are part of the data and commas that are field separators. For some reason, if you don't want the double-quotes, Export-Csv cmdlet unfortunately has no parameter or option that will specify this. Instead what you can do is use the ConvertTo-Csv cmdlet instead of Export-Csv cmdlet and remove the double-quotes yourself:
Invoke-SqlCmd -Query "SELECT * FROM AdventureWorks.Sales.Currency" |
ConvertTo-Csv -NoType |
ForEach-Object {$_.Replace('"','')}|
Out-file c:\temp\test.csv
When you want to automate the exports, you can save the command to script file with .ps1 extension and run the script file.

I like Powershell over BCP to export CSV files mainly because, by default, files exported using BCP does not include column names, and there is no simple option or switch in BCP to request that column names be included.

Tags: 


SunitaBeck
680 · 0% · 49
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • In the last example, due to SQLPS limitations, the ConvertTo-CSV does not work. However, one can export to a file and then Get-Content "file.csv" again.

    commented on Apr 20 2014 10:59PM
    KipB7
    1542 · 0% · 13

Your Comment


Sign Up or Login to post a comment.

"Exporting from SQL Server to CSV file using Powershell" rated 5 out of 5 by 2 readers
Exporting from SQL Server to CSV file using Powershell , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]