0

I tried to upload some records into my table ABC. None of the records went through and they all showed up in the .bad log.

I am pretty new to sqlldr. Not quite sure where did I messed up. Let me show you the steps I took.

First, I created an empty table called ABC.

 create table abc
    (
      location_id varchar2(10),
      sold_month date,
      item_name varchar2(30),
      company_id varchar2(10),
      qty_sold number(10),
      total_revenue number(14,3),
      promotional_code varchar2(10)
    );

Here is my flat file abcflat.dat. The columns correspond to the columns in the table above.

"1000","02/01/1957","Washing Machine","200011","10","10000","ABCDE"
"1000","05/02/2013","Computer","200012","5","5000","ABCDE"
"1000","05/01/2013","Bolt","200010","100","500","ABCDE"
"1000","05/03/2013","Coca Cola","200011","1000","1000","ABCDE"

Here is my control file abc.ctl

LOAD DATA
INFILE 'C:\Users\Public\abcflat.dat'
INTO TABLE ABC
FIELDS TERMINATED BY "," 
enclosed by '"'
(
Location_ID
, Sold_month
, item_name
, Company_id
, QTY_Sold
, Total_revenue
, Promotional_Code
)

And my last step

sqlldr hr/open@xe control=c:\users\public\abc.ctl

It says 

Commit point reached - logical record count 3
Commit point reached - logical record count 4

but none of the record showed up on my ABC table.

Thank You

JohnD
  • 353
  • 4
  • 13
  • 26
  • Rows going into the bad file means that the format is different than what is expected by the loader.... The problem is with the DATE column. You have to provide a to_date function with the proper format string... To verify this, you could create a similar table, with a varchar2 column instead of the date, and try your sql loader script – ppeterka Sep 18 '13 at 16:23
  • @a_horse_with_no_name the .bad file only replicates the records that didn't go through. It didn't specify any reasons. A .bad file was create automatically but a log wasn't. Do I have to create a log manually when I enter the command? – JohnD Sep 18 '13 at 16:41

1 Answers1

1

It's most probably the date format, try this:

LOAD DATA
INFILE 'C:\Users\Public\abcflat.dat'
INTO TABLE ABC
FIELDS TERMINATED BY "," 
enclosed by '"'
(
Location_ID
, Sold_month DATE "DD/MM/YYYY"
, item_name
, Company_id
, QTY_Sold
, Total_revenue
, Promotional_Code
)
  • Yes it works. I thought I tried that but it didn't work out before. Anyway I have a quick question: if my flat file's data contains quotations, then I have to use enclosed by '"' ? – JohnD Sep 18 '13 at 16:39
  • @GoodLooking: yes you need `enclosed by '"'` –  Sep 18 '13 at 16:40