Solved

Export Oracle view data in Text format via PS

Posted on 2014-09-05
5
325 Views
Last Modified: 2014-09-28
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),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)"

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()

Open in new window


footech  assisted me on the previous post,
http://www.experts-exchange.com/Programming/Languages/Scripting/Powershell/Q_28426936.html
0
Comment
Question by:ipsec600
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 200 total points
ID: 40306619
Try replacing this:
$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) Qry1 FROM Int_Ow.View_It"

Open in new window

0
 
LVL 40

Accepted Solution

by:
footech earned 300 total points
ID: 40306812
For ii., if you are referring to the quotation marks that surround each field, that happens whenever you use Export-CSV.  It makes it so that a field can actually contain the delimiter in use like ",".

To get around that, you have to essentially create your own custom-formatted .CSV.  For example instead of using
| export-csv C:\administrator\Desktop\exportdata.txt -notype

Open in new window

at the end use the following:
| ConvertTo-CSV -notype | ForEach { $_ -replace '"' } | Out-File C:\administrator\Desktop\exportdata.txt

Open in new window

0
 

Author Comment

by:ipsec600
ID: 40339420
Apology for replying late, actually due to sudden personal ground I was away for two weeks and once again apologies for replying late. Hope you guys will understand.

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\exportdata.txt -notype

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\exportdata.txt

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.
0
 
LVL 40

Assisted Solution

by:footech
footech earned 300 total points
ID: 40342728
Only two problems can occur by removing the quotes as I showed (that I'm aware of).
1) there were quotes within a field that were removed along with the quotes that surround a field
2) there are commas (or other character matching the delimiter in use) within a field

I have used the code I showed a number of times and haven't had any issue with later importing and reusing the data, but I also know the data in my fields didn't have any problem characters.  I can't imagine how it would work when you do a find and replace of the quotation marks after using Export-CSV, and not the other way.  The only suggestion I have would be to examine the output file and look for differences.  You can use a tool like ExamDiff to help with that.  I wouldn't think it would be an encoding problem but you could try using the -Encoding parmeter with Out-File and set it to "ascii" like
| Out-File C:\administrator\Desktop\exportdata.txt -Encoding ascii

Open in new window

0
 

Author Closing Comment

by:ipsec600
ID: 40349212
Awesome!!! Everything is working perfectly, thank you  footech  and MikeOM_DBA for your advise.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Synchronize a new Active Directory domain with an existing Office 365 tenant
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question