Load Numerical Column with Variable Comma

oraclesql-loader

I'm using sqlldr to load a file which has a numerical value as one of its fields.

The problem is that in some of the records the number has commas whereas in other, it doesn't.

So doing something like

num "to_number(:num, '999,999,999.99')",

loads the records with commas but not those without commas and doing

num "to_number(:num)",

loads only those without commas.

Is there a way to tell Oracle (sqlldr) that the comma is optional?

Best Answer

Here you go:

SQL> desc loadertst;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

SQL> !cat loadertst.ctl
load data
infile *
into table loadertst
fields terminated by ',' enclosed by '"'
(
col1 "to_number(replace(:col1,',',''))"
)
begindata
"123456"
"1,2,3,4,5,6"

SQL> !sqlldr phil/phil control=loadertst.ctl

SQL*Loader: Release 11.2.0.2.0 - Production on Thu Nov 29 23:33:17 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 2

SQL> select count(*) from loadertst;

  COUNT(*)
----------
         2

SQL>

Obviously you may need to fiddle with the fields terminated by ',' enclosed by '"' part to suit your data.

Related Question