Trying to write a data set from a SQL query out to a CSV file in Powershell

Question

I would like to create a CSV file (with no headers if possible) from a SQL query I make. My powershell is below. It reads the DB no problem and I see row results come back from printint to stdout. But I can't seem to figure out how to get the results into a CSV file.

The txt file created from my powershell only has one line it:

TYPE System.Int32

Which seems to me like it is just outputing the data type or something. It seems odd it is In32 but maybe that is because it is a pointer to the object?

I saw one article that used Tables[0] when using a data set but when I replaced my last line with $QueryResults.Tables[0] | export-csv c:\tests.txt It gave me an error saying it couldn't bind an argument to parameter because it was null.

function Get-DatabaseData {
param (
    [string]$connectionString,
    [string]$query,
    [switch]$isSQLServer
)
if ($isSQLServer) {
    Write-Verbose 'in SQL Server mode'
    $connection = New-Object -TypeName System.Data.SqlClient.SqlConnection
} else {
    Write-Verbose 'in OleDB mode'
    $connection = New-Object -TypeName System.Data.OleDb.OleDbConnection
}
$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $query
if ($isSQLServer) {
    $adapter = New-Object -TypeName System.Data.SqlClient.SqlDataAdapter $command
} else {
    $adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
}
$dataset = New-Object -TypeName System.Data.DataSet
$adapter.Fill($dataset)
$dataset.Tables[0]
}

$QueryResults = Get-DatabaseData -verbose -connectionString 'Server=localhost\SQLEXPRESS;;uid=sa; pwd=Mypwd;Database=DanTest;Integrated Security=False;' -isSQLServer -query "SELECT * FROM Computers"
foreach($MyRow in $QueryResults) {
write-output $MyRow.computer
write-output $MyRow.osversion
}

$QueryResults | export-csv c:\tests.txt

I added a line to print $QueryResults to stdout and below is the output in the console of that and the write form my for loop to show what my $QueryResults has.

5


computer         : localhost
osversion        :
biosserial       :
osarchitecture   :
procarchitecture :

computer         : localhost
osversion        :
biosserial       :
osarchitecture   :
procarchitecture :

computer         : not-online
osversion        :
biosserial       :
osarchitecture   :
procarchitecture :

computer         :
osversion        : win8
biosserial       :
osarchitecture   :
procarchitecture :

computer         : dano
osversion        : win8
biosserial       :
osarchitecture   :
procarchitecture :

localhost

localhost

not-online


win8
dano
win8

Matt · Accepted Answer · 2014-12-05 16:23:21Z

1

You might have looping issues as from the sounds of it your $QueryResults might not contain the data you are looking for. What does $QueryResults look like while in the console? Once you address that this should take care of the output for you.

From TechNet

By default, the first line of the CSV file contains "#TYPE " followed by the fully-qualified name of the type of the object.

To get around that you would just use the -NoTypeInformation switch like Alroc suggested. However you also mentioned removing the header which would require a different approach.

$QueryResults | ConvertTo-CSV -NoTypeInformation | Select-Object -Skip 1 | Set-Content "c:\tests.txt"

or something slightly different with the same result.

$QueryResults | ConvertTo-CSV | Select -Skip 2 | Set-Content "c:\tests.txt"

Convert the $QueryResults to a csv object. However you choose to we omit the lines containing the type information and the header / title row. Should just have nothing but data at that point.

Update From Discussion

The ConvertTo-CSV and Export-CSV were not showing expected data but nothing except type information. I didnt really notice at first but the output of $QueryResults contained a first line that was just the number 5. Presumably it was the # of records from the result dataset. If we skip the first record of $QueryResults then we are left with just the data needed. Might be better to look into ther reason that first line is there ( maybe there is a way to supress it. ). As the question stands currently we address this as follows. The first skip is to ignore the "5" line and the second is to remove the header from the csv output.

$QueryResults | Select -Skip 1 | Convert-ToCSV -NoTypeInformation | Select-Object -Skip 1 | Set-Content "c:\tests.txt"

edited Dec 5, 2014 at 16:23

answered Dec 5, 2014 at 3:43

Matt

47.1k9 gold badges90 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

user461051 Over a year ago

Thanks guys. I added to my original question the output of $QueryResults to Console and the output from my for loop where I print the computer and osversion values.

user461051 Over a year ago

Maybe I don't understand what should be in the $MyRow values in each loop through the for. I thought it would be each record in the dataset and then I would expect my write-output to print the computer and then osversion for each row. But in the output I get it is printing all three computer values first then all 3 osversions

user461051 Over a year ago

I figured out my misunderstanding of $MyRow values. It looks like it isn't a row I get in the foreach loop but all values in the first column of a dataset. So I added foreach blocks to print to stdout. e.g. foreach($aRow in $MyRow.computer) { $aRow } gives all the computer names.

user461051 Over a year ago

Matt I tried your first suggestion of doing the convert but it doesn't write to a file c:\tests.txt like it did when I had the export-csv call. What my code was writing isn't what I want but the file did exist.

Matt Over a year ago

@user461051 What is the output of $QueryResults | ConvertTo-CSV -NoTypeInformation?

|

alroc · Accepted Answer · 2014-12-05 02:40:54Z

0

If you want just the data in the CSV file, pass the -NoTypeInformation switch into Export-CSV.

answered Dec 5, 2014 at 2:40

alroc

28.3k6 gold badges55 silver badges106 bronze badges

Collectives™ on Stack Overflow

Trying to write a data set from a SQL query out to a CSV file in Powershell

TYPE System.Int32

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

TYPE System.Int32

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related