| Trying To Load Data WITHOUT Blanks. [message #71417] |
Thu, 31 October 2002 01:46  |
Lee Bennett
Messages: 3
Registered: September 2002
|
Junior Member |
|
|
|
I am devising a generic approach to transferring data from one database to another. The method needs to be applied to future projects and I won't know how many tables there will be, the construct of the tables, etc. My method spools each source table to a separate comma separated flat file. I then use SQL*Loader to load this data into temporary loading tables. Some conversion/filtering is carried out at this stage and I then copy straight into the target database tables. Here's the problem :- What I have found is that spooling the CSV files leaves trailing blanks and these get copied into the target database. I need to remove these blanks at some point in the process and don't know the best way to do it. I can't change the method that I have adopted, I basically just need to incorporate a RTRIM somewhere. Any ideas anyone?
|
|
|
|
| Re: Trying To Load Data WITHOUT Blanks. [message #71421 is a reply to message #71417] |
Thu, 31 October 2002 05:38  |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
i would prefer to handle the trailing nulls, rather RTRIM them, to maintain the integrity of data.
why not u try something like this.
add a "trailing nullcols"
load data
infile 'MyFile.txt'
into table MyTable
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(
INSTITUTE_NUMBERS nullif INSTITUTE_NUMBERS="(null)",
BD_BUDG_ROLLUP nullif BD_BUDG_ROLLUP="(null)",
IDC_ROLLUP nullif IDC_ROLLUP="(null)",
OTHER_ROLLUP nullif OTHER_ROLLUP="(null)"
) |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
