?
Solved

Export Oracle view data in Text format via PS

Posted on 2014-09-05
5
Medium Priority
?
352 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 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 41

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 41

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In previous parts of this Nano Server deployment series, we learned how to create, deploy and configure Nano Server as a Hyper-V host. In this part, we will look for a clustering option. We will create a Hyper-V cluster of 3 Nano Server host nodes w…
Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
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.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Suggested Courses

578 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