Thursday, November 20, 2008

SQLLOAD with multi-byte characters when data is in Excel

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 sims.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

No comments: