store the table data in excel file

Shell Programming and Scripting
1

Hello -

I have a below table and i want to extract the data into excel sheet and send to different location.

Here is the table structure...

SQL> desc t_i1_exportdocs
Name Null? Type
----------------------------------------- -------- -----------------
Delivery # VARCHAR2(50)
REGION VARCHAR2(50)
Proforma Invoice VARCHAR2(50)
SONUM VARCHAR2(50)
Export Docs print date VARCHAR2(50)
Export Docs print time VARCHAR2(50)
SLD2NAME VARCHAR2(50)
Freight Fwdr (name) VARCHAR2(50)
Shipto Ctry VARCHAR2(50)
Shipping Point VARCHAR2(50)
Extract Date VARCHAR2(50)

SQL>

Here is the code i am using shell script. But the excel file content is not in correct order..

Each record is not in the same line in excel sheet... How can i display the entire record in the same in excel sheet?

${ORACLE_HOME}/bin/sqlplus -s ${ORA_USER}/${ORA_PASSW}@${ORACLE_SID} << EOF > ${FTP_TO}/T_i1_exportdocs_b.csv

set pages 0
set echo off
set ver off
set feed off

SELECT
'Delivery #'||','||'REGION'||','||'Proforma Invoice'||','||'SONUM'||','||'Export Docs print date'||','||'Export Docs print time'||','||'SLD2NAME'||','||'Frei
ght Fwdr (name)'||','||'Shipto Ctry'||','||'Shipping Point'||','||'Extract Date' from dual;

select
"Delivery #"||','||
REGION||','||
"Proforma Invoice"||','||
SONUM||','||
"Export Docs print date"||','||
"Export Docs print time"||','||
SLD2NAME||','||
"Freight Fwdr (name)"||','||
"Shipto Ctry"||','||
"Shipping Point"||','||
"Extract Date"
FROM xyz;

exit;
EOF

uuencode ${FTP_TO}/T_i1_exportdocs_b.csv ${FTP_TO}/T_i1_exportdocs_b.csv | mailx -s " excel data for t_i1_exportdocs B box" \ xx@comp.com

Here is the excel file content...

Delivery #	REGION	Proforma Invoice	SONUM	Export Docs print date	Export Docs prin		
t time	SLD2NAME	Freight Fwdr (name)	Shipto Ctry	Shipping Point	Extract Date		
							
99572613	EMEA	2900000263	11297854	2/26/2009	16:37:42	 Int'l Sales Ltd.	
	UPS  SOLUTIONS	DE	CF3N	2/27/2009			
							
99568129	CALA	2900001376	11317104	2/26/2009	13:04:01	TALLARD TECHNOLOGIES I	
NCORPORATED	CONTINENTAL FREIGHT FORWARDING INC	US	CF3N	2/27/2009			
							
99570027	CALA	2900001377	11317104	2/26/2009	13:04:15	TALLARD TECHNOLOGIES I	
NCORPORATED	CONTINENTAL FREIGHT FORWARDING INC	US	CF3N	2/27/2009
2

If you are going to export database data to import to an Excel spreadsheet, it is recommended to create a CSV file (fields delimited by some value, such as ','). Excel should have no problem reading that.

3

Hello, I created the file with csv file format. But each reocrd is splitted into two lines... Please see my posting above... Any help is appreciated...

4

You may want to add

SET LINE n

Where n is the max length of the line a record will be.

From the SQL*Plus doc:

SET LINESIZE sets the total number of characters that SQL*Plus displays on one line before beginning a new line.

If LINESIZE is too small, columns that cannot fit next to each other are put on separate lines.

5

It works after setting linesize. Thank you so much for your help.

6

The excel file works good. But one issue... I have windows directory mounted in unixbox. I am moving the excel files from unix to windows directory. When i open the excel file in windows directory, it is not showing as excel format. The record is showing just comma delimited line. But at the same time, if i ftp the excel file to my local machine, format looks good. Any thoughts...

7

This sounds like a newline/carriage return issue.

You have to convert the file from Unix to DOS format.

You can do it on the Unix side with the ux2dos utility or an awk statement
like this

awk '{sub(/$/,"\r");print} < Unixcvs.xls  > Doscvs.xls
8

Hello, Thanks for your input.

Here is the code i tried now.

Here is the windows location

/odsimp/supply_chain/"Global Order Mgmt"/"US Exports"/EXPORT_LOG_SQL/yy.xls

Here is the windows file content...

9

Any help is highly appreicated...

10

I think I know what the problem is. It is your file extension name. You have to make it "csv", not "xls". These files are not really Excel files, so I think Excel is getting confused because it is trying to treat them as such.

Rename T_i1_exportdocs_b.xls to T_i1_exportdocs_b.csv
Rename yy.xls to yy.csv

But you should still run the awk since you are going from Unix to Windows.

11

SFNYC - Thank you. It works great. Perfect...

Another question, Is it possible to store two different set of data in the same excel file. One set of data should go into one tab. Another set of data should go to another tab in the same excel.. Please let me know. Thanks again and appreciate your help..

12

SFNYC - Never mind. Business agreed to put in individual files... thanks