Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

SQL query in UNIX script - output in flat file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting SQL query in UNIX script - output in flat file
# 8  
Old 05-31-2013
One approach would be merging fields using || separated by comma ',':
Code:
select field1 || ',' || field2 || ',' || field3 from table_name;

# 9  
Old 05-31-2013
Thank you guys for all your help... Smilie

---------- Post updated at 04:19 PM ---------- Previous update was at 01:27 PM ----------

One last question:
I am able to create a csv file now, but when I open it in windows (double click)... leading zeros in certain columns are missing... may be because of text to number conversion..
How can I avoid this? Is it something that I can specify in sqlplus while fetching the records from the table and save it in the csv file?
# 10  
Old 05-31-2013
I assume you are opening your CSV in Microsoft Excel.

If yes, by default the cell format will be general number and it will strip leading zeros.

You have to select the range of cells and change the format manually in order to view the leading zeros.
# 11  
Old 05-31-2013
Thanks Yoda..
Manually I can do that....
But I need to do it as a part of my automation process... any options in SET commands in sqlplus??
# 12  
Old 05-31-2013
Quote:
Originally Posted by juzz4fun
any options in SET commands in sqlplus??
No, there is no such option.

As I already mentioned it is behavior of your spreadsheet application.

If it is just numeric with leading zero, then it will strip leading zeros. Or you might have to put a symbol or character as prefix to preserve the zeros.

But I don't think that is what you want Smilie
# 13  
Old 05-31-2013
I see...
I guess no... I do not want to put any symbols.... Smilie
Thanks anyway, Yoda.
# 14  
Old 05-31-2013
If you prefix the field with a single quote it may preserve the leading zeros. It does in Open Office, in any case, so might work in Excel.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Not able to write SQL query output in to .csv file with shell script.

I am trying to write SQL query output into a .csv file. But in the output columns are displaying in different lines instead of coming in one line. Main Code shell script: this is my code: #!/bin/bash file="db_detail.txt" . $file rm /batch/corpplan/bin/dan.csv... (6 Replies)
Discussion started by: sandeepgoli53
6 Replies

2. Shell Programming and Scripting

Shell script appending output of sql query

I am writing the following script to create the file v_out.txt. sqlplus -s /nolog << EOF CONNECT scott/tiger@orcl; whenever sqlerror exit sql.sqlcode; set newpage 0; SET PAGESIZE 0; SET ECHO OFF; SET FEEDBACK OFF; SET HEADING OFF; SET VERIFY OFF; SET LINESIZE 100; set tab off; set... (7 Replies)
Discussion started by: itzkashi
7 Replies

3. Shell Programming and Scripting

SQL Query in Shell Script output formatting

Hi All, #!/bin/ksh call_sql () { sql=$1 sqlplus -s $sqlparam_sieb <<EOF SET ECHO OFF; SET NEWPAGE NONE; SET SQLBL OFF; SET VERIFY OFF; SET LINESIZE 2000; SET... (2 Replies)
Discussion started by: Kevin Tivoli
2 Replies

4. UNIX for Dummies Questions & Answers

Read a flat file, evaluate and create output. UNIX SCRIPT.

Hi all, I have a flat file as below; 470423495|1||TSA-A000073800||1|||1 471423495|1||TSA-A000073800||5|||5 472423495|1||TSA-A000073800||2|||7 473423495|1||TSA-A000073800||3|||3 I like to create a Unix script. The script have to valuate the last two columns, if the values are... (4 Replies)
Discussion started by: mrreds
4 Replies

5. Shell Programming and Scripting

How to store the sql query output into txt file?

Hi I want ot save SQL query result in one txt file. for that i have written one code line sqlplus -s $dbstring @/usr/local/bin/sched/nightly_Cronjob/exec_123.sql >> /usr/local/bin/sched/nightly_Cronjob/result.txt but it is not working . database : Oracle so please advice me how can i... (7 Replies)
Discussion started by: Himanshu_soni
7 Replies

6. UNIX for Advanced & Expert Users

Output the SQL Query result to a File

Hello Guys, This message is somewhat relates with last thread. But I need to re-write thing. I start over a little. I am stuck now and need your help. Here is my script- #! /bin/ksh export ORACLE_HOME=/opt/oracle/app/oracle/product/9.2 /opt/oracle/app/oracle/product/9.2/bin/sqlplus -s... (5 Replies)
Discussion started by: thepurple
5 Replies

7. Shell Programming and Scripting

How to use sql data file in unix csv file as input to an sql query from shell

Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s... (2 Replies)
Discussion started by: Nareshp
2 Replies

8. Shell Programming and Scripting

redirecting sql query output to a file

Hi, I am executing sql files in my unix shell script. Now i want to find whether its a success or a failure record and redirect the success or failure to the respective files. meaning. success records to success.log file failure record to failure.log file. As of now i am doing like... (1 Reply)
Discussion started by: sailaja_80
1 Replies

9. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

10. Shell Programming and Scripting

need help in reading a output of a sql query in unix script

i'm used a sql query in a unix script to get the information from table. but unable to extract the output which i need. Any help with logic will be greatly appreciated. my sql query provide output some thing like this - col1 col2 count ---- ---- ------ A B 10 c D 6 e... (8 Replies)
Discussion started by: pharos467
8 Replies
Login or Register to Ask a Question