Solved

Need help on  oracle data load tool -- Challenging Question

Posted on 2016-10-04
7
64 Views
Last Modified: 2016-10-07
Hi Team,

I have an file the data in an input file called as emaidata.txt

"JobID"|"BatchID"|"SubID"|"EmailAddr"|"FirstName"|"LastName"|"pincode"|"SendDate"
"2796921"|"1442"|"151719024"|"RAJU@gmail.com"|""|""|""|"2016-07-31 02:40:37.697000000"
"2796926"|"487"|"75326115"|"CHANDRU@VINTEL.EDU"|""|""|""|"2016-07-31 01:39:52.070000000"
"2797764"|"1065"|"33696904"|"RAMCHAMDRA@GMAIL.COM"|""|""|""|"2016-07-29 20:34:14.113000000"
"2797764"|"5749"|"40004462"|"DSOUZA@VINTEL.edu"|""|""|""|"2016-07-30 14:52:42.443000000"
"2797764"|"13077"|"108620732"|"NAGAVENU@GMAIL.com"|""|""|""|"2016-07-31 15:19:48.123000000"
"2797764"|"13790"|"144806831"|"HAPPYTOHELP@outlook.com"|""|""|""|"2016-07-31 18:23:40.883000000"
"2797764"|"10633"|"42705052"|"venkatnarayan@vintel.edu"|""|""|""|"2016-07-31 06:14:53.760000000"
"2797764"|"13922"|"168459259"|"alxendar@outlook.com"|""|""|""|"2016-07-31 19:19:29.643000000"

I need to load this data into a table called as tmp_email. Below is the control file i have created , but it gives me an error.

Structure of Tmp_email
WORKID  NUMBER
 BatchID NUMBER
 SubID  NUMBER
 EmailAddr VARCHAR2(80)
 FirstName VARCHAR2(80)
 LastName VARCHAR2(80)
 pinCode NUMBER
 Senddate timestamp
 


load data
infile 'emaildata.txt'
TRUNCATE
into table TMP_email
fields terminated by '|'  OPTIONALLY ENCLOSED BY '"'
(WORKID  "RTRIM(LTRIM(:WORKID ,'\"'),'\"')",
 BatchID "RTRIM(LTRIM(:BatchID ,'"'),'"')",
 SubID  "RTRIM(LTRIM(:SubID ,'"'),'"')" ,
 EmailAddr "RTRIM(LTRIM(:EmailAddr ,'"'),'"')",
 FirstName "RTRIM(LTRIM(:FirstName ,'"'),'"')",
 LastName "RTRIM(LTRIM(:LastName ,'"'),'"')",
 pinCode "RTRIM(LTRIM(:PINCODE ,'"'),'"')",
 Senddate "RTRIM(LTRIM(:Senddate ,'"'),'"')" )
 

 
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Oct 5 02:05:43 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-350: Syntax error at line 6.
Expecting "," or ")", found "),".
(JobID  "RTRIM(LTRIM(:JOBID ,'"'),'"')",


Also, I need to convert the string into timestamp.

Any help is really appreciated.
0
Comment
Question by:sam_2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41829313
Try this on which the column you wanted to convert.

CASE WHEN TRY_PARSE(coltobeConverted AS DATETIME) IS NOT NULL THEN TO_TIMESTAMP (coltobeConverted, 'DD-Mon-RR HH24:MI:SS.FF') END
0
 

Author Comment

by:sam_2012
ID: 41829318
about the error Iam getting for rtrim and ltrim , also , I need to remove the " in the data while inserting into the database table.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41829325
Ok so do one thing , please post your query here..
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:sam_2012
ID: 41829388
SQL*Loader: Release 11.2.0.1.0 - Production on Wed Oct 5 02:05:43 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

SQL*Loader-350: Syntax error at line 6.
Expecting "," or ")", found "),".
(JobID  "RTRIM(LTRIM(:JOBID ,'"'),'"')",

This is the error Iam getting when I run sqlloader.
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41829391
Can you provide us the entire query you have used in there?
0
 

Author Comment

by:sam_2012
ID: 41829393
In this question  i want to resolve the sqlldr issue
0
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 41829701
I used the below and your sample data loaded just fine.
OPTIONS	(SKIP=1)
LOAD DATA
	INFILE *
	DISCARDMAX 9999
	APPEND INTO TABLE tmp_email
	fields terminated by "|" optionally enclosed by '"'
	trailing NULLCOLS
	(
		WORKID,
		BATCHID,
		SUBID,
		EMAILADDR,
		FIRSTNAME,
		LASTNAME,
		PINCODE,
		SENDDATE TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF9'
	)
BEGINDATA
"JobID"|"BatchID"|"SubID"|"EmailAddr"|"FirstName"|"LastName"|"pincode"|"SendDate"
"2796921"|"1442"|"151719024"|"RAJU@gmail.com"|""|""|""|"2016-07-31 02:40:37.697000000"
"2796926"|"487"|"75326115"|"CHANDRU@VINTEL.EDU"|""|""|""|"2016-07-31 01:39:52.070000000"
"2797764"|"1065"|"33696904"|"RAMCHAMDRA@GMAIL.COM"|""|""|""|"2016-07-29 20:34:14.113000000"
"2797764"|"5749"|"40004462"|"DSOUZA@VINTEL.edu"|""|""|""|"2016-07-30 14:52:42.443000000"
"2797764"|"13077"|"108620732"|"NAGAVENU@GMAIL.com"|""|""|""|"2016-07-31 15:19:48.123000000"
"2797764"|"13790"|"144806831"|"HAPPYTOHELP@outlook.com"|""|""|""|"2016-07-31 18:23:40.883000000"
"2797764"|"10633"|"42705052"|"venkatnarayan@vintel.edu"|""|""|""|"2016-07-31 06:14:53.760000000"
"2797764"|"13922"|"168459259"|"alxendar@outlook.com"|""|""|""|"2016-07-31 19:19:29.643000000"

Open in new window

The optionally enclosed by will remove the double quotes.  Not sure why you are trying to do that with trim commands.  All you should need to do with the control file you have is to remove all the trims and add the format for the timestamp.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question