Solved

Export query results to xls from linux unix.

Posted on 2014-04-09
8
3,242 Views
Last Modified: 2014-04-14
Hi Techies,

I have a requirement to convert the query results as .xls and send email.

Please help me to do this from linux / unix.

let me know if you need more info.

how to do this using shell script.?
0
Comment
Question by:sakthikumar
8 Comments
 
LVL 40

Expert Comment

by:omarfarid
ID: 39990718
More info is required on what you want. What query?

Anyway, you can always produce comma separated values file (put commas between values) and then use excel to process.
0
 

Author Comment

by:sakthikumar
ID: 39990823
Please give me a sample code,  I will convert based on my query.
0
 
LVL 40

Expert Comment

by:omarfarid
ID: 39990854
You need to show me sample data first.
0
 

Author Comment

by:sakthikumar
ID: 39990887
See for eg,

Below is a query which will retrive 9 records in any DB.
I want to put these records in a CSV file or XLS and send email.
If I get the idea for the below, I can use different queries.

select owner, object_name, subobject_name, object_id, created
  from dba_objects
where rownum < 10
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:sakthikumar
ID: 39990892
sAMPLE DATA

ACL_APP      PROM_OPBE_FIN_ADJ_VIEW            450380      3/18/2014 10:06:04 AM
ACL_APP      PROM_OPBE_FIN_ADJ_STG_VIEW      450381      3/18/2014 10:06:08 AM
ACL_APP      PROM_OPBE_FIN_COLL_VIEW            450382      3/18/2014 10:06:10 AM
ACL_APP      PROM_OPBE_FIN_COLL_STG_VIEW      450383      3/18/2014 10:06:12 AM
ACL_APP     ROM_MDS_FIN_COLL_VIEW            450384      3/18/2014 10:06:15 AM
ACL_APP      GCB_INV_VIEW                                 457355      3/25/2014 12:00:29 PM
ACL_APP      GCB_FIN_STG_INV_VIEW            457356      3/25/2014 12:00:50 PM
0
 
LVL 40

Expert Comment

by:omarfarid
ID: 39990972
Are using sqlplus?

Please see example on how to generate CSV file

http://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus
0
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 500 total points
ID: 39993473
besides what omar said, if you want to run it via shell then check this out.. it illustrates how to spool the output to a csv file via shell script

replace the existing query there with your query and map your directories and you will be ready to use it..

http://www.oracle-base.com/articles/misc/oracle-shell-scripting.php#unix_and_linux_1


also after you make your script ready , you might want to send the result to your mail for that you need to append this line to the script in the end

cat result.out; uuencode result.out result.out) | mail -s "Result File" mailid@domain.com

where result.out is the the file where you do the spool and "Result File" is the subject
0
 
LVL 61

Expert Comment

by:gheist
ID: 39997873
Default CSV separator in excel is semicolon
Here is some example
https://en.wikipedia.org/wiki/MIME#Multipart_messages
Echo introducing words
Like echo mime heading (text/csv shuld be the type)
Then place your csv
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

708 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

15 Experts available now in Live!

Get 1:1 Help Now