Solved

Export query results to xls from linux unix.

Posted on 2014-04-09
8
4,012 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
[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
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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
 

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 62

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

Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

Question has a verified solution.

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

Why Shell Scripting? Shell scripting is a powerful method of accessing UNIX systems and it is very flexible. Shell scripts are required when we want to execute a sequence of commands in Unix flavored operating systems. “Shell” is the command line i…
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…
In a previous video, we went over how to export a DynamoDB table into Amazon S3.  In this video, we show how to load the export from S3 into a DynamoDB table.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
Suggested Courses

628 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