?
Solved

Powershell Script Export from SQL to CSV No Headers

Posted on 2013-01-16
5
Medium Priority
?
3,256 Views
Last Modified: 2013-01-17
My script is pretty simple. Just learning, and working my way up to a bigger solution. The script works but I have two issues:

1. I don't want a header, or more to the point, I want to replace the existing one with "alias".
2. My test table only has one row that will be returned. When it runs in Primal Script, it's writing the User Name twice.

I'm assuming from my output that it's telling me only one row was affected.

1

*** PowerShell Script finished. ***

Thanks in advance,

JB

PS I'm trying to be as efficient as possible with the code, so if it looks like I'm "going the long way around" I'm open for suggestions on improvements. Thanks again!!

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

$AttachmentPath = "C:\PSTest.csv"
$SqlQuery = "SELECT UserName from Test WHERE UserName='jschmo'"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery

$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null

#Populate Hash Table
$objTable = $DataSet.Tables[0]

#Export Hash Table to CSV File
$objTable | Export-Csv -NoTypeInformation $AttachmentPath


$SqlConnection.Close()
0
Comment
Question by:JB4375
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:coraxal
ID: 38785193
Since you have the table in an object, you could then use the Select-Object cmdlet to consume the object like this:

$objTable |
Select-Object COLUMN1,COLUMN2,@{label="NEW_COLUMN_NAME";expression={$_.COLUMN_NAME_IN_SQL_TBL}} |
Export-Csv $AttachmentPath -NoTypeInformation
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 38785203
This line does not make sense - $nRecs is sent to the pipeline, and then discarded:
   $nRecs | Out-Null
Just remove it.

If you do not use intermediate results, don't store them in variables. It doesn't matter for small data sets, but the more rows you have, the worse is the memory consumption. Further, if you use pipelines only one object at any time is created for processing, while storing results in vars creates one object per row, so you have e.g. 60000 objects which you usually do not want to process anyway. That means for your code: better to use
   $DataSet.Tables[0] | Export-Csv -Header "Alias" -NoTypeInformation $AttachmentPath

Open in new window

I cannot see where your "1" and doubled user name should come from - maybe it is a Primal Script thingy. Do you see that on the screen only, or in the CSV file?
0
 
LVL 1

Author Comment

by:JB4375
ID: 38787250
Qlemo:

Thanks for the tips. Using your line solved the duplicate listing problem, however it's having an issue with -Header "Alias" and the script throws the following error when included:

ERROR: Export-Csv : A parameter cannot be found that matches parameter name 'Header'.
ERROR: At line:54 char:59
ERROR: + $DataSet.Tables[0] | Export-Csv -NoTypeInformation -Header <<<<  "Alias" $Att
ERROR: achmentPath
ERROR:     + CategoryInfo          : InvalidArgument: (:) [Export-Csv], ParameterBind
ERROR:    ingException
ERROR:     + FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Comm
ERROR:    ands.ExportCsvCommand

Thanks again,

JB
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 38788044
That would have been too easy, I guess. No, export-csv does not allow for providing headers, only import-csv does :( .
The most simple method is to rename the column as it is retrieved:
$AttachmentPath = "C:\PSTest.csv"
$SqlQuery = "SELECT UserName as Alias from Test WHERE UserName='jschmo'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection("Server = $SQLServer; Database = $SQLDBName; Integrated Security = True")
$SqlCmd        = New-Object System.Data.SqlClient.SqlCommand($SqlQuery, $SqlConnection)
$SqlAdapter    = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCmd)

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | out-null

#Populate Hash Table, and export to CSV File
$DataSet.Tables[0] | Export-Csv -NoTypeInformation $AttachmentPath

$SqlConnection.Close()

Open in new window

If you need to change column headers "on the fly" (after having executed the SQL):
$AttachmentPath = "C:\PSTest.csv"
$SqlQuery = "SELECT UserName from Test WHERE UserName='jschmo'"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection("Server = $SQLServer; Database = $SQLDBName; Integrated Security = True")
$SqlCmd        = New-Object System.Data.SqlClient.SqlCommand($SqlQuery, $SqlConnection)
$SqlAdapter    = New-Object System.Data.SqlClient.SqlDataAdapter($SqlCmd)

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | out-null

#Populate Hash Table, and export to CSV File
$DataSet.Tables[0] | select @{l='Alias'; e={$_.Username}} | Export-Csv -NoTypeInformation $AttachmentPath

$SqlConnection.Close()

Open in new window

0
 
LVL 1

Author Closing Comment

by:JB4375
ID: 38788275
Thanks Qlemo!!

On the fly works for me!!

JB
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question