| Removing Leading single quote in excel file [message #577773] |
Wed, 20 February 2013 06:39  |
|
|
Hi All,
This package is generating excel file which contains cursor result.
In excel data is populated like below.Column name is Zip_code .My concern is how to remove that single quote from excel file.
eg:
Zip_Code:
'01234
'12567
'23432
'00234
create or replace
PACKAGE BODY PKG_MONTH_END_AUTOMATION AS
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';
-- main body
BEGIN
-- Generating Zip Files
SELECT last_day(add_months(sysdate,-1))
INTO v_last_date
FROM dual;
SELECT sysdate-1
INTO v_last_date1
FROM dual;
IF v_last_date = v_last_date1
THEN
SELECT TO_CHAR(add_months(sysdate, -1), 'MON-YYYY')
INTO v_mon
FROM dual;
ELSE
SELECT TO_CHAR(sysdate, 'MON-YYYY')
INTO v_mon
FROM dual;
END IF;
BEGIN
XLSDataFileName := v_mon||'_BANK.xls';
XLSDataFile := UTL_FILE.fopen (DirLoc, XLSDataFileName, 'W', 32767);
counter := 0;
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(var2,'fm00000') || 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: Removing Leading single quote in excel file [message #577787 is a reply to message #577785] |
Wed, 20 February 2013 08:39  |
joy_division
Messages: 4267
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
visuora@yahoo.com wrote on Wed, 20 February 2013 09:33
Because It(||'''') is used to avoid to remove the leading zero values in excel file.
If we enter 001 in excel it removes the leading zeros.
If we enter '001 in excel it keeps the leading zeros.
Wait a minute. You are asking us why your data is showing a leading quote when you specifically put it there and do not put an end quote? Think about it.
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
