Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1272
  • Last Modified:

Format Mask for numbers in SQLLoader

I need to give a format mask for numbers in sqlloader
The csv file is cvariable record length and variable column length

Is it possible and how do I do it
0
mbergeby
Asked:
mbergeby
3 Solutions
 
riazpkCommented:
Yes sure.

Here is an example:

LOAD DATA
INFILE *
INTO TABLE isnert_without_columns
replace
FIELDS TERMINATED BY X'9'
(OBJECT_TYPE,
OBJECT_NAME,
INSERT_STMT "upper(:INSERT_STMT)",  -- You will apply number format accordingly in the same way as done here.
TABLE_NAME,
FIX)
BEGINDATA
<Data>
0
 
mbergebyAuthor Commented:
Hi Riazpk
I have to ask you to be more specific
Is this an example or is it something I have to do before the actual loading
Can I use the actual column name instead of INSERT_STMT as in your example

0
 
mbergebyAuthor Commented:
Additional Info
The numbers are in the format 123.456.789 and sometimes 123.456
Fields terminated by ;
Data in a separate file which is defined by the infile parameter
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
sapnamCommented:
For numbers without decimal values you use integer external
For numbers with decimal valies you use decimal external

like suppose my data is of 3 fields, Acct which is NUMBER(4) , DBCR VARCHAR2(1) and BAL NUMBER(13,2)

load data infile 'c:\temp\data.csv'
append into table my_table
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(acct   integer external,
 dbcr   char,
 bal     decimal external)

0
 
riazpkCommented:
Yes sure,

You will have to use your own column names and own function (to_number instead of upper).

That was just an example to show you that it works.
0
 
MikeOM_DBACommented:

Actually you may need to remove the commas:

load data infile 'c:\temp\data.csv'
append into table my_table
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
(acct   integer external,
 dbcr   char,
 bal     decimal external "REPLACE(:BAL,',','')")
0
 
riazpkCommented:
Hi,
Any update on this? If your problem has been resolved, then please close this question.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now