Oracle csv export help-array parameters

This topic contains 1 reply, has 2 voices, and was last updated by  Don Jones 1 week, 5 days ago.

  • Author
    Posts
  • #104023

    John Harper
    Participant

    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.

  • #104038

    Don Jones
    Keymaster

    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."

You must be logged in to reply to this topic.