PowerShell.com
Forums » Ask the Experts » Learn PowerShell » Pulling data out of access and formatting it into excel

Pulling data out of access and formatting it into excel

rated by 0 users
This post has 1 Reply | 2 Followers

Not Ranked
Posts 1
Reply
magsdtev Posted: 04-25-2012 3:23 PM

Hello all

I have an Access database and within there is a table that has perf metric data in it (layout below) 

 

METRICS
OBJECTCOUNTERINSTANCETIMEVALUE
Memory % Committed Bytes In Use
4/14/2012 11:17:45 PM 20.6496604287062
Paging File % Usage _Total 4/14/2012 11:17:45 PM 6.94272741147741
System Context Switches/sec
4/14/2012 11:17:45 PM 5899.05361730013

I am trying to pull this infomration out of access and put it into the following format in excel or CSV so line charts and uploading can be done to our main CAP managment system

Time, memory % Committed Byte in use, Paging file % Usage, System Context Switches

4/14/2012 11:17:45 PM, 20.6496604287062, 6.94272741147741, 5899.05361730013

 

This is what I have so far, but I cant get it to format the right way,  any ideas on what I am missing

---> snip

 $datassource = "C:\temp\perfdata.mdb"

 

 

 

$strquery = "select * from Metrics "

 

 

 

$dsn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$datassource"

 

 

 

$objconn = New-Object system.data.oledb.oledbconnection $dsn

 

 

 

$objcmd = New-Object system.data.oledb.oledbcommand $strquery,$objconn

$objconn.Open()

 

 

 

 

 

 

 

 

$adapter = New-Object system.data.oledb.oledbdataadapter $objcmd

 

 

 

$dataset = New-Object system.data.dataset

 

 

 

$adapter.fill($dataset)

 

 

 

$out = $dataset.Tables[0].rows | % { "{0},{1},{2},{3},{4}" -f $_[0],$_[1],$_[2],$_[3],$_[4] }

 

 

 

echo "object,counter,instance,time,value" | Out-File C:\temp\perfdata.csv

 

 

 

$out | Out-File c:\temp\perdata.csv -Append

 

---> snip

 

 

 

 

 

This gives me  the following output

Terminal Services,Total Sessions,,4/17/2012 7:13:44 PM,1 which isnt right

All and any help apprecited

Top 10 Contributor
Posts 323
Microsoft MVP
Top Contributor
Reply
Thomas Lee replied on 06-28-2012 1:06 PM

I'm not much of a db guy, but it looks like you may not be getting the data you want from the database.

 

What's ion the $adapter after you fill it? And what's in $dataset.Tables[0].rows?

 

As I do not have the database I can't really duplicate but hopefully this will help you to find the issue.

Page 1 of 1 (2 items) | RSS
Copyright 2012 PowerShell.com. All rights reserved.