Utilizing MySQL query results

Question

Need help with solving this problem: I need to get table data from MySQL DB for further use in script. I'm using this code for accessing MySQL data:

[void][System.Reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Connector Net 6.7.4\Assemblies\v2.0\MySql.Data.dll")
$Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
$Connection.ConnectionString = $ConnectionString
$Connection.Open()
$Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
$DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
$DataSet = New-Object System.Data.DataSet
$RecordCount = $dataAdapter.Fill($dataSet,"data") | Out-Null
$Command.Dispose()
$Table=$DataSet.Tables["data"] | FT  -auto
$Connection.Close()
$Table

and it gives me my precious piece of junk:

TASKID TASKTYPE fistNAME secNAME STATUS
------ -------- -------- ------- ------
1111          1 Dep1                  0
2222          2 User321  Dep1         0

BUT when I try to, for example export results to CSV:

Export-Csv -Path "c:\test.csv" -InputObject $Table

all I get is:

#TYPE System.Object[]
"Count","Length","LongLength","Rank","SyncRoot","IsReadOnly","IsFixedSize","IsSynchronized"
"6","6","6","1","System.Object[]","False","True","False"

so when I try to parse data in variable like this:

Write-Host $Table
foreach ($Task in $Table) {
    Write-Host $Task.TASKID
}

all I get is:

Microsoft.PowerShell.Commands.Internal.Format.FormatStartData
Microsoft.PowerShell.Commands.Internal.Format.GroupStartData
Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData
Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData
Microsoft.PowerShell.Commands.Internal.Format.GroupEndData
Microsoft.PowerShell.Commands.Internal.Format.FormatEndData

Can anyone help me to resove this problem?

What happens when you write-host $Task and then pipe it into a format-list and select all Properties? (write-host $Task | fl -Property *) That will show you all the Properties that you can access. You could also try write-host $Task.TASKID.Value that mite pull out the value of the TASKID property. — Richard
– Richard, Commented Jul 12, 2013 at 10:39
well on 'write-host $Task | fl -Property *' it gives me"Microsoft.PowerShell.Commands.Internal.Format.FormatEndData" and on 'write-host $Task.TASKID.Value' another blank row — Andrey
– Andrey, Commented Jul 12, 2013 at 12:06
It's the way that your creating the variable $Table that is the problem. Have you tried the suggested answer below? — Richard
– Richard, Commented Jul 12, 2013 at 12:27

Ansgar Wiechers · Accepted Answer · 2013-07-12 11:57:06Z

6

This line

$Table=$DataSet.Tables["data"] | FT  -auto

transforms your data into an array of FormatStartData objects.

Don't pipe your data into Format-Table when you want to export it. Try this instead:

$DataSet.Tables["data"] | Export-Csv "c:\test.csv"

answered Jul 12, 2013 at 11:57

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

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

1 Comment

Andrey Over a year ago

YES this is the solution! you're the MAN.

Collectives™ on Stack Overflow

Utilizing MySQL query results

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related