Solved

Powershell Script Export from SQL to CSV No Headers

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

A procedure for exporting installed hotfix details of remote computers using powershell
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

705 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