On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Tuesday, June 24, 2008

Retain Leading Zeros in Excel


To keep the leading zeros when taking data into Excel, do three things:

1) pad with zeros,
2) concatenate with an equal sign
3) set Entmap Off for that particular column
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
SET TERM OFF

COLUMN Empno ENTMAP OFF

SPOOL Employee.xls

Select '="'||LPad(EmpNo,8,0)||'"'
As
EmpNo,
--------------------------------
EName
From Emp;

SPOOL OFF
SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT OFF
SET TERM ON
See Oracle documentation for further detail:
Generating HTML Reports from SQL*Plus

3 comments: