Home » SQL & PL/SQL » SQL & PL/SQL » Removing Leading single quote in excel file (PLSQL,Windows XP)
Removing Leading single quote in excel file [message #577773] Wed, 20 February 2013 06:39 Go to next message
visuora@yahoo.com
Messages: 13
Registered: September 2008
Junior Member

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 #577775 is a reply to message #577773] Wed, 20 February 2013 06:43 Go to previous messageGo to next message
cookiemonster
Messages: 10846
Registered: September 2008
Location: Rainy Manchester
Senior Member
Please read and follow How to use [code] tags and make your code easier to read?

You appear to be explicitly adding the quote:
|| ''''

Why are you doing that?
Re: Removing Leading single quote in excel file [message #577776 is a reply to message #577773] Wed, 20 February 2013 06:48 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

Try like..

select replace (ename, '''', '') from emp 
Re: Removing Leading single quote in excel file [message #577781 is a reply to message #577776] Wed, 20 February 2013 08:14 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1950
Registered: January 2010
Senior Member
a) It is more readable when using Q literals
b) Empty string in replace is treated as NULL and replace third parameter is NULL by default.

select replace(ename, Q'[']') from emp


SY.

[Updated on: Wed, 20 February 2013 08:15]

Report message to a moderator

Re: Removing Leading single quote in excel file [message #577785 is a reply to message #577775] Wed, 20 February 2013 08:33 Go to previous messageGo to next message
visuora@yahoo.com
Messages: 13
Registered: September 2008
Junior Member

Hi cookie,

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.

Thanks
Viswanathan.S
Re: Removing Leading single quote in excel file [message #577786 is a reply to message #577781] Wed, 20 February 2013 08:34 Go to previous messageGo to next message
visuora@yahoo.com
Messages: 13
Registered: September 2008
Junior Member

Hi

Its still having quote in excel file.


Re: Removing Leading single quote in excel file [message #577787 is a reply to message #577785] Wed, 20 February 2013 08:39 Go to previous message
joy_division
Messages: 4485
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.
Previous Topic: Audit Table with Primary keys
Next Topic: oracle pl/sql
Goto Forum:
  


Current Time: Thu Jul 24 04:24:49 CDT 2014

Total time taken to generate the page: 0.07013 seconds