0

My CSV file look like this

111,,Allen,2000,,30
222,,John,3000,,40
333,,Miller,4000,,50
333,,King,5000,,50

Some times empty columns may come in different positions, Can we suggest anything to ctl files ,such that it can skip only empty columns with comma seperated?

Srinivas
  • 147
  • 1
  • 14
  • When you say "skip only empty columns" what behaviour do you have in mind? There are options for handling null cells for NOT NULL table columns; the default is to reject the row and write it to the bad file. – APC Jul 06 '17 at 05:56
  • Suppose my table having only four columns ID,NAME,Sal,Address. But in CSV file I have empty columns like above , now my question is how to eliminate those empty columns (because ,here empty column is treating as one column).I want dynamically to eliminate those empty columns from ctl file – Srinivas Jul 06 '17 at 08:42
  • 1
    So you want to automatically ignore empty columns regardless of their position in the row? That sounds dangerous. A better plan would be to fix the data source so it provided reliable data. – APC Jul 06 '17 at 09:06
  • Some times it may contains large number of empty columns ,then ? and also I want to make it dynamic (I mean so many csv files will come like this) – Srinivas Jul 06 '17 at 09:20
  • 1
    Don't accept bad files from your data source. Reject them until they get it right.Unless of course YOU have it wrong and those NULL columns are supposed to be there? Go back, review the specs and be sure. – Gary_W Jul 06 '17 at 16:44

1 Answers1

0

actually, I've never it used, but it seems to me usefull:

LOAD DATA
INFILE 'name.txt'
BADFILE 'name.bad'
DISCARDFILE 'name.dsc'
APPEND
INTO TABLE emp_tab
WHEN emp_id != BLANKS
FIELDS TERMINATED BY ";"
TRAILING NULLCOLS
(emp_id, name, age)

WHEN emp_id != BLANKS

saphsys
  • 96
  • 4