Solved

Powershell Script Export from SQL to CSV No Headers

Posted on 2013-01-16
5
3,003 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 69

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 69

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

785 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