ipsec600
asked on
Export Oracle view data in Text format via PS
Hi Experts,
I am able to read Oracle view data via Power Shell, and able to export data in TXT format as well but the problem is below:
I . Query string is showing in the first line of the text file, need assistance to remove the query string from the first line of the text file.
"RPAD(TO_CHAR(LAST_NAME),3 0)|| RPAD(TO_CHAR(FIRST_NAME),3 0)||RPAD(T O_CHAR(LOC ATION),50) || RPAD(TO_CHAR(START_DATE),1 0)|| RPAD(TO_CHAR(POSITION_TYPE ),10)||RPA D(TO_CHAR( EMPLOYEE_S TATUS),10) || RPAD(TO_CHAR(TITLE),30)|| RPAD(' ',24)|| RPAD(TO_CHAR(END_DATE),10) ||RPAD(TO_ CHAR(COMPA NY_SERVICE _DATE),10) "
ii. In the text file output starting with quotation mark “ and also ending with quotation mark “, need to remove the quotation mark from the text file as well. Please advise.
I am using the following code.
footech assisted me on the previous post,
https://www.experts-exchange.com/questions/28426936/Export-Oracle-view-data-in-CSV-format-via-PS.html
I am able to read Oracle view data via Power Shell, and able to export data in TXT format as well but the problem is below:
I . Query string is showing in the first line of the text file, need assistance to remove the query string from the first line of the text file.
"RPAD(TO_CHAR(LAST_NAME),3
ii. In the text file output starting with quotation mark “ and also ending with quotation mark “, need to remove the quotation mark from the text file as well. Please advise.
I am using the following code.
$connectionString = "Data Source=db.bl.com;User Id=oracleuser;Password=P@ssW0rd;Integrated Security=no"
$queryString = "select RPAD(TO_CHAR(LAST_NAME),30)|| RPAD(TO_CHAR(FIRST_NAME),30)||RPAD(TO_CHAR(LOCATION),50)|| RPAD(TO_CHAR(START_DATE),10)|| RPAD(TO_CHAR(POSITION_TYPE),10)||RPAD(TO_CHAR(EMPLOYEE_STATUS),10)|| RPAD(TO_CHAR(TITLE),30)|| RPAD(' ',24)|| RPAD(TO_CHAR(END_DATE),10)||RPAD(TO_CHAR(COMPANY_SERVICE_DATE),10)||from int_ow.view_IT"
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | Out-Null
$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
$connection.Open()
$users = $command.ExecuteReader()
$Counter = $users.FieldCount
@(while ($users.Read()) {
$temp = New-Object PsObject
for ($i = 0; $i -lt $Counter; $i++) {
$temp | Add-Member -MemberType NoteProperty -Name $users.GetName($i) -Value $users.GetValue($i)
}
$temp
}) | export-csv C:\administrator\Desktop\exportdata.txt -notype
$connection.Close()
footech assisted me on the previous post,
https://www.experts-exchange.com/questions/28426936/Export-Oracle-view-data-in-CSV-format-via-PS.html
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Awesome!!! Everything is working perfectly, thank you footech and MikeOM_DBA for your advise.
ASKER
Thank you so much for your advise, I have tested both the above steps and below is the observation:
Hi MikeOM_DBA, it works but qry1 is showing in the first line of the text output but good part is that entire command line is not showing in the text file.
Is there any way to remove the qry1 from the text file.
Hi footech, it also works and now it removes quotation mark " from the text file, but the issue is while i try to use that text file to read data receiving error "One or more errors occurred during processing of command", For that is there any way to remove the quotation mark in different way.
While I export text file with following parameter
| export-csv C:\administrator\Desktop\e
and find and replace the quotation mark, then it works perfectly. but for the same text file while I use the following command
| ConvertTo-CSV -notype | ForEach { $_ -replace '"' } | Out-File C:\administrator\Desktop\e
I received the text file in the desired format but while i try to use that text file to read data receiving error "One or more errors occurred during processing of command".
Thanks you guys for helping me.