Skipping empty rows from data file 1

[EvansEd]

I'd like to know how to skip empty rows from a data file when writing my sql loader .ctl file?

There will be empty rows in the data file, but I just don't know where they will be.

 

[Turkbear]

You can add a test to your ctl file that checks the first character in a line and can act on the result:
For Instance

LOAD DATA
INFILE "PUBLIC.DAT"
INSERT
INTO TABLE mydata
when record_type <> ' '
 ( RECORD_TYPE POSITION(1:2),
 PERSON_NUM POSITION(3:8),
 LAST_NAME  POSITION(9:38),
 FIRST_NAME  POSITION(39:68) ,
 MIDDLE_NAME POSITION(69:98) 
 )

should only load lines where there is a non-space in position 1

 

[EvansEd]

I have a spreadsheet(.csv) file, therefore I can't use POSITION.

Any other suggestions?

 

[rcurva]

Since your file is a comma-delimited one, make use of this;

LOAD DATA
INFILE "PUBLIC.CSV"
INSERT INTO TABLE mydata
WHEN record_type <> ' '
FIELDS TERMINATED BY ','
(
RECORD_TYPE INTEGER EXTERNAL,
PERSON_NUM INTEGER EXTERNAL,
LAST_NAME CHAR,
FIRST_NAME CHAR,
MIDDLE_NAME CHAR NULLIF (MIDDLE_NAME=BLANKS)
)

Robbie

&quot;The rule is, not to besiege walled cities if it can possibly be avoided&quot; -- Art of War