SQL Loader

[Chao]

Hi,

I am importing some data into a table from a delimited text file. Unforturnately the text file contains NULL records:

ID|First Name|Last Name
1|John|Smith

2|Mary|White
3|Ken|Gray

My question is how can I signal to SQL Loader to ignore any null record.

Thanks for your responses...

 

[mpramods]

Do u mean u have empty lines in the file. If so remove the blank lines from the file and then run ur sql loader script against the file.

Thanks,
Pramod

 

[Chao]

Removing the space is simply not possible due to the size of the file and the fact that blank lines appears randomly in the file.

I found the solution and would like to share it with you all. Just increase the maximum # of records SQL Loader can discard in the control file.

Thanks,
CHAO

 

[THoey]

You can use the WHEN clause to only add records where the destination field will not equal NULL:

options(errors=10)
load data				-- Keywords to start SQL Loader
infile 'D:\FTP\SATR\satxref.txt'	-- Import file and path
into table AGENT_XREF TRUNCATE		-- Table to import into
[b]     when (WORK_TYPE <> '')[/b]
(
 AGENT_ID    position(01:04)  CHAR,
 AGENT_NAME  position(05:24)  CHAR "INITCAP(:AGENT_NAME)",
 DIALER_ID   position(25:31)  CHAR "LOWER(:DIALER_ID)",
 SUPERVISOR  position(32:50)  CHAR "INITCAP(:SUPERVISOR)",
 WORK_TYPE   position(60:62)  CHAR,
 CENTER      CONSTANT 'SAT'
)

Yes, you need to increase your errors to be able to handle the number of blank lines.

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Chao]

Hi Terry,

Does SQL Loader treat '' as NULL? If so then this is very different from SQL statement. I will give your suggestion a try. Thanks for your feedback.

CHAO