Multibyte Sqlload

To SQLLOAD with multi-byte characters when data is in Excel

1. Add a header line at the top of the page (if one doesn’t exist)
2. Save as a Unicode text file
3. Open the saved Unicode file in Word
4. Remove any tabs. Edit->Replace (you may have to cut and paste a tab in from notepad to do this) – Replace All
5. The delimiter used above may be in double quotes - remove the quotes. For instance, Edit->Replace “,” , replace all
6. Save the file as plain text, then Unicode (UTF-8). Note: Saving as just Unicode will not work
7. Binary ftp the file to Unix only using command line
8. Make sure to specify Options (skip =1) in your ctl file to skip the header

Note: the reason for the ensuring there is a header is that some characters are inserted at the beginning of the first line. If we make it the header and then skip it anyway, we don’t need to worry about deleting those characters.

Sample datafile:

Site, Rep
1000,仕切値について,
1001,仕切値について,
1002,仕切値について,
1003,仕切値について,


Sample CTL file (characterset should be optional):

-------------------------------------------------------------------------
--
-- Description: SQL*Loader control file to load data from level 1
--
-- Date:
-------------------------------------------------------------------------
OPTIONS (skip = 1)
Load Data
--Infile 'load2.txt'
Characterset UTF8
REPLACE
INTO TABLE LEVEL1_SALESREP_UPD
Fields terminated BY ','
optionally enclosed BY '"'
trailing nullcols
(
site_number,
lv1_rep
)


Sample load command:

sqlldr userid=apps/ control=load.ctl data=load3.txt

Sample Log output:

QL*Loader: Release 8.0.6.1.0 - Production on Thu Dec 11 16:53:18 2003

(c) Copyright 1999 Oracle Corporation. All rights reserved.

Control File: load.ctl
Character Set UTF8 specified for all input.

Data File: load3.txt
Bad File: load3.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 1
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional

Table LEVEL1_SALESREP_UPD, loaded from every logical record.
Insert option in effect for this table: REPLACE
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SITE_NUMBER FIRST * , O(") CHARACTER
LV1_REP NEXT * , O(") CHARACTER


Table LEVEL1_SALESREP_UPD:
4 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.


Space allocated for bind array: 33024 bytes(64 rows)
Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 1
Total logical records read: 4
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Thu Dec 11 16:53:18 2003
Run ended on Thu Dec 11 16:53:18 2003

Elapsed time was: 00:00:00.23
CPU time was: 00:00:00.04