Solved

Powershell Script Export from SQL to CSV No Headers

Posted on 2013-01-16
5
2,903 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Qlemo!!

On the fly works for me!!

JB
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

16 Experts available now in Live!

Get 1:1 Help Now