Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sql loader data load

Posted on 2006-11-08
7
Medium Priority
?
1,714 Views
Last Modified: 2012-06-21
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!
0
Comment
Question by:naga1979
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 18

Expert Comment

by:rbrooker
ID: 17901021
hi,

use a positional controlfile.
http://orafaq.com/faqloadr.htm
http://www.psoug.org/reference/sqlloader.html

good luck :)
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 17901190
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.

0
 
LVL 18

Expert Comment

by:rbrooker
ID: 17901234
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 :)
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 32

Accepted Solution

by:
awking00 earned 300 total points
ID: 17901374
use filler keyword

create table skip_a(col2 number, col3 number, col4 number, col5 number);

create loader control file, ld.ctl

LOAD DATA
INFILE *
TRUNCATE INTO TABLE SKIP_A
FIELDS TERMINATED BY ','
(col1 filler char
,col2        char
,col3        char
,col4        char
,col5        char)
BEGINDATA
A,1,2,3,4
A,5,6,7,8
A,5,7,8,3

sqlldr username/password control=ld

SQL> select * from skip_a;

      COL2       COL3       COL4       COL5
---------- ---------- ---------- ----------
         1          2          3          4
         5          6          7          8
         5          7          8          3
0
 
LVL 1

Author Comment

by:naga1979
ID: 17901391
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!!!
0
 
LVL 32

Expert Comment

by:awking00
ID: 17901577
>> 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.
0
 
LVL 1

Author Comment

by:naga1979
ID: 17901660
awking00,

It works, Thanks a lot.

Cheers!!!
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question