#!/usr/bin/perl
#
# You have to have these CPAN modules.
#
# $ perl -MCPAN -e shell
# install DBI
# install Text::CSV_XS

=cut
mysql -u root
create database zip;
grant all privileges on zip.* to "k-ozaki"@localhost.localdomain
identified by 'password' with grant option;
grant all privileges on zip.* to "k-ozaki"@localhost
identified by 'password' with grant option;
mysql -p zip
create table ZIP (
-- 全国地方公共団体コード(JIS X0401、X0402)………　半角数字
CODE VARCHAR(10),
-- (旧)郵便番号(5桁)………………………………………　半角数字
OLD_ZIP VARCHAR(5),
-- 郵便番号(7桁)………………………………………　半角数字
ZIP VARCHAR(7),
-- 都道府県名　…………　半角カタカナ(コード順に掲載)　(注1)
PREFECTURE_KANA VARCHAR(255),
-- 市区町村名　…………　半角カタカナ(コード順に掲載)　(注1)
CITY_KANA VARCHAR(255),
-- 町域名　………………　半角カタカナ(五十音順に掲載)　(注1)
STREET_KANA VARCHAR(255),
-- 都道府県名　…………　漢字(コード順に掲載)　(注1,2)
PREFECTURE VARCHAR(255),
-- 市区町村名　…………　漢字(コード順に掲載)　(注1,2)
CITY VARCHAR(255),
-- 町域名　………………　漢字(五十音順に掲載)　(注1,2)
STREET VARCHAR(255));
=cut

use DBI;
use strict;
use IO::File;
use Text::CSV_XS;

#
# configuration
#

## csv file
my $csvfile = 'ken_all.csv';

## database name
my $database_name = 'zip';

## database user
my $database_user = 'k-ozaki';

## database password
my $database_password = 'password';

#
# main
#

## csv reader
my $csv = Text::CSV_XS->new({'binary' => 1});

## mysql writer
my $dbh = DBI->connect("DBI:mysql:database=$database_name", $database_user, $database_password);

## prepare statement
my $sth = $dbh->prepare("insert into ZIP values (?,?,?,?,?,?,?,?,?);");

## keep track of csv lines
my $line = 1;

## open the csv file
my $io = IO::File->new($csvfile, "r");
while (not $io->eof) {

    ## get 1 csv line
    my $row = $csv->getline($io);

    if (!$row) {
	print "bad csv format $line: ";
	print "\n";
	next;
    }
    $line++;

    ## break up the fields
    my $code            = $row->[0];
    my $old_zip         = $row->[1];
    my $zip             = $row->[2];
    my $prefecture_kana = $row->[3];
    my $city_kana       = $row->[4];
    my $street_kana     = $row->[5];
    my $prefecture      = $row->[6];
    my $city            = $row->[7];
    my $street          = $row->[8];

    ## execute statement
    $sth->execute($code, $old_zip, $zip,
		  $prefecture_kana, $city_kana, $street_kana,
		  $prefecture, $city, $street);
    $sth->finish();

}
$dbh->disconnect();

exit;

=cut
$Log: loadzip.pl,v $
Revision 1.1  2005/04/01 13:02:37  k-ozaki
created

=cut