Well Thanks for all the help so far for the newb on powershell I have learned alot from this site…Thanks a ton.
Well i still have stuff to learn it seems.
taking a few snips of code I have been able to get this far.
my script has two parts, an csv file with a single column [site] which is the parameter for the oracle sql script and the name of the output file. problem is the script runs fine…but it does not produce the output file till the last row of the csv
here is my code
$SiteArray=import-csv “C:\temp\SITE_LIST.csv”
$SiteArray | ForEach-Object{
$site = $_.SITE
Write-Host "The name of the object is:" $site
Get-ChildItem -Path C:\temp\ASSET -Include $site.csv -File -Recurse | foreach { $_.Delete()}
Add-Type -Path C:\app\orcladmin\product\12.2.0\dbhome_1\ODP.NET\managed\common\Oracle.ManagedDataAccess.dll
$username = "user"
$password = "password"
$datasource = "database"
$connectionString = "User Id=$username;Password=$password;Data Source=$datasource"
$query = @"
select
assetnum,
parent, serialnum, assettag, location, description, vendor, failurecode, manufacturer,
purchaseprice, replacecost, installdate, warrantyexpdate, totalcost, ytdcost,
budgetcost, calnum,
isrunning as "isrunning_text",
itemnum, unchargedcost, totunchargedcost, totdowntime,
statusdate, changedate, changeby,
eq1, eq2, eq3, eq4, eq5, eq6, eq7, eq8, eq9, eq10, eq11, eq12, eq23, eq24,
priority, invcost, glaccount, rotsuspacct,
children as "children_text",
binnum,
disabled as "disabled_text",
classstructureid,
sourcesysid, ownersysid, externalrefid, siteid, orgid,
autowogen as "autowogen_text",
itemsetid, conditioncode,
groupname, assettype, usage, status,
mainthierchy as "mainthierchy_text",
assetid,
moved as "moved_text",
assetuid, langcode, toolrate,
itemtype, ancestor, sendersysid, shiftnum, toolcontrolaccount,
hasld as "hasld_text",
direction, startmeasure,
endmeasure,
islinear as "islinear_text",
enddescription, startdescription, lrm, defaultrepfacsiteid, defaultrepfac,
returnedtovendor as "returnedtovendor_text",
tloamhash,
tloampartition as "tloampartition_text",
pluscassetdept, pluscclass, pluscduedate, plusciscondesc,
plusciscontam as "plusciscontam_text",
pluscisinhousecal as "pluscisinhousecal_text ",
pluscismte as "pluscismte_text",
pluscismteclass, pluscloopnum, pluscmodelnum, pluscoprgeeu,
pluscoprgefrom, pluscoprgeto, pluscphyloc,
pluscpmextdate as "pluscpmextdate_text",
pluscsolution as "pluscsolution_text",
pluscsumdir, pluscsumeu, pluscsumread,
pluscsumspan, pluscsumurv, pluscvendor,
iscalibration as "iscalibration_text",
templateid, plusclploc, pluspcustomer, pluspcustchacct, pluspcostcenter,
sdx_asbestos, sdx_ascondition1, sdx_busimpact, sdx_criticality, sdx_criticalitydesc, sdx_department, sdx_jpsize, sdx_legacyasset,
sdx_model, sdx_permitreqd, sdx_returnedvendor, sdx_type, sdx_vicinity, saddresscode, tloamlicensecompliance, tloamdispcharge, tloamdispdate,
tloamdisprecipient, tloamdispremark, tloamdispreq, tloamdisptype, tloamdispvalue, tloamrefreshdate, tloamrefreshplandate, tloamrefreshremarks,
tloamrefreshstatus, sdx_age, sdx_lce, moblastauditeddate, moblastauditedby, mobrfidtag, sdx_assetrisk, sdx_assetriskdesc,
tloamrefreshcycle, sdxlifecycleasset, sdx_yearsremain
from maximo.asset
where siteid='$site'
"@
$connection = New-Object Oracle.ManagedDataAccess.Client.OracleConnection("$connectionString")
$connection.open()
$command = New-Object Oracle.ManagedDataAccess.Client.OracleCommand
$command.Connection = $connection
$command.CommandText = $query
$ds = New-Object system.Data.DataSet
$da = New-Object Oracle.ManagedDataAccess.Client.OracleDataAdapter($command)
[void]$da.fill($ds)
return $ds.Tables[0] | Export-Csv C:\temp\ASSET\$site.csv -Delimiter "|" -NoTypeInformation
$connection.Close()
}
let me known if you can see my fubar mistake…
Much appreciated.