iT邦幫忙

2018 iT 邦幫忙鐵人賽
DAY 18
1
Modern Web

ASP.NET MVC網頁程式介紹系列 第 18

[Day 18] 使用HeidiSQL匯入Excel資料

今天把鄉鎮的資料整理完了,
不過鄉鎮的資料實在太多了,
想到HeidiSQL可以匯入Excel檔案,
就以這個為主題來當作今天的分享,

首先要建一個空的資料表如下:
https://ithelp.ithome.com.tw/upload/images/20171227/20105694xuWBFxCXo9.jpg
一個鄉鎮(Village)的資料表,裡面有四個欄位,其中CityId是對應到之前City資料表的id欄位,City欄位可以不需要,不過還是留下來,是給人看的不是程式要用的。

然後HeidiSQL只能匯入csv格式的Excel檔案,
所以要先把檔案轉成csv檔案,
而csv檔案只能有一個標籤,
所以要先將標籤匯出,
在Excel檔案下方的標籤按右鍵,選擇移動或複製
https://ithelp.ithome.com.tw/upload/images/20171227/201056946hhnnQs3CY.jpg

然後勾選新作業簿,並選擇新的檔案
https://ithelp.ithome.com.tw/upload/images/20171227/20105694EL9Tkof4tu.jpg

之後存成csv檔案,記得檔案類型要選擇csv檔案
https://ithelp.ithome.com.tw/upload/images/20171227/201056946tFtEVHbhf.jpg

接下來到HeidiSQL選擇 工具 -> 匯入CSV文件
https://ithelp.ithome.com.tw/upload/images/20171227/20105694kQOKAzFO1s.jpg

這下來這張圖就是重點了,基本上參考這張圖去操作就可以了
https://ithelp.ithome.com.tw/upload/images/20171227/20105694V5s4k6mWgt.jpg
1.瀏覽選擇檔案
2.下方的方式要選 服務器格式化數據(才能改編碼)
3.Excel一般是用big5編碼的,上方的編碼就選big5吧
4.忽略前1行代表那一行是標題列,不要匯入
5.右邊用 , 做分隔(csv是用逗號做分隔的)
6.確認資料庫跟資料表是否正確
7.選擇匯入的欄位,我是用同樣欄位名稱跟順序

這樣就大功告成了!
https://ithelp.ithome.com.tw/upload/images/20171227/20105694jJMljCrpAi.jpg

明天待續...

--
小弟不才,
如果有謬誤或是要補充的,
都歡迎一起來討論!


上一篇
[Day 17] 使用C#連結MySql(MariaDB)資料庫(三)
下一篇
[Day 19] 使用C#連結MySql(MariaDB)資料庫(四)
系列文
ASP.NET MVC網頁程式介紹30

2 則留言

0
wilson1966
iT邦研究生 5 級 ‧ 2019-01-18 09:57:50

/MS SQL/

