Home » SQL & PL/SQL » SQL & PL/SQL » EXCEL LEADING ZERO (PLSQL,Windows XP)
EXCEL LEADING ZERO [message #576563] Wed, 06 February 2013 04:37 Go to next message
visuora@yahoo.com
Messages: 13
Registered: September 2008
Junior Member

Hi Team,

Column name: Zip_Code

This column contains leading zero. But writing into excel file it is stored without leading zero.

Please advise me how to store with leading zero also for the column Zip_Code.


PROCEDURE PROC_ZIP_CODE_MONTHEND (directoryOrPath IN VARCHAR2 default 'LOC_PHASE1_WHOUSE_SALES_ADMIN')
AS


--initialize variables here
sqlStatement VARCHAR2 (3000);
counter NUMBER := 0;
var1 VARCHAR2 (200);
var2 VARCHAR2 (200);
var3 VARCHAR2 (200);
var4 VARCHAR2 (200);
var5 VARCHAR2 (200);
var6 VARCHAR2 (200);
var7 VARCHAR2 (200);
var8 VARCHAR2 (200);
var9 VARCHAR2 (200);
var10 VARCHAR2 (200);
var11 VARCHAR2 (200);
var12 VARCHAR2 (200);
var13 VARCHAR2 (200);
var14 VARCHAR2 (200);
var15 VARCHAR2 (200);
var16 VARCHAR2 (200);
var17 VARCHAR2 (200);
var18 VARCHAR2 (200);
var19 VARCHAR2 (200);
v_last_date DATE;
v_last_date1 DATE;
v_mon VARCHAR2 (10);
sErrCode VARCHAR2 (200);
sErrMsg VARCHAR2 (255);
DLM VARCHAR2 (10) := '" "';

XLSDataFile UTL_FILE.file_type;
XLSDataFileName VARCHAR2 (50);
fblocksize NUMBER (10, 2) := 0;
delimitStr VARCHAR2 (1) := ',';
newline VARCHAR2 (1) := CHR (13);


fexists BOOLEAN;
flength NUMBER;
DirLoc VARCHAR2 (100) := '/opt/oracled/feeds/srdev01/outgoing';
RunDate VARCHAR (15) ;
LoadDate VARCHAR (15) ;

CURSOR CUR_ZIP_BANK
IS
SELECT B.CHANNEL_CODE,
A.ZIP_CODE,
NULL CITY,
C.STATE_CODE,
B.TERRITORY_NAME,
NULL WHOLESALER_NAME,
NULL XID,
NULL SPIKE_ID,
NULL ARMS_TERR_ID,
B.TERRITORY_NUMBER
FROM salesadmin.FINAL_zip_assign a,
saas.dim_wholesaler_territory b,
salesadmin.zipcode_master c
WHERE a.TERRITORY_KEY = b.WHOLESALER_TERRITORY_KEY
AND a.ZIP_CODE = c.ZIPCODE(+)
AND a.CHANNEL_CODE = 'BANK';

BEGIN
XLSDataFileName := v_mon||'_BANK.xls';
XLSDataFile := UTL_FILE.fopen (DirLoc, XLSDataFileName, 'W', 32767);


UTL_FILE.PUT_LINE (
XLSDataFile,
'"CHANNEL_CODE'
|| DLM
|| 'ZIP_CODE'
|| DLM
|| 'CITY'
|| DLM
|| 'STATE_CODE'
|| DLM
|| 'TERRITORY_NAME'
|| DLM
|| 'WHOLESALER_NAME'
|| DLM
|| 'XID'
|| DLM
|| 'SPIKE_ID'
|| DLM
|| 'ARMS_TERR_ID'
|| DLM
|| 'TERRITORY_NUMBER"');

OPEN CUR_ZIP_BANK;

LOOP
FETCH CUR_ZIP_BANK
INTO var1,
var2,
var3,
var4,
var5,
var6,
var7,
var8,
var9,
var10;

EXIT WHEN CUR_ZIP_BANK%NOTFOUND;

counter := counter + 1;

--While Creating File, delimistr and newline is added at the end in order to generate correct file
UTL_FILE.PUT_LINE (
XLSDataFile,
'"'
|| var1
|| DLM
|| to_char(lpad(var2,5,0))
|| DLM
|| var3
|| DLM
|| var4
|| DLM
|| var5
|| DLM
|| var6
|| DLM
|| var7
|| DLM
|| var8
|| DLM
|| var9
|| DLM
|| var10
|| '"'
/* || Newline*/)
;
END LOOP;

CLOSE CUR_ZIP_BANK;

UTL_FILE.FCLOSE (XLSDataFile);
DBMS_OUTPUT.put_line ('No OF Records FOR ALL ==' || counter);
DBMS_OUTPUT.put_line ('******ENDED*****');
EXCEPTION
WHEN OTHERS THEN
sErrCode := SQLCODE;
sErrMsg := substr(SQLERRM, 1,255);

RAISE_APPLICATION_ERROR(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
Re: EXCEL LEADING ZERO [message #576567 is a reply to message #576563] Wed, 06 February 2013 04:44 Go to previous messageGo to next message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Mon, 24 December 2012 13:26
From one of your previous topics:

Michel Cadot wrote on Sat, 18 August 2012 11:33
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...


In addition, FEEDBACK to those that help you, we are still waiting for you in your previous topics.
Me, I can no more help you if I never have feedback telling if the time I spent for you was useful or just a waste of time.

...


Quote:
This column contains leading zero. But writing into excel file it is stored without leading zero.


1/ This is a FAQ
2/ This Excel which transforms the data NOT Oracle

Regards
Michel

[Updated on: Wed, 06 February 2013 04:45]

Report message to a moderator

Re: EXCEL LEADING ZERO [message #576571 is a reply to message #576563] Wed, 06 February 2013 05:05 Go to previous messageGo to next message
visuora@yahoo.com
Messages: 13
Registered: September 2008
Junior Member

CREATE TABLE "SALESADMIN"."FINAL_ZIP_ASSIGN"
( "TERRITORY_KEY" NUMBER NOT NULL ENABLE,
"ZIP_CODE" VARCHAR2(10 BYTE) NOT NULL ENABLE,
"EFFECTIVE_DATE" DATE,
"LASTUPDATEDBY" VARCHAR2(10 BYTE),
"LASTUPDATEDTIME" DATE,
"INCREMENT_FLAG" CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE,
"TERRITORY_NUMBER" NUMBER,
"CHANNEL_CODE" VARCHAR2(15 BYTE),
"TERRITORY_ZONE" VARCHAR2(100 BYTE)



Insert into FINAL_ZIP_ASSIGN (CHANNEL_CODE,EFFECTIVE_DATE,INCREMENT_FLAG,LASTUPDATEDBY,LASTUPDATEDTIME,TERRITORY_KEY,TERRITORY_NUMBER,TERRITORY_ZONE,ZIP_CODE) values ('WIRE-WACH/RJ',to_timestamp('31-MAR-11','DD-MON-RR HH.MI.SSXFF AM'),'1','X132689',to_timestamp('31-MAR-11','DD-MON-RR HH.MI.SSXFF AM'),397086,1530,null,'58202');
Insert into FINAL_ZIP_ASSIGN (CHANNEL_CODE,EFFECTIVE_DATE,INCREMENT_FLAG,LASTUPDATEDBY,LASTUPDATEDTIME,TERRITORY_KEY,TERRITORY_NUMBER,TERRITORY_ZONE,ZIP_CODE) values ('WIRE-WACH/RJ',to_timestamp('31-MAR-11','DD-MON-RR HH.MI.SSXFF AM'),'1','X132689',to_timestamp('31-MAR-11','DD-MON-RR HH.MI.SSXFF AM'),397086,1530,null,'57426');
Insert into FINAL_ZIP_ASSIGN (CHANNEL_CODE,EFFECTIVE_DATE,INCREMENT_FLAG,LASTUPDATEDBY,LASTUPDATEDTIME,TERRITORY_KEY,TERRITORY_NUMBER,TERRITORY_ZONE,ZIP_CODE) values ('WIRE-WACH/RJ',to_timestamp('31-MAR-11','DD-MON-RR HH.MI.SSXFF AM'),'1','X132689',to_timestamp('31-MAR-11','DD-MON-RR HH.MI.SSXFF AM'),397086,1530,null,'57311');
Insert into FINAL_ZIP_ASSIGN (CHANNEL_CODE,EFFECTIVE_DATE,INCREMENT_FLAG,LASTUPDATEDBY,LASTUPDATEDTIME,TERRITORY_KEY,TERRITORY_NUMBER,TERRITORY_ZONE,ZIP_CODE) values ('WIRE-WACH/RJ',to_timestamp('31-MAR-11','DD-MON-RR HH.MI.SSXFF AM'),'1','X132689',to_timestamp('31-MAR-11','DD-MON-RR HH.MI.SSXFF AM'),397081,1528,null,'88310');
Insert into FINAL_ZIP_ASSIGN (CHANNEL_CODE,EFFECTIVE_DATE,INCREMENT_FLAG,LASTUPDATEDBY,LASTUPDATEDTIME,TERRITORY_KEY,TERRITORY_NUMBER,TERRITORY_ZONE,ZIP_CODE) values ('WIRE-WACH/RJ',to_timestamp('31-MAR-11','DD-MON-RR HH.MI.SSXFF AM'),'1','X132689',to_timestamp('31-MAR-11','DD-MON-RR HH.MI.SSXFF AM'),397081,1528,null,'88324');
Insert into FINAL_ZIP_ASSIGN (CHANNEL_CODE,EFFECTIVE_DATE,INCREMENT_FLAG,LASTUPDATEDBY,LASTUPDATEDTIME,TERRITORY_KEY,TERRITORY_NUMBER,TERRITORY_ZONE,ZIP_CODE) values ('WIRE-WACH/RJ',to_timestamp('31-MAR-11','DD-MON-RR HH.MI.SSXFF AM'),'1','X132689',to_timestamp('31-MAR-11','DD-MON-RR HH.MI.SSXFF AM'),397081,1528,null,'87046');
Re: EXCEL LEADING ZERO [message #576576 is a reply to message #576571] Wed, 06 February 2013 05:55 Go to previous messageGo to next message
Michel Cadot
Messages: 58525
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And?

Regards
Michel
Re: EXCEL LEADING ZERO [message #576627 is a reply to message #576576] Wed, 06 February 2013 12:43 Go to previous message
Bill B
Messages: 1068
Registered: December 2004
Senior Member
This is a known problem when excel brings in a csv file column that is all numeric like zip codes then it will always strip off the leading zeros. When I do is to use an unknown extension when importing into excel. This will cause it to go through the import wizard and you can specify the column as text.
Previous Topic: How to find out Date Format
Next Topic: Trouble with reqexp_substr and connect by
Goto Forum:
  


Current Time: Thu Jul 24 05:59:15 CDT 2014

Total time taken to generate the page: 0.22095 seconds