Oracle csv export help-array parameters

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 = @"

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", 
disabled as "disabled_text", 
sourcesysid, ownersysid, externalrefid, siteid, orgid, 
autowogen as "autowogen_text", 
itemsetid, conditioncode, 
groupname, assettype, usage, status, 
mainthierchy as "mainthierchy_text", 
moved as "moved_text", 
assetuid, langcode, toolrate, 
itemtype, ancestor, sendersysid, shiftnum, toolcontrolaccount, 
hasld as "hasld_text", 
direction, startmeasure, 
islinear as "islinear_text", 
enddescription, startdescription, lrm, defaultrepfacsiteid, defaultrepfac, 
returnedtovendor as "returnedtovendor_text", 
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")
$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)
return $ds.Tables[0] | Export-Csv C:\temp\ASSET\$site.csv -Delimiter "|" -NoTypeInformation


let me known if you can see my fubar mistake…

Much appreciated.

That’s because you’re kind of Franken-coding.

You could fix this by adding -Append to Export-Csv. By default, the command starts a fresh file every time, so you’re getting your output - it’s just being overwritten each time.

A better approach would be to build a proper function that output objects containing your data. Then just run:

Get-Whatever | Export-Csv

To let those objects go into a CSV file. This is a deeply important design pattern in PowerShell; the more you don’t follow it, the more difficult and annoying things will become. See, “Learn PowerShell Scripting in a Month of Lunches” and “The PowerShell Scripting & Toolmaking Book.”