Need help on SQL loader record delimiter

Hi Team,

I have an requirement , I need to load the below data in the my table
'101','30-MAR-14','Vivek','Y'|                      
'101','30-MAR-14','Sanjay','Y'|                      
'101','30-MAR-14','Sanjay'Kumar,'Y'      

               
Below  is the control file which I have created
LOAD DATA
INFILE 'D:\Data-Load\EMP-DATA.csv'
TRUNCATE
INTO TABLE EMPDATA_TMP
FIELDS TERMINATED BY ','  OPTIONALLY ENCLOSED BY  "'"
(
EMPID integer,
DOB DATE,
NAME CHAR,
MARRIED CHAR
)


Iam getting error for date format not being recognised and the pipe in the end. Is there an work around for it.
sam_2012Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
sam_2012Author Commented:
My Database date format is DD-MM-YY.
0
 
sam_2012Author Commented:
I tried the below it did not work
DOB DATE "TO_DATE(TO_CHAR(:DOB,'DD-MON-YY'),'DD-MM-RR')",
0
 
johnsoneSenior Oracle DBACommented:
Given your 3 rows of sample data, this worked for me:
LOAD DATA
INFILE * 
TRUNCATE
INTO TABLE EMPDATA_TMP
FIELDS TERMINATED BY ','
(
EMPID optionally enclosed by "'",
DOB DATE 'DD-MON-YY' optionally enclosed by "'",
NAME optionally enclosed by "'",
MARRIED CHAR  optionally enclosed by "|" "TRIM(REPLACE(REPLACE(:MARRIED,''''),'|'))"
)
BEGINDATA
'101','30-MAR-14','Vivek','Y'|                      
'101','30-MAR-14','Sanjay','Y'|                      
'101','30-MAR-14','Sanjay Kumar','Y' 

Open in new window

Be careful to cut an paste that properly.  There are lots of single and double quotes in there.  Don't mix them up.
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sam_2012Author Commented:
Hi Johnsone ,
Can you explain me the below
NAME optionally enclosed by "'", -- not mentioned the pipe
MARRIED CHAR  optionally enclosed by "|" "TRIM(REPLACE(REPLACE(:MARRIED,''''),'|'))" -- not able to follow what is happening here
0
 
johnsoneSenior Oracle DBACommented:
According to the question and the sample data, the pipe only appears at the end of the record.  Outside the delimiters.  So, what you need to do is fool SQL*Loader to think that the pipe is meaningful on the last column.  Then use REPLACE to get rid of the enclosing characters.

Since there is no | in the NAME field, it isn't mentioned there.  If there can be pipes in all the fields, that changes things and I would really look at a script the processes the file to remove the pipes before you try to load it.  You could do that anyway and then you wouldn't have to deal with making SQL*Loader jump through hoops.
0
 
sam_2012Author Commented:
awesomne. Thanks a lot
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.