Home » RDBMS Server » Server Utilities » Using a condition in SQL Loader for comma delimited file
icon14.gif  Using a condition in SQL Loader for comma delimited file [message #294613] Fri, 18 January 2008 03:36 Go to next message
bhalotias
Messages: 12
Registered: January 2008
Junior Member
Hi

1. I want to load a comma delimited file using a condition (WHEN clause). But when i tried it, only the first condition was loaded and rest ignored. How do I load the file?

2. I have a table with cols

Batch,
CC_Count,
P_Count

But my .csv file has jus two fields
batch, count

Now I wish to load the data into my table which has 3 cols. This should be based on the batch. For Eg. If batch is 2 then load count into CC_Count and set P_Count = 0 and if batch is 3, then load count into P_Count and set CC_count to 0.

I could ve done this if point 1 (when clause) worked. But how to do it when thats not working?
Can this be done? How? Kindly help me out.

regards
Saurabh

[Updated on: Fri, 18 January 2008 03:39]

Report message to a moderator

Re: Using a condition in SQL Loader for comma delimited file [message #294618 is a reply to message #294613] Fri, 18 January 2008 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But how to do it when thats not working?

How can we know as you didn't post anything of what you did?

POst post your control file, an extract of your data, what you want to have with these data.

Regards
Michel
Re: Using a condition in SQL Loader for comma delimited file [message #294624 is a reply to message #294613] Fri, 18 January 2008 04:07 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Also have a look at this.
Does this help you?

By
Vamsi
Re: Using a condition in SQL Loader for comma delimited file [message #294625 is a reply to message #294618] Fri, 18 January 2008 04:07 Go to previous messageGo to next message
bhalotias
Messages: 12
Registered: January 2008
Junior Member
Control File

LOAD DATA
INFILE 'c:/testfiles/testdata.csv'
discardfile ''c:/testfiles/test.log'
APPEND
INTO TABLE recon
WHEN(BATCH='2')
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(BATCH, BATCH_DESC FILLER, CC_CNT, PRIMRY_CNT CONSTANT "0")
WHEN(BATCH='3')
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(BATCH, BATCH_DESC FILLER, PRIMRY_CNT, CC_CNT CONSTANT "0")


Data File

2,CC Foreign,33
3,Primary Domestic,09
4,Primary Foreign,64


RECON Table has 3 cols:
Batch
CC_CNT
PRIMRY_CNT

[Updated on: Fri, 18 January 2008 04:09]

Report message to a moderator

Re: Using a condition in SQL Loader for comma delimited file [message #294633 is a reply to message #294625] Fri, 18 January 2008 04:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the execution gave you which output?

please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.

Regards
Michel
Re: Using a condition in SQL Loader for comma delimited file [message #294635 is a reply to message #294633] Fri, 18 January 2008 04:41 Go to previous messageGo to next message
bhalotias
Messages: 12
Registered: January 2008
Junior Member
No i didnt get the output. Thats what I had mentioned in the first post. Only the first when clause was executed not the second.
Re: Using a condition in SQL Loader for comma delimited file [message #294638 is a reply to message #294635] Fri, 18 January 2008 04:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What SQL*Loader gave you? Did you try to execute it?

Regards
Michel
Re: Using a condition in SQL Loader for comma delimited file [message #294640 is a reply to message #294638] Fri, 18 January 2008 04:50 Go to previous messageGo to next message
bhalotias
Messages: 12
Registered: January 2008
Junior Member
When I executed it, the recon table had just one entry and that is for batch 2. The entry for batch 3 was not loaded.
Re: Using a condition in SQL Loader for comma delimited file [message #294641 is a reply to message #294640] Fri, 18 January 2008 04:56 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
What is in c:/testfiles/test.log?

By
Vamsi
Re: Using a condition in SQL Loader for comma delimited file [message #294645 is a reply to message #294641] Fri, 18 January 2008 05:09 Go to previous messageGo to next message
bhalotias
Messages: 12
Registered: January 2008
Junior Member
Please find the log file attached.
  • Attachment: TEST.LOG
    (Size: 2.45KB, Downloaded 214 times)
Re: Using a condition in SQL Loader for comma delimited file [message #294652 is a reply to message #294645] Fri, 18 January 2008 05:55 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Have you looked at my first post?

By
Vamsi
Re: Using a condition in SQL Loader for comma delimited file [message #294661 is a reply to message #294652] Fri, 18 January 2008 06:33 Go to previous messageGo to next message
bhalotias
Messages: 12
Registered: January 2008
Junior Member
I do not think decode is going to help the problem. Because, since its a csv file, the second position should state the col name in which the count is to be loaded. Using decode I can populate CC_cnt correctly, but what about Primry_cnt. Column names cant be swapped right. I hope you have understood what I exactly require.
Re: Using a condition in SQL Loader for comma delimited file [message #294672 is a reply to message #294645] Fri, 18 January 2008 07:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The second when clause is on 22 and not 2 as you said.

Regards
Michel
Re: Using a condition in SQL Loader for comma delimited file [message #294675 is a reply to message #294672] Fri, 18 January 2008 07:14 Go to previous messageGo to next message
bhalotias
Messages: 12
Registered: January 2008
Junior Member
Sorry. That was an older log file. Please find the newer one attached.
  • Attachment: TEST.LOG
    (Size: 2.46KB, Downloaded 188 times)
Re: Using a condition in SQL Loader for comma delimited file [message #294680 is a reply to message #294675] Fri, 18 January 2008 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
When condition in on BATCH_NBR not on BATCH.
...I know this is an old log file and you will post a new one.

Also, we don't the data file.

Regards
Michel
Re: Using a condition in SQL Loader for comma delimited file [message #294687 is a reply to message #294680] Fri, 18 January 2008 08:03 Go to previous messageGo to next message
bhalotias
Messages: 12
Registered: January 2008
Junior Member
Oh !! Sorry. But whether its batch or batch_nbr, the logic remains the same. Only one record is getting loaded and the other isn't. SO i wanted to know if there is any way of loading the data.
Re: Using a condition in SQL Loader for comma delimited file [message #294689 is a reply to message #294687] Fri, 18 January 2008 08:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fix the errors.
Post the control file.
Post the data.
Post the log.

Regards
Michel
Re: Using a condition in SQL Loader for comma delimited file [message #294759 is a reply to message #294687] Sat, 19 January 2008 00:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You need one INTO clause for each WHEN clause and you need to reset the position of the first field after any WHEN clauses after the first one.


LOAD DATA
INFILE 'testdata.csv'
discardfile 'test.log'
APPEND
INTO TABLE recon
WHEN (batch='2')
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(BATCH, BATCH_DESC FILLER, CC_CNT, PRIMRY_CNT CONSTANT "0")
INTO TABLE recon
WHEN (BATCH='3')
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(BATCH position (1), BATCH_DESC FILLER, PRIMRY_CNT, CC_CNT CONSTANT "0")
Re: Using a condition in SQL Loader for comma delimited file [message #295134 is a reply to message #294759] Mon, 21 January 2008 07:07 Go to previous message
bhalotias
Messages: 12
Registered: January 2008
Junior Member
Thankyou Vamsi. The decode statement did work with some workaround.

Thanyou Barbara Boehmer. The method posted by you also works. But since the batch numbers are from 0 to 60, the LOC increases alot. Hence the decode statement was appropriate.

Thankyou all for you help and ideas.
Previous Topic: Database Import failed (Error Code:IMP-00098)
Next Topic: SQL LOADER (sqlldr)
Goto Forum:
  


Current Time: Sat Dec 10 01:10:34 CST 2016

Total time taken to generate the page: 0.08660 seconds