Pulling data out of access and formatting it into excel

by Apr 26, 2012

Hello all

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



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:tempperfdata.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










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




$dataset = New-Object system.data.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:tempperfdata.csv




$out | Out-File c:tempperdata.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