Solved

Export Oracle view data in Text format via PS

Posted on 2014-09-05
5
327 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Windows 10 came with  a lot of built in applications, Some organisations leave them there, some will control them using GPO's. This Article is useful for those who do not want to have any applications in their image (example:me).
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

707 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