naga1979
asked on
Sql loader data load
Hi All,
I need some help with the sql loader data loading options. I am trying to load a delimited file and want to skip the first field of the data stream. For example
Input file:
A,1,2,3,4
A,5,6,7,8
A,5,7,8,3
I want to skip the first column i.e., I don't want to load A. How to set this in the control file.
Cheers!
I need some help with the sql loader data loading options. I am trying to load a delimited file and want to skip the first field of the data stream. For example
Input file:
A,1,2,3,4
A,5,6,7,8
A,5,7,8,3
I want to skip the first column i.e., I don't want to load A. How to set this in the control file.
Cheers!
I think with a delimited data file, you cannot skip fields with SQL*Loader. With fixed-length (positional) data files, then SQL*Loader can skip fields. If you have to work with delimited files, then your options are:
1. use a two step process: first use SQL*Loader to load the entire records into a single-column work table that you create for this purpose, then use "substr" to copy the parts of the records that you want to the table where you want the data.
2. use a PL\SQL procedure that uses utl_file to read each line of the data file, "substr" the values you want into variables, then insert each record.
1. use a two step process: first use SQL*Loader to load the entire records into a single-column work table that you create for this purpose, then use "substr" to copy the parts of the records that you want to the table where you want the data.
2. use a PL\SQL procedure that uses utl_file to read each line of the data file, "substr" the values you want into variables, then insert each record.
or..
load into a stage table and process using plsql to move the desired columns from the stage table to the destination table.
an advantage of this is that the stage table can be an external table. an external table references a file on disk directly without needing you to use sqlloader. to change the data in the external table, change the file it references ( remembering to maintain the same structure ). this way, you will not need to use sql loader, it will be automatically done when you query the external table using sql.
simple :)
load into a stage table and process using plsql to move the desired columns from the stage table to the destination table.
an advantage of this is that the stage table can be an external table. an external table references a file on disk directly without needing you to use sqlloader. to change the data in the external table, change the file it references ( remembering to maintain the same structure ). this way, you will not need to use sql loader, it will be automatically done when you query the external table using sql.
simple :)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Markqeer,
Currently that's what I am doing. Stripping off the first field using a script and input that file to sql loader. I want to avoind that step if I can do it in the loader itself.
Cheers!!!
Currently that's what I am doing. Stripping off the first field using a script and input that file to sql loader. I want to avoind that step if I can do it in the loader itself.
Cheers!!!
>> Stripping off the first field using a script and input that file to sql loader. I want to avoind that step if I can do it in the loader itself.<<
That's what the keyword, FILLER, does.
That's what the keyword, FILLER, does.
ASKER
awking00,
It works, Thanks a lot.
Cheers!!!
It works, Thanks a lot.
Cheers!!!
use a positional controlfile.
http://orafaq.com/faqloadr.htm
http://www.psoug.org/reference/sqlloader.html
good luck :)