Get MySQL Data Using Powershell

Question

I've been trying to set up powershell (v.2) to connect to my MySQL database, so that I can then

get the number of rows returned, and
export to csv

I've tried various scripts I've come across but have had the most success using the function on this page:

http://www.thomasmaurer.ch/2011/04/powershell-run-mysql-querys-with-powershell/

Using this code I have been able to export a csv file using:

$result = run-MySQLQuery -ConnectionString "
    Server=127.0.0.1;
    Port=3312;
    Uid=xxxxx;
    Pwd=xxxxx;" `
    -Query "$SQL;"

$result | export-csv "c:\_emptyFolder\Test\test2.csv" -NoTypeInformation

But, the csv file has the following data:

ClassId2e4f51ef21dd47e99d3c952918aff9cd pageHeaderEntry pageFooterEntry autosizeInfo shapeInfo 033ecb2bc07a4d43b5ef94ed5a35d280 Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo 9e210fe47d09416682b841769c78b8a3
27c87ef9bbda4f709f6b4002fa4af63c

when it should have:

id action

1 a

2 a

1 b

Also whenI tried to get the number of rows returned using:

$result.length | write-host

I get 31 when it should be 27

As you can tell I'm new to Powershell, any help on this matter would be appreciated.

I'm just using test data at the minute similar to:

CREATE TABLE test.`tb1` (`id` int(11) NOT NULL,`action` char(2) NOT NULL,PRIMARY KEY (`action`,`id`)) ENGINE=InnoDB;
INSERT INTO test.`tb1` VALUES (3,'a'),(3,'b'),(4,'a'),(4,'b');

I don't have a solution, but I would advise not using that function (or modifying it for use). He's returning the data via Format-Table, which makes it impossible to use as data - it's just formatted text output to the caller. You can avoid this by removing |format-table from the last line of the try block. — alroc
– alroc, Commented Aug 16, 2014 at 12:26
I think that may be the answer right there. Certainly it looks a lot more like what I was after now. The Row Count is now correct, and the CSV file has 'proper' data in it. I assume there is some 'trick' I need to learn to handle BLOB fields, but that's a minor point right now. Cheers (if you write it up I'll mark it as the answer) — IGGt
– IGGt, Commented Aug 18, 2014 at 9:19

alroc · Accepted Answer · 2014-08-18 13:29:43Z

1

The function you're using outputs data in a format that can't be used downstream. The results of the query are piped through Format-Table, which is nice for reading on screen but if you need to use those results as data then you're stuck.

Unless the task of a cmdlet/function is to format output, it should not be returning data through a format-* cmdlet. Let the caller handle the formatting, if necessary.

For this particular case, remove |format-table from the last line of the try block and the results will be returned as usable data.

answered Aug 18, 2014 at 13:29

alroc

28.3k6 gold badges55 silver badges106 bronze badges

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

Collectives™ on Stack Overflow

Get MySQL Data Using Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related