Solved

Powershell Script Export from SQL to CSV No Headers

Posted on 2013-01-16
5
2,961 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
  • 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 68

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 68

Accepted Solution

by:
Qlemo earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
How to sign a powershell script so you can prevent tampering, and only allow users to run authorised Powershell scripts
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

864 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now