?
Solved

Export Oracle view data in Text format via PS

Posted on 2014-09-05
5
Medium Priority
?
339 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 800 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 1200 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 1200 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

The following article is intended as a guide to using PowerShell as a more versatile and reliable form of application detection in SCCM.
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.
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

777 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