/****** Object: Table [dbo].[Village] Script Date: 2019/1/18 上午 09:57:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Village](
[CityId] varchar NOT NULL,
[City] varchar NOT NULL,
[VillageId] varchar NOT NULL,
[Village] varchar NOT NULL
) ON [PRIMARY]
GO

0
wilson1966
iT邦研究生 5 級 ‧ 2019-01-18 09:59:05

INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','001','朴子市');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','002','布袋鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','003','大林鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','004','民雄鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','005','溪口鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','006','新港鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','007','六腳鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','008','東石鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','009','義竹鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','010','鹿草鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','011','水上鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','012','中埔鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','013','竹崎鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','014','梅山鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','015','番路鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','016','大埔鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('2','連江縣','017','南竿鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('2','連江縣','018','北竿鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('2','連江縣','019','莒光鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('2','連江縣','020','東引鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('3','金門縣','021','金城鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('3','金門縣','022','金沙鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('3','金門縣','023','金湖鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('3','金門縣','024','金寧鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('3','金門縣','025','烈嶼鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('3','金門縣','026','烏坵鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('4','宜蘭縣','027','宜蘭市');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('4','宜蘭縣','028','羅東鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('4','宜蘭縣','029','蘇澳鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('4','宜蘭縣','030','頭城鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('4','宜蘭縣','031','礁溪鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('4','宜蘭縣','032','壯圍鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('4','宜蘭縣','033','員山鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('4','宜蘭縣','034','冬山鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('4','宜蘭縣','035','五結鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('4','宜蘭縣','036','三星鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('4','宜蘭縣','037','大同鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('4','宜蘭縣','038','南澳鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','039','桃園區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','040','中壢區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','041','大溪區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','042','阿里山鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','043','楊梅區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','044','屏東市');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','045','蘆竹區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','046','潮州鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','047','東港鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','048','恆春鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','049','萬丹鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','050','大園區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','051','龜山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','052','八德區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','053','長治鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','054','龍潭區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','055','平鎮區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','056','麟洛鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','057','新屋區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','058','觀音區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','059','九如鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','060','里港鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','061','鹽埔鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','062','高樹鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('5','桃園市','063','復興區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','064','竹北市');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','065','竹東鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','066','新埔鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','067','關西鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','068','湖口鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','069','新豐鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','070','芎林鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','071','橫山鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','072','北埔鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','073','寶山鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','074','峨眉鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','075','尖石鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('7','新竹縣','076','五峰鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','077','苗栗市');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','078','苑裡鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','079','通霄鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','080','竹南鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','081','頭份鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','082','後龍鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','083','卓蘭鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','084','大湖鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','085','公館鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','086','銅鑼鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','087','南庄鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','088','頭屋鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','089','三義鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','090','西湖鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','091','造橋鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','092','三灣鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','093','獅潭鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('8','苗栗縣','094','泰安鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','095','彰化市');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','096','鹿港鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','097','和美鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','098','線西鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','099','伸港鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','100','福興鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','101','秀水鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','102','花壇鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','103','芬園鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','104','員林鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','105','溪湖鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','106','田中鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','107','大村鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','108','埔鹽鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','109','埔心鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','110','永靖鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','111','社頭鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','112','二水鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','113','北斗鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','114','二林鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','115','田尾鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','116','埤頭鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','117','芳苑鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','118','大城鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','119','竹塘鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('9','彰化縣','120','溪州鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','121','南投市');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','122','埔里鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','123','草屯鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','124','竹山鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','125','集集鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','126','名間鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','127','鹿谷鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','128','中寮鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','129','魚池鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','130','國姓鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','131','水里鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','132','信義鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('10','南投縣','133','仁愛鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','134','斗六市');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','135','斗南鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','136','虎尾鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','137','西螺鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','138','土庫鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','139','北港鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','140','古坑鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','141','大埤鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','142','莿桐鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','143','林內鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','144','二崙鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','145','崙背鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','146','麥寮鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','147','東勢鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','148','褒忠鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','149','臺西鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','150','元長鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','151','四湖鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','152','口湖鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('11','雲林縣','153','水林鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('1','嘉義縣','154','太保市');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','155','萬巒鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','156','內埔鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','157','竹田鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','158','新埤鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','159','枋寮鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','160','新園鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','161','崁頂鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','162','林邊鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','163','南州鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','164','佳冬鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','165','琉球鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','166','車城鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','167','滿州鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','168','枋山鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','169','三地門鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','170','霧臺鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','171','瑪家鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','172','泰武鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','173','來義鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','174','春日鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','175','獅子鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('6','屏東縣','176','牡丹鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','177','臺東市');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','178','成功鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','179','關山鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','180','卑南鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','181','鹿野鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','182','池上鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','183','東河鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','184','長濱鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','185','太麻里鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','186','大武鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','187','海端鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','188','延平鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','189','達仁鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','190','花蓮市');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','191','鳳林鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','192','玉里鎮');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','193','新城鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','194','吉安鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','195','壽豐鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','196','光復鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','197','豐濱鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','198','瑞穗鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','199','富里鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','200','秀林鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','201','萬榮鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('13','花蓮縣','202','卓溪鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('14','澎湖縣','203','馬公市');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('14','澎湖縣','204','湖西鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('14','澎湖縣','205','白沙鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('14','澎湖縣','206','西嶼鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('14','澎湖縣','207','望安鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('14','澎湖縣','208','七美鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('15','基隆市','209','中正區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('15','基隆市','210','七堵區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('15','基隆市','211','暖暖區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('15','基隆市','212','仁愛區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('15','基隆市','213','中山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('15','基隆市','214','安樂區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('15','基隆市','215','信義區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('16','新竹市','216','東區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('16','新竹市','217','北區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('16','新竹市','218','香山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('17','嘉義市','219','東區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('17','嘉義市','220','西區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('18','臺北市','221','松山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('18','臺北市','222','信義區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('18','臺北市','223','大安區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('18','臺北市','224','中山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('18','臺北市','225','中正區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('18','臺北市','226','大同區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('18','臺北市','227','萬華區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('18','臺北市','228','文山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('18','臺北市','229','南港區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('18','臺北市','230','內湖區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('18','臺北市','231','士林區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('18','臺北市','232','北投區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','233','鹽埕區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','234','鼓山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','235','左營區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','236','楠梓區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','237','三民區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','238','新興區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','239','前金區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','240','苓雅區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','241','前鎮區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','242','旗津區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','243','小港區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','244','鳳山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','245','林園區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','246','大寮區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','247','大樹區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','248','大社區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','249','仁武區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','250','鳥松區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','251','岡山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','252','橋頭區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','253','燕巢區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','254','田寮區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','255','阿蓮區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','256','路竹區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','257','湖內區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','258','茄萣區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','259','永安區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','260','彌陀區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','261','梓官區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','262','旗山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','263','美濃區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','264','六龜區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','265','甲仙區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','266','杉林區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','267','內門區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','268','茂林區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','269','桃源區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('19','高雄市','270','那瑪夏區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','271','板橋區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','272','三重區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','273','中和區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','274','永和區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','275','新莊區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','276','新店區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','277','樹林區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','278','鶯歌區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','279','三峽區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','280','淡水區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','281','汐止區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','282','瑞芳區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','283','土城區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','284','蘆洲區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','285','五股區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','286','泰山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','287','林口區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','288','深坑區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','289','石碇區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','290','坪林區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','291','三芝區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','292','石門區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','293','八里區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','294','平溪區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','295','雙溪區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','296','貢寮區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','297','金山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','298','萬里區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('20','新北市','299','烏來區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','300','中區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','301','東區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','302','南區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','303','西區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','304','北區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','305','西屯區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','306','南屯區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','307','北屯區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','308','豐原區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','309','東勢區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','310','大甲區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','311','清水區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','312','沙鹿區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','313','后里區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','314','神岡區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','315','潭子區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','316','大雅區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','317','新社區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','318','石岡區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','319','外埔區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','320','大安區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','321','烏日區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','322','大肚區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','323','龍井區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','324','霧峰區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','325','太平區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','326','大里區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','327','和平區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','328','新營區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','329','鹽水區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','330','白河區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','331','柳營區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','332','後壁區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','333','東山區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','334','麻豆區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','335','下營區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','336','六甲區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','337','官田區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','338','大內區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','339','佳里區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','340','學甲區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','341','西港區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','342','七股區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','343','將軍區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','344','北門區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','345','新化區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','346','善化區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','347','新市區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','348','安定區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','349','山上區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','350','玉井區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','351','楠西區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','352','南化區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','353','左鎮區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','354','仁德區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','355','歸仁區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','356','關廟區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','357','龍崎區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','358','永康區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','359','東區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','360','南區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','361','北區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','362','安南區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','363','安平區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('22','臺南市','364','中西區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('21','臺中市','365','梧棲區');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','366','金峰鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','367','蘭嶼鄉');
INSERT INTO Village (CityId,City,VillageId,Village) VALUES('12','臺東縣','368','綠島鄉');

ID 是亂編的,僅供參考,非正式資料

我要留言

立即登入留言