Oracle Database Discussions

Hi:

I am on 10.2.0.3; hp-ux B.11.23.

I regularly need to sqlload excel files into one of my DBs.

I convert them to csv files, ftp to unix in bin mode,convert them using dos2ux, create control files and run sqlldr. Since my front end is PeopleSoft, every non-date column is defined as NOT NULL.

By some reason all spaces on my csv files are perceived as nulls by sqlldr, so I have to use that: FLD1 nvl(:FLD1,' ') on almost every column. Then it works but it's very cumbersome - my tables could have 100+ columns. Of course, I could remove NOT NULL constraints, load data, update nulls to spaces and re-enable NOT NULL constraints. But it's so much manual work...And they refuse to provide me with the dump files!

Anyone has any ideas to simplify that? Please see example below - and thanks.

A line from csv file:
CVD01,A90677.15USD01,1,1901-01-01,A, , ,ENG, ,USA,1 Old Bridge Street, , , ,Cambridge, , , , ,MA,02139, , , , , , ,CNV,1999-12-15

And here is my ctl file:

load data infile myfile.csv
replace
into table mytable
fields terminated by "," optionally enclosed by '"' TRAILING NULLCOLS
(
SETID ,
CUST_ID ,
ADDRESS_SEQ_NUM ,
EFFDT "to_date(:EFFDT ,'yyyy-mm-dd')" ,
EFF_STATUS ,
ALT_NAME1 "nvl(:ALT_NAME1,' ')" ,
ALT_NAME2 "nvl(:ALT_NAME2,' ')" ,
LANGUAGE_CD ,
TAX_CD "nvl(:TAX_CD,' ')" ,
COUNTRY "nvl(:COUNTRY,' ')" ,
ADDRESS1 "nvl(:ADDRESS1,' ')" ,
ADDRESS2 "nvl(:ADDRESS2,' ')" ,
ADDRESS3 "nvl(:ADDRESS3,' ')" ,
ADDRESS4 "nvl(:ADDRESS4,' ')" ,
CITY "nvl(:CITY,' ')" ,
NUM1 "nvl(:NUM1,' ')" ,
NUM2 "nvl(:NUM2,' ')" ,
HOUSE_TYPE "nvl(:HOUSE_TYPE,' ')" ,
COUNTY "nvl(:COUNTY,' ')" ,
STATE "nvl(:STATE,' ')" ,
POSTAL "nvl(:POSTAL,' ')" ,
GEO_CODE "nvl(:GEO_CODE,' ')" ,
IN_CITY_LIMIT "nvl(:IN_CITY_LIMIT ,' ')" ,
COUNTRY_CODE "nvl(:COUNTRY_CODE,' ')" ,
PHONE "nvl(:PHONE,' ')" ,
EXTENSION "nvl(:EXTENSION,' ')" ,
FAX "nvl(:FAX,' ')" ,
LAST_MAINT_OPRID "nvl(:LAST_MAINT_OPRID,' ')" ,
DATE_LAST_MAINT "to_date(:DATE_LAST_MAINT ,'yyyy-mm-dd')"
)