special character while loading [message #596250] |
Thu, 19 September 2013 08:45 |
|
guddu_12
Messages: 227 Registered: April 2012 Location: UK
|
Senior Member |
|
|
Hi All,
I have a file which i am loading , the last column is being loaded with special character which looks like sqare shape character for all the rows.
How can i prevent it to load as it is not present in the file
data in file
Department|2|Tranport for London|Rail & Underground|ER|ER|ER|ER|ER|ER|555555555|Owner 8|8
the data once loaded for the last column on which is for above example last column value 8 will be loaded with 8 and square share character.
Any clue
|
|
|
|
|
|
|
|
|
Re: special character while loading [message #617215 is a reply to message #616555] |
Thu, 26 June 2014 03:04 |
|
guddu_12
Messages: 227 Registered: April 2012 Location: UK
|
Senior Member |
|
|
HI Michel
just a one line code
$ cat pdwpws_dos2unix.sh
/usr/bin/dos2unix $1 2>/dev/null
This scripts is being called in the external table preprocessor , which removes the character on the fly when it reads the row
CREATE TABLE EXT_LCH_TARGET
(
ATTRIBUTED_AREA VARCHAR2(250 BYTE),
BUSINESS_UNIT VARCHAR2(250 BYTE),
LINEOPS VARCHAR2(250 BYTE),
ALLOCATION VARCHAR2(250 BYTE),
TARGETMEASURETYPE VARCHAR2(250 BYTE),
LCH_TARGET_DATE VARCHAR2(250 BYTE),
INTERNAL_TARGETS VARCHAR2(250 BYTE),
EXTERNAL_TARGETS VARCHAR2(250 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY PDW_SOURCE
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
PREPROCESSOR PDW_SOURCE:'pdwpws_dos2unix.sh' ---------------used here
BADFILE PDW_BAD:'PDWLCHTargetFile.bad'
LOGFILE PDW_LOG:'PDWLCHTargetFile.log'
skip 1
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
|
|
|
|