• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4118
  • Last Modified:

SQL LOADER EMPTY ROW

Hi,

Below is the control file I am using to load data into the table INFO_STAGE2.  At the end of the load it is creating an empty row, I mean the  first three columns will be empty and it generates sysdate  for DATE column.

Table:

ROW_NUMBER    VARCHAR2(15 byte)
ID                          VARCHAR2(25 byte)
STATUS                VARCHAR2(5 byte)
DATE                     DATE

Control File:
LOAD DATA
INFILE '/usr/summary/Info.txt'
BADFILE '/usr/Scripts/error_info.bad'
DISCARDFILE '/usr/Scripts/error_info.dsc'
APPEND
INTO TABLE INFO_STAGE2
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
    ROW_NUMBER"trim(:ROW_NUMBER)",
    ID"trim(:ID)",
    STATUS"trim(:STATUS)",
    DATE "sysdate"
)

 

Info.txt
0
new_perl_user
Asked:
new_perl_user
  • 6
  • 6
  • 2
1 Solution
 
morguloCommented:
I think you should use "REJECT ROWS WITH ALL NULL FIELDS" option or remove last row from the source file.
0
 
new_perl_userAuthor Commented:
There is nothing like null row in the source file. And should I implement the "REJECT ROWS WITH ALL NULL FIELDS" statement in the control file
0
 
morguloCommented:
Sorry I've missed sample file. You can try remove CR chars (\r). It is possible that loader interprets this pair as \n\n.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
new_perl_userAuthor Commented:
Sorry I did not get you. if possible can you be more specific.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
I tested it with the below control file and it works fine and i can load 5 records successfully into info_stage2 table :

LOAD DATA
INFILE 'c:\Info.txt'
BADFILE 'c:\info.bad'
DISCARDFILE 'c:\error_info.dsc'
APPEND
INTO TABLE INFO_STAGE2
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
    ROW_NUMBER"trim(:ROW_NUMBER)",
    ID"trim(:ID)",
    STATUS"trim(:STATUS)",
    DATE1 "sysdate"
)


ee.doc
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
also this is my log file  :


a.log
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
can you provide your log file to let us know what is the actual error you are getting ?
0
 
new_perl_userAuthor Commented:
Hi,
 I am attaching the log file from sql loder and also the  snap shot of table which shows the empty row. Still not understanding why it is working for you and for me it is loading an empty row. I used the same control file as above.
INFO.log
Table.doc
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
your log file shows this "136 Rows successfully loaded." i thought only 5 should be there ?

Can you give your full data file not just 5 records for me to test ?
0
 
new_perl_userAuthor Commented:
Hi,
Attached is the whole data file I am trying to load. But it is not the problem how many records where there because sometimes I get only 5 records in this data file or only 1 record sometimes.

But regardless of the no of records is always create a nuill row as shown in the above attached table.doc
Info.txt
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Hi, i was able to replicate your issue in my test database. that is just because of empty (blank) lines at the end of your data file. Just open the Info.txt (4 KB) which you had attached in the 35128409 update and go to the end of the file and there are two blank lines which is causing  the issue.

i have modified your data file to remove those blank records and attaching it here. just try to load this and you will not encounter the issue.

Thanks

Info-one.txt
0
 
new_perl_userAuthor Commented:

Hi,
 How to remove this blank lines in future. I mean I have to load data from these kind of files everyday, so is there any modification I need to do in the control file to overcome this issue.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Can you try this. It works for me fine in not loading the blank records though my data file has blank records.

LOAD DATA
INFILE 'c:\Info_one.txt'
BADFILE 'c:\info_one.bad'
DISCARDFILE 'c:\error_info.dsc'
APPEND
INTO TABLE INFO_STAGE2
when ID <> ' '
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
    ROW_NUMBER "trim(:ROW_NUMBER)",
    ID "trim(:ID)",
    STATUS "trim(:STATUS)",
    DATE1 "sysdate"
)
0
 
new_perl_userAuthor Commented:
It worked. Thank you.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now