處理數(shù)據(jù)批量生成sql插入語(yǔ)句

最近在做一個(gè)天氣預(yù)報(bào)模塊，首先需要將客戶(hù)端公網(wǎng)ip轉(zhuǎn)換成所在城市，然后將所在城市名轉(zhuǎn)換成對(duì)應(yīng)的城市代碼，在網(wǎng)上找到了城市代碼，但是需要處理一下，看了看，有三百多城市及對(duì)應(yīng)的城市代碼，想存到數(shù)據(jù)庫(kù)。就想著做一個(gè)數(shù)據(jù)處理自動(dòng)生成sql語(yǔ)句的工具，提高效率。
  1 直轄市
  2     "北京","上海","天津","重慶"
  3     "101010100","101020100","101030100","101040100"
  4
  5 特別行政區(qū)
  6     "香港","澳門(mén)"
  7     "101320101","101330101"
  8
  9 黑龍江
 10     "哈爾濱","齊齊哈爾","牡丹江","大慶","伊春","雙鴨山","鶴崗","雞西","佳木斯","七臺(tái)河","黑河","綏化","大興安嶺"
 11     "101050101","101050201","101050301","101050901","101050801","101051301","101051201","101051101","101050401","101051002","101050601","101050501","101050701"
 12     
 13 吉林
 14     "長(zhǎng)春","延吉","吉林","白山","白城","四平","松原","遼源","大安","通化"
 15     "101060101","101060301","101060201","101060901","101060601","101060401","101060801","101060701","101060603","101060501"
 16     
 17 遼寧
 18     "沈陽(yáng)","大連","葫蘆島","盤(pán)錦","本溪","撫順","鐵嶺","遼陽(yáng)","營(yíng)口","阜新","朝陽(yáng)","錦州","丹東","鞍山"
 19     "101070101","101070201","101071401","101071301","101070501","101070401","101071101","101071001","101070801","101070901","101071201","101070701","101070601","101070301"
 20     
 21 內(nèi)蒙古
 22     "呼和浩特","呼倫貝爾","錫林浩特","包頭","赤峰","海拉爾","烏海","鄂爾多斯","通遼"
 23     "101080101","101081000","101080901","101080201","101080601","101081001","101080301","101080701","101080501"
 24
 25 河北
 26     "石家莊","唐山","張家口","廊坊","邢臺(tái)","邯鄲","滄州","衡水","承德","保定","秦皇島"
 27     "101090101","101090501","101090301","101090601","101090901","101091001","101090701","101090801","101090402","101090201","101091101"
 28     
 29 河南
 30     "鄭州","開(kāi)封","洛陽(yáng)","平頂山","焦作","鶴壁","新鄉(xiāng)","安陽(yáng)","濮陽(yáng)","許昌","漯河","三門(mén)峽","南陽(yáng)","商丘","信陽(yáng)","周口","駐馬店"
 31     "101180101","101180801","101180901","101180501","101181101","101181201","101180301","101180201","101181301","101180401","101181501","101181701","101180701","101181001","101180601","101181401","101181601"
 32     
 33 山東
 34     "濟(jì)南","青島","淄博","威海","曲阜","臨沂","煙臺(tái)","棗莊","聊城","濟(jì)寧","菏澤","泰安","日照","東營(yíng)","德州","濱州","萊蕪","濰坊"
 35     "101120101","101120201","101120301","101121301","101120710","101120901","101120501","101121401","101121701","101120701","101121001","101120801","101121501","101121201","101120401","101121101","101121601","101120601"
 36     
 37 山西
 38     "太原","陽(yáng)泉","晉城","晉中","臨汾","運(yùn)城","長(zhǎng)治","朔州","忻州","大同","呂梁"
 39     "101100101","101100301","101100601","101100401","101100701","101100801","101100501","101100901","101101001","101100201","101101101"
 40     
 41 江蘇
 42     "南京","蘇州","昆山","南通","太倉(cāng)","吳縣","徐州","宜興","鎮(zhèn)江","淮安","常熟","鹽城","泰州","無(wú)錫","連云港","揚(yáng)州","常州","宿遷"
 43     "101190101","101190401","101190404","101190501","101190408","101190406","101190801","101190203","101190301","101190901","101190402","101190701","101191201","101190201","101191001","101190601","101191101","101191301"
 44     
 45 安徽
 46     "合肥","巢湖","蚌埠","安慶","六安","滁州","馬鞍山","阜陽(yáng)","宣城","銅陵","淮北","蕪湖","毫州","宿州","淮南","池州"
 47     "101220101","101221601","101220201","101220601","101221501","101221101","101220501","101220801","101221401","101221301","101221201","101220301","101220901","101220701","101220401","101221701"
 48     
 49 陜西
 50     "西安","韓城","安康","漢中","寶雞","咸陽(yáng)","榆林","渭南","商洛","銅川","延安"
 51     "101110101","101110510","101110701","101110801","101110901","101110200","101110401","101110501","101110601","101111001","101110300"
 52     
 53 寧夏
 54     "銀川","固原","中衛(wèi)","石嘴山","吳忠"
 55     "101170101","101170401","101170501","101170201","101170301"
 56     
 57 甘肅
 58     "蘭州","白銀","慶陽(yáng)","酒泉","天水","武威","張掖","甘南","臨夏","平?jīng)?,"定西","金昌"
 59     "101160101","101161301","101160401","101160801","101160901","101160501","101160701","101050204","101161101","101160301","101160201","101160601"
 60     
 61 青海
 62     "西寧","海北","海西","黃南","果洛","玉樹(shù)","海東","海南"
 63     "101150101","101150801","101150701","101150301","101150501","101150601","101150201","101150401"
 64     
 65 湖北
 66     "武漢","宜昌","黃岡","恩施","荊州","神農(nóng)架","十堰","咸寧","襄陽(yáng)","孝感","隨州","黃石","荊門(mén)","鄂州"
 67 "101200101","101200901","101200501","101201001","101200801","101201201","101201101","101200701","101200201","101200401","101201301","101200601","101201401","101200301"
 68     
 69 湖南
 70     "長(zhǎng)沙","邵陽(yáng)","常德","郴州","吉首","株洲","婁底","湘潭","益陽(yáng)","永州","岳陽(yáng)","衡陽(yáng)","懷化","韶山","張家界"
 71     "101250101","101250901","101250601","101250501","101251501","101250301","101250801","101250201","101250701","101251401","101251001","101250401","101251201","101250202","101251101"
 72     
 73 浙江
 74     "杭州","湖州","金華","寧波","麗水","紹興","衢州","嘉興","臺(tái)州","舟山","溫州"
 75     "101210101","101210201","101210901","101210401","101210801","101210501","101211001","101210301","101210601","101211101","101210701"
 76     
 77 江西
 78     "南昌","萍鄉(xiāng)","九江","上饒","撫州","吉安","鷹潭","宜春","新余","景德鎮(zhèn)","贛州"
 79     "101240101","101240901","101240201","101240301","101240401","101240601","101241101","101240501","101241001","101240801","101240701"
 80     
 81 福建
 82     "福州","廈門(mén)","龍巖","南平","寧德","莆田","泉州","三明","漳州"
 83     "101230101","101230201","101230701","101230901","101230301","101230401","101230501","101230801","101230601"
 84     
 85 貴州
 86     "貴陽(yáng)","安順","赤水","遵義","銅仁","六盤(pán)水","畢節(jié)","凱里","都勻"
 87     "101260101","101260301","101260208","101260201","101260601","101260801","101260701","101260501","101260401"
 88     
 89 四川
 90     "成都","瀘州","內(nèi)江","涼山","阿壩","巴中","廣元","樂(lè)山","綿陽(yáng)","德陽(yáng)","攀枝花","雅安","宜賓","自貢","甘孜州","達(dá)州","資陽(yáng)","廣安","遂寧","眉山","南充"
 91     "101270101","101271001","101271201","101271601","101271901","101270901","101272101","101271401","101270401","101272001","101270201","101271701","101271101","101270301","101271801","101270601","101271301","101270801","101270701","101271501","101270501"
 92     
 93 廣東
 94     "廣州","深圳","潮州","韶關(guān)","湛江","惠州","清遠(yuǎn)","東莞","江門(mén)","茂名","肇慶","汕尾","河源","揭陽(yáng)","梅州","中山","德慶","陽(yáng)江","云浮","珠海","汕頭","佛山"
 95     "101280101","101280601","101281501","101280201","101281001","101280301","101281301","101281601","101281101","101282001","101280901","101282101","101281201","101281901","101280401","101281701","101280905","101281801","101281401","101280701","101280501","101280800"
 96     
 97 廣西
 98     "南寧","桂林","陽(yáng)朔","柳州","梧州","玉林","桂平","賀州","欽州","貴港","防城港","百色","北海","河池","來(lái)賓","崇左"
 99     "101300101","101300501","101300510","101300301","101300601","101300901","101300802","101300701","101301101","101300801","101301401","101301001","101301301","101301201","101300401","101300201"
100     
101 云南
102     "昆明","保山","楚雄","德宏","紅河","臨滄","怒江","曲靖","思茅","文山","玉溪","昭通","麗江","大理"
103     "101290101","101290501","101290801","101291501","101290301","101291101","101291201","101290401","101290901","101290601","101290701","101291001","101291401","101290201"
104     
105 海南
106     "?？?,"三亞","儋州","瓊山","通什","文昌"
107     "101310101","101310201","101310205","101310102","101310222","101310212"
108     
109 新疆
110     "烏魯木齊","阿勒泰","阿克蘇","昌吉","哈密","和田","喀什","克拉瑪依","石河子","塔城","庫(kù)爾勒","吐魯番","伊寧"
111     "101130101","101131401","101130801","101130401","101131201","101131301","101130901","101130201","101130301","101131101","101130601","101130501","101131001"
112     
113 西藏
114     "拉薩","阿里","昌都","那曲","日喀則","山南","林芝"
115     "101140101","101140701","101140501","101140601","101140201","101140301","101140401"
116     
117 臺(tái)灣
118     "臺(tái)北","高雄"
119     "101340102","101340201"

城市代碼

一看上去很亂的，而且對(duì)應(yīng)關(guān)系是每個(gè)省城市一行，代碼一行，分別用引號(hào)引起，用逗號(hào)分隔，每行間都沒(méi)有符號(hào)分隔，省名沒(méi)有用引號(hào)。首先是想著把省名去掉，因?yàn)槊總€(gè)城市名都是不相同的。想著每?jī)尚袃尚械娜ヌ幚恚且惨M(fèi)不少功夫，還容易出錯(cuò)。就想個(gè)索性一次性的全處理的算法。

   ps：界面很簡(jiǎn)單，上面是輸入數(shù)據(jù)，中間是轉(zhuǎn)換，下面是輸出數(shù)據(jù)。

