Solved

Export query results to xls from linux unix.

Posted on 2014-04-09
8
3,355 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can't get /etc/resolv.conf to configure after reboot 8 72
bash script question (chmod) 10 54
nagios 1 21
networking details on centos 6.6 4 50
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
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…
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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

912 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

22 Experts available now in Live!

Get 1:1 Help Now