CODEWITHSUNDEEP
sqlimportsql-loader
HackSparrow
HackSparrow

Reputation: 27

SQL loader to load data into specific column of a table

Recently started working on SQL Loader, enjoying the way it works. We are stuck with a problem where we have to load all the columns in csv format say (10 columns in excel)but the destination table contains around 15 fields. filler works when you want you skip columns in source file but unsure what to do here. using is staging table helps but is there any alternative?

Any help is really appreciated. thanks.

Upvotes: 1

Views: 8277

Answers (1)

ppeterka
ppeterka

Reputation: 20726

You have to specify the columns in the control file

Recommended reading: SQL*Loader Control File Reference

10 The remainder of the control file contains the field list, which provides information about column formats in the table being loaded. See Chapter 6 for information about that section of the control file.

Excerpt from Chapter 6:

Example 6-1 Field List Section of Sample Control File

1  (hiredate  SYSDATE,
2     deptno  POSITION(1:2)  INTEGER EXTERNAL(2)
              NULLIF deptno=BLANKS,
3       job   POSITION(7:14)  CHAR  TERMINATED BY WHITESPACE
              NULLIF job=BLANKS  "UPPER(:job)",
       mgr    POSITION(28:31) INTEGER EXTERNAL 
              TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
       ename  POSITION(34:41) CHAR 
              TERMINATED BY WHITESPACE  "UPPER(:ename)",
       empno  POSITION(45) INTEGER EXTERNAL 
              TERMINATED BY WHITESPACE,
       sal    POSITION(51) CHAR  TERMINATED BY WHITESPACE
              "TO_NUMBER(:sal,'$99,999.99')",
4      comm   INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'
              ":comm * 100"
    )

In this sample control file, the numbers that appear to the left would not appear in a real control file. They are keyed in this sample to the explanatory notes in the following list:

1 SYSDATE sets the column to the current system date. See Setting a Column to the Current Date.

2 POSITION specifies the position of a data field. See Specifying the Position of a Data Field.

INTEGER EXTERNAL is the datatype for the field. See Specifying the Datatype of a Data Field and Numeric EXTERNAL.

The NULLIF clause is one of the clauses that can be used to specify field conditions. See Using the WHEN, NULLIF, and DEFAULTIF Clauses.

In this sample, the field is being compared to blanks, using the BLANKS parameter. See Comparing Fields to BLANKS.

3 The TERMINATED BY WHITESPACE clause is one of the delimiters it is possible to specify for a field. See TERMINATED Fields.

4 The ENCLOSED BY clause is another possible field delimiter. See Enclosed Fields.

Upvotes: 3

Related Questions