Re: SQL loader - SKIP FIELD + ADD DATA

In article <2355c0e9.0111190854.5e6b2878_at_posting.google.com>, daniel.st-jacques_at_ca.kontron.com says...
>
>Thank you for the tips Thomas.
>
>I do have an other one for you.
>
>If a have a ASCII file with 3 fields:
>code|long description|short description
>
>I do have a table with 2 fields:
>Code and description
>
>I want to put the long description in to the table description if the
>length of it is smaller then 25 characters. If the ascii file long
>description is longer then 24 characters, I want to have the ascii
>file short description loaded into the description table.
>
>I don't know how to make 'decode' working for that.
>table x: Code varchar2(3)
> description varchar2(24)
>
>INTO TABLE X
>( code "UPPER( :code )",
> description "IF LENGTH( :description ) > 24 THEN NULL ELSE UPPER(
>:description )",
> description "IF description = NULL THEN UPPER( :description ) ELSE
>:description"
>)
>
>In fact is : IF the long description from the ascii file is smaller
>then 25 characters, we should take that one else if the length of the
>ascii file long description is greater than 24 we have to take the
>ascii file short description.
>

goto
http://osi.oracle.com/~tkyte/SkipCols/index.html

and get my delimited package, then you can code:

LOAD DATA
INFILE * BADFILE 'asciifile.bad'
INTO TABLE t
replace
( code position(1:4096) "delimited.word( :code, 1, '''', '|' )", descript position(1:1) "decode(
sign(length(delimited.word(:code,2,'''','|'))-24),1,delimited.word(:code,3,'''','|' ) ,delimited.word(:code,2,'''','|'))"
)
begindata
1234|long desc|hello
5678|this is bigger then 25 characters so we should not load it|short

which results in

ops$ora734_at_ORA734.WORLD> select * from t;

CODE DESCRIPT

---------- -------------------------
1234       long desc
5678       short

ops$ora734_at_ORA734.WORLD>

which I believe is what you want

>
>
>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message
>news:<9t4d0h01bav_at_drn.newsguy.com>...
>> In article <2355c0e9.0111161341.129f16db_at_posting.google.com>,
>> daniel.st-jacques_at_ca.kontron.com says...
>> >
>> >Hello everyone
>> >
>> >I am using Oracle 7.3.2.2
>> >
>> >I do have an ascii file to load that is look like:
>> >
>> >number|name|address1|address2|city|zip
>> >
>> >In the Oracle table I have several field that are mandatory but not
>> >present into the ascii file.
>> >Oracle table: cust_id
>> > name
>> > address
>> > city
>> > zip
>> > can-phone
>> > can-write
>> >
>> >1. Is it possible with SQL loader 7.3.4.4.1 to skip a field from the
>> >ascii file?
>> > in this example "address2"
>> >
>> >2. Is it possible to force data into some field even if the data is
>> >not part of the ascii file?
>> > example : can-phone = "Y"
>> > can-write = "N"
>> >
>> >Here is my control file
>> >LOAD DATA
>> >INFILE 'asciifile' BADFILE 'asciifile.bad'
>> >INTO TABLE customer
>> > FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
>> > ( cust_num,
>> > name "UPPER( :name )",
>> > address "UPPER( :address )"
>> >----> Something missing to skip address2. FILLER doesn't work
>> > city "UPPER( :city )"
>> > zip "UPPER( :zip )"
>> > )
>> >
>> >There is 2 things missing in the control file.
>> > 1. How to skip a field
>> > 2. How to add fields with fix data that is not part of the ASCII
>> >file.
>> > If the fields that are not part of the ascii file can be filled
>> >up
>> > by condition it would be great.
>> > example: if zip begins by 1
>> > then can-phone = "Y"
>> > can-write = "N"
>> > else can-phone = "N"
>> > can-write = "Y"
>> >
>> >Is anyone can find an EASY way to do this.
>> >
>> >That has to by done each week.
>> >
>> >I am looking forward for the answers
>> >
>> >Daniel St-Jacques
>> >daniel.st-jacques_at_ca.kontron.com
>>
>>
>> LOAD DATA
>> INFILE * BADFILE 'asciifile.bad'
>> INTO TABLE t
>> replace
>> FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
>> trailing nullcols
>> ( cust_id,
>> name "UPPER( :name )",
>> address "UPPER( :address )",
>> can_phone "decode( substr(:zip,1,1), '1', 'Y', 'N' )",
>> city "UPPER( :city )",
>> zip "UPPER( :zip )",
>> can_write "decode( substr(:zip,1,1), '1', 'N', 'Y' )"
>> )
>> begindata
>> 1234|name|address1|address2|city|12345
>> 5678|name|address1|address2|city|23456
>>
>>
>> We can use the fact that you want to skip address2 to stuff "can_phone" in
>> there. can_phone will get address2 but -- we'll apply a sql function to
>> override that fact and populate can_phone with a decode on zip.
>>
>> Later, since we added trailing nullcols to the spec -- we'll load can_write
>>using the same technique. Instead of loading NULLS as trailing nullcols would
>> normally, we'll apply a function to zip again and get the data.
>>
>> After loading, we find:
>>
>> ops$ora734_at_ORA734.WORLD> l
>> 1 select cust_id, zip, can_phone, can_write
>> 2* from t
>> ops$ora734_at_ORA734.WORLD> /
>>
>> CUST_ID ZIP CAN_PHONE CAN_WRITE
>> ---------- ----- ------------------------- -------------------------
>> 1234 12345 Y N
>> 5678 23456 N Y
>>
>> as you want.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp