OracleDBにcsv 郵便番号DB取り込こむ
こちらを参考にしました。
http://otndnld.oracle.co.jp/beginner/9ias/4_2/index.html
大体つぎのような順番で
213 sqlplus orcl/xxxx@xxxx
216 wget http://www.post.japanpost.jp/zipcode/dl/kogaki/lzh/ken_all.lzh
236 lha x ken_all.lzh
239 cat ken_all.csv | nkf -w -Lu > ken_all.utf8.csv
242 vi zip.ctl
247 sqlldr userid=orcl/xxxx@xxxx control=zip.ctl
253 sqlplus orcl/xxxx@xxxx
[oracle@cent5-64b-40 zipcode]$ sqlplus orcl/xxxx
SQL*Plus: Release 11.2.0.1.0 Production on 土 8月 25 12:08:44 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning and Real Application Testing options
に接続されました。
SQL>
SQL>
SQL> CREATE TABLE ZIP(
publicCode NUMBER(5) NOT NULL,
OldZipcode VARCHAR2(5) NOT NULL,
Zipcode VARCHAR2(7) NOT NULL,
PrefectureKana VARCHAR2(30) NOT NULL,
CityKana VARCHAR2(300) NOT NULL,
TownKana VARCHAR2(300) NOT NULL,
Prefecture VARCHAR2(30) NOT NULL,
City VARCHAR2(300) NOT NULL,
Town VARCHAR2(300) NOT NULL,
TownDivideFlg NUMBER(1) NOT NULL,
KoazaBranchFlg NUMBER(1) NOT NULL,
ChomeFlg NUMBER(1) NOT NULL,
HasSomeTownsFlg NUMBER(1) NOT NULL,
UpdateStatus NUMBER(1) NOT NULL,
UpdateReason NUMBER(1) NOT NULL
);
表が作成されました。
SQL> select * from zip;
SQL> CREATE INDEX ZIPCODE_INDEX ON ZIP(ZIPCODE);
SQL> CREATE INDEX OLD_ZIPCODE_INDEX ON ZIP(OLD_ZIPCODE);
zip.ctlの中身は
LOAD DATA
INFILE 'ken_all.utf8.csv'
INTO TABLE ZIP
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
publicCode ,
OldZipcode ,
Zipcode ,
PrefectureKana ,
CityKana ,
TownKana ,
Prefecture ,
City ,
Town ,
TownDivideFlg ,
KoazaBranchFlg ,
ChomeFlg ,
HasSomeTownsFlg ,
UpdateStatus ,
UpdateReason
)
247 sqlldr userid=orcl/xxxx@xxxx control=zip.ctl
253 sqlplus orcl/xxxx@xxxx
SQL> select city,town from zip where zipcode='2030013';
CITY
--------------------------------------------------------------------------------
TOWN
--------------------------------------------------------------------------------
東久留米市
新川町
SQL>