后臺(tái)主要代碼：
[csharp] view plain copy

    private void button1_Click(object sender, EventArgs e)  
    {  
        string data = textBox1.Text.Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace(" ", "").Replace(" ", "").Replace("    ", "");  
        MatchCollection matchsdata = matches(data, "\"[\\s\\S]*?\"");  
        string[,] temps = new string[matchsdata.Count / 2, 2];  
        int count0 = 0;  
        int count1 = 0;  
        string input = string.Empty;  
        foreach (Match m in matchsdata)  
        {  
            string tempdata = m.Value.Replace("\"", "");  
            try  
            {  
                int tryp = int.Parse(tempdata);  
                temps[count1, 1] = tempdata;  
                count1++;  
            }  
            catch (Exception ex)  
            {  
                temps[count0, 0] = tempdata;  
                count0++;  
            }  
        }  
        for (int i = 0; i < (matchsdata.Count / 2); i++)  
        {  
            input += "insert into tbl_CityCode(c_city,c_code) values('" + temps[i, 0] + "','" + temps[i, 1] + "')\r\n";  
        }  
        textBox2.Text = input;  
    }  
      
    public static MatchCollection matches(string str, string exp)  
    {  
        return Regex.Matches(str, exp, RegexOptions.IgnoreCase);  
    }  

 首先是將輸入的數(shù)據(jù)處理，去除換行符，空格什么的。然后你應(yīng)該是會(huì)得到一行數(shù)據(jù)，然后通過(guò)正則表達(dá)式匹配出所有帶引號(hào)的數(shù)據(jù)，你會(huì)發(fā)現(xiàn)需要的數(shù)據(jù)全部都是用引號(hào)引起來(lái)的，但是怎樣區(qū)分城市名和城市代碼呢，它們是混在一起的。不用擔(dān)心，你發(fā)現(xiàn)了嗎？城市名是字符串，城市代碼是一串?dāng)?shù)字，我們只要將匹配出的數(shù)據(jù)數(shù)組遍歷，每一行數(shù)據(jù)都去轉(zhuǎn)換成int類(lèi)型，這樣城市名的行就會(huì)報(bào)錯(cuò)，在catch中捕捉，這一行就是城市名，沒(méi)錯(cuò)的就是城市代碼，把數(shù)據(jù)一次存到一個(gè)二維數(shù)組，對(duì)應(yīng)的列中就行了。這樣就會(huì)獲得了相對(duì)應(yīng)的城市名和城市代碼。生成的sql語(yǔ)句要對(duì)應(yīng)相應(yīng)的數(shù)據(jù)庫(kù)表。
表結(jié)構(gòu)：
轉(zhuǎn)換完了將生成的sql語(yǔ)句放到查詢(xún)器中執(zhí)行就ok了。共處理了349個(gè)城市。
最后不放心自己的算法，隨機(jī)抽查了幾條數(shù)據(jù)，沒(méi)有錯(cuò)誤。

<script type="text/javascript"><!-- google_ad_client = "ca-pub-1944176156128447"; /* cnblogs 首頁(yè)橫幅 */ google_ad_slot = "5419468456"; google_ad_width = 728; google_ad_height = 90; //--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>