Oracle csv export help-array parameters

Welcome Forums General PowerShell Q&A Oracle csv export help-array parameters

This topic contains 1 reply, has 2 voices, and was last updated by

10 months, 3 weeks ago.

  • Author
  • #104023

    Topics: 1
    Replies: 0
    Points: 0
    Rank: Member

    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.

  • #104038

    Topics: 13
    Replies: 4872
    Points: 1,811
    Helping HandTeam Member
    Rank: Community Hero

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

The topic ‘Oracle csv export help-array parameters’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort