Solved

Export Oracle view data in Text format via PS

Posted on 2014-09-05
5
316 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
  • 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 39

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 39

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Microsoft Windows Server Update Service (WSUS) is free for everyone, but it lacks of some desirable features like send an e-mail to the administrator with the status of all computers on the WSUS server. This article is based on my PowerShell script …
In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
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 the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now