0

Ok so we have a manual process that runs through PL/SQL Developer to run a query and then export to csv.

I am trying to automate that process using powershell since we are working in a windows environment.

I have created two files that seems to be exact duplicates from the automated and manual process but they don't work the same so I assume I am missing some hidden characters but I can't find them or figure out how to remove them.

The most obvious example of them working differently is opening them in excel. The manual file opens in excel automatically putting each column in it's own seperate column. The automated file instead puts everything into one column.

Can anybody shed some light? I am hoping that by resolving this or at least getting some info will help with the bigger problem of it not processing correctly.

Thanks.

ex one column "rownum","year","month","batch","facility","transfer_facility","trans_dt","meter","ticket","trans_product","trans","shipper","customer","supplier","broker","origin","destination","quantity"

ex seperate column "","ROWNUM","RPT_YR","RPT_MO","BATCH_NBR","FACILITY_CD","TRANSFER_FACILITY_CD","TRANS_DT","METER_NBR","TKT_NBR","TRANS_PRODUCT_CD","TRANS_CD","SHIPPER_CD","CUSTOMER_NBR","SUPPLIER_NBR","BROKER_CD","ORIGIN_CD","DESTINATION_CD","NET_QTY"

$connectionstring = "Data Source=database;User Id=user;Password=password"
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionstring)
$command = New-Object System.Data.OracleClient.OracleCommand($query, $connection)
$connection.Open()
Write-Host -ForegroundColor Black " Opening Oracle Connection"
Start-Sleep -Seconds 2

#Getting data from oracle
Write-Host
Write-Host -ForegroundColor Black "Getting data from Oracle"
$Oracle_data=$command.ExecuteReader()
Start-Sleep -Seconds 2

if ($Oracle_data.read()){
Write-Host -ForegroundColor Green "Connection Success"
while ($Oracle_data.read()) {

#Variables for recordset
$rownum = $Oracle_data.GetDecimal(0)
$rpt_yr = $Oracle_data.GetDecimal(1)
$rpt_mo = $Oracle_data.GetDecimal(2)
$batch_nbr = $Oracle_data.GetString(3)
$facility_cd = $Oracle_data.GetString(4)
$transfer_facility_cd = $Oracle_data.GetString(5)
$trans_dt = $Oracle_data.GetDateTime(6)
$meter_nbr = $Oracle_data.GetString(7)
$tkt_nbr = $Oracle_data.GetString(8)
$trans_product_cd = $Oracle_data.GetString(9)
$trans_cd = $Oracle_data.GetString(10)
$shipper_cd = $Oracle_data.GetString(11)
$customer_nbr = $Oracle_data.GetString(12)
$supplier_nbr = $Oracle_data.GetString(13)
$broker_cd = $Oracle_data.GetString(14)
$origin_cd = $Oracle_data.GetString(15)
$destination_cd = $Oracle_data.GetString(16)
$net_qty = $Oracle_data.GetDecimal(17)

#Define new file
$filename = "Pipeline" #Get-Date -UFormat "%b%Y"
$filename = $filename + ".csv"
$fileLocation = $newdir + "\" + $filename 
$fileExists = Test-Path $fileLocation

    #Create object to hold record
    $obj = new-object psobject -prop @{
        rownum = $rownum
        year = $rpt_yr
        month = $rpt_mo
        batch = $batch_nbr
        facility = $facility_cd
        transfer_facility = $transfer_facility_cd
        trans_dt = $trans_dt
        meter = $meter_nbr
        ticket = $tkt_nbr
        trans_product = $trans_product_cd
        trans = $trans_cd
        shipper = $shipper_cd
        customer = $customer_nbr
        supplier = $supplier_nbr
        broker = $broker_cd
        origin = $origin_cd
        destination = $destination_cd
        quantity = $net_qty
    }
    $records += $obj

}

}else {
Write-Host -ForegroundColor Red " Connection Failed"
}

#Write records to file with headers
$records | Select-Object      rownum,year,month,batch,facility,transfer_facility,trans_dt,meter,ticket,trans_product,trans,shipper,customer,supplier,broker,origin,destination,quantity |
ConvertTo-Csv | 
Select -Skip 1|
Out-File $fileLocation
Improve this question
6
  • Please add some samples. Ex. how does the column that collects everything look like? Does it contain "columna,columnb,..", "columna;columnb;..", "columnacolumnb"? We need something to work with here :) A script sample will help alot too Commented Jan 29, 2013 at 16:41
  • It looks exactly like the one that opens in seperate columns Commented Jan 29, 2013 at 16:42
  • Formatting is exactly the same not the column names but they shouldn't impact how it opens I wouldn't think. Commented Jan 29, 2013 at 16:45
  • so all the info from "ex one column" is inside a single field when using your script, but not when it's run manually and saved to csv? Also "ex seperate column", do you mean column or did you mean ROW? And again, can you provide script? Commented Jan 29, 2013 at 16:49
  • Are you creating the CSV file on the Oracle database server or another machine? What program are you calling from Powershell (sqlplus?) to connect to Oracle and create the CSV file? As already asked, we also need to see the script. Commented Jan 29, 2013 at 17:30

1 Answer 1

Reset to default
1

Why are you skipping the first row(usually the headers)? Also, try using Export-CSV instead:

#Write records to file with headers
$records | Select-Object rownum, year, month, batch, facility, transfer_facility, trans_dt, meter, ticket, trans_product, trans, shipper, customer, supplier, broker, origin, destination, quantity |
Export-Csv $fileLocation -NoTypeInformation
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Honestly I don't remember why I put the skip in there but it wasn't skipping the headers. I have changed it to Export-Csv as you suggested and so far the signs look good. I'm going to run a full test and vote you up if all things go well. Thanks for your help

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.