Processing data to generate sql insert statements in batches.

Processing data to generate sql insert statements in batches.

I'm working on the weather forecast module recently. First, I need to convert the ip of the client's public network into a city, and then convert the name of the city into the corresponding city code. I found the city code online, but I need to deal with it. Have a look, there are more than 300 cities and their corresponding city codes, and I want to save them in the database. I just want to be a tool for data processing to automatically generate sql statements to improve efficiency.

1 municipality

2 Beijing, Shanghai, Tianjin and Chongqing

3 " 10 10 10 100"," 10 1020 100"," 10 1030 100"," 10 1040 100"

four

Five special administrative regions

6 "Hong Kong" and "Macau"

7 " 10 1320 10 1"," 10 1330 10 1"

eight

9 Heilongjiang

10 Harbin, Qiqihar, Mudanjiang, Daqing, Yichun, Shuangyashan, Hegang, Jixi, Jiamusi, Qitaihe, Heihe, Suihua and Daxinganling

1 1 " 10 1050 10 1"," 10 105020 1"," 10 105030 1", " 10 105090 1", " 10 105080 1"," 10 105 130 1", " 10 105 120 1"," 10 105 1 10 1", " 10 105040 1", " 10 105 1002"," 10 105060 1"," 10 105050 1"," 10 105070 1"

12

13 Jilin

14 Changchun, Yanji, Jilin, Baishan, Baicheng, Siping, Songyuan, Liaoyuan, Daan and Tonghua.

15 " 10 1060 10 1"," 10 106030 1"," 10 106020 1", " 10 106090 1", " 10 106060 1"," 10 106040 1"," 10 106080 1", " 10 106070 1"," 10 1060603"," 10 106050 1"

16

17 Liaoning

18 Shenyang, Dalian, Huludao, Panjin, Benxi, Fushun, Tieling, Liaoyang, Yingkou, Fuxin, Chaoyang, Jinzhou, Dandong and Anshan.

19 " 10 1070 10 1"," 10 107020 1"," 10 107 140 1", " 10 107 130 1"," 10 107050 1"," 10 107040 1", " 10 107 1 10 1", " 10 107 100 1"," 10 107080 1", " 10 107090 1"," 10 107 120 1", " 10 107070 1"," 10 107060 1"," 10 107030 1"

20

2 1 Inner Mongolia

Hohhot, Hulunbeier, Xilinhot, Baotou, Chifeng, Hailar, Wuhai, Ordos and Tongliao.

23 " 10 1080 10 1"," 10 108 1000"," 10 108090 1", " 10 108020 1", " 10 108060 1"," 10 108 100 1", " 10 108030 1"," 10 108070 1"," 10 108050 1"

24

25 Hebei

Shijiazhuang, Tangshan, Zhangjiakou, Langfang, Xingtai, Handan, Cangzhou, Hengshui, Chengde, Baoding and Qinhuangdao.

27 " 10 1090 10 1"," 10 109050 1"," 10 109030 1", " 10 109060 1", " 10 109090 1"," 10 109 100 1", " 10 109070 1"," 10 109080 1"," 10 1090402", " 10 109020 1"," 10 109 1 10 1"

28

29 Henan

Zhengzhou, Kaifeng, Luoyang, Pingdingshan, Jiaozuo, Hebi, Xinxiang, Anyang, Puyang, Xuchang, Luohe, Sanmenxia, Nanyang, Shangqiu, Xinyang and Zhoukou.

3 1 " 10 1 180 10 1"," 10 1 18080 1", " 10 1 18090 1", " 10 1 18050 1"," 10 1 18 1 10 1", " 10 1 18 120 1", " 10 1 18030 1"," 10 1 18020 1", " 10 1 18 130 1"," 10 1 18040 1", " 10 1 18 150 1", " 10 1 18 170 1"," 10 1 18070 1", " 10 1 18 100 1", " 10 1 18060 1"," 10 1 18 140 1"," 10 1 18 160 1"

32

33 Shandong

Jinan, Qingdao, Zibo, Weihai, Qufu, Linyi, Yantai, Zaozhuang, Liaocheng, Jining, Heze, Taian, Rizhao, Dongying, Dezhou and Binzhou.

35 " 10 1 120 10 1"," 10 1 12020 1"," 10 1 12030 1", " 10 1 12 130 1"," 10 1 1207 10"," 10 1 12090 1", " 10 1 12050 1", " 10 1 12 140 1"," 10 1 12 170 1", " 10 1 12070 1", " 10 1 12 100 1"," 10 1 12080 1", " 10 1 12 150 1", " 10 1 12 120 1"," 10 1 12040 1", " 10 1 12 1 10 1", " 10 1 12 160 1"," 10 1 12060 1"

36

37 Shanxi

Taiyuan, Yangquan, Jincheng, Jinzhong, Linfen, Yuncheng, Changzhi, Shuozhou, Xinzhou, Datong and Lvliang.

39 " 10 1 100 10 1"," 10 1 10030 1"," 10 1 10060 1", " 10 1 10040 1"," 10 1 10070 1"," 10 1 10080 1", " 10 1 10050 1", " 10 1 10090 1"," 10 1 10 100 1", " 10 1 10020 1"," 10 1 10 1 10 1"

40

4 1 Jiangsu

42 "Nanjing", "Suzhou", "Kunshan", "Nantong", "Taicang", "Wuxian", "Xuzhou", "Yixing", "Zhenjiang", "Huai 'an", "Changshu", "Yancheng", "Taizhou", "Wuxi" and "Lianyungang",

43 " 10 1 190 10 1"," 10 1 19040 1"," 10 1 190404", " 10 1 19050 1"," 10 1 190408"," 10 1 190406", " 10 1 19080 1", " 10 1 190203"," 10 1 19030 1", " 10 1 19090 1"," 10 1 190402", " 10 1 19070 1", " 10 1 19 120 1"," 10 1 19020 1", " 10 1 19 100 1", " 10 1 19060 1"," 10 1 19 1 10 1"," 10 1 19 130 1"

Forty-four

45 Anhui

Hefei, Chaohu, Bengbu, Anqing, Lu 'an, Chuzhou, Ma 'anshan, Fuyang, Xuancheng, Tongling, Huaibei, Wuhu, Mizhou, Suzhou, Huainan, "

47 " 10 1220 10 1"," 10 122 160 1"," 10 122020 1", " 10 122060 1", " 10 122 150 1"," 10 122 1 10 1", " 10 122050 1"," 10 122080 1", " 10 122 140 1", " 10 122 130 1"," 10 122 120 1"," 10 122030 1", " 10 122090 1"," 10 122070 1"," 10 122040 1"," 10 122 170 1"

48

49 Shaanxi

50“Xi 'an, Hancheng, Ankang, Hanzhong, Baoji, Xianyang, Yulin, Weinan, Shangluo, Tongchuan and Yan 'an.

5 1 " 10 1 1 10 10 1"," 10 1 1 105 10", " 10 1 1 1070 1", " 10 1 1 1080 1", " 10 1 1 1090 1"," 10 1 1 10200", " 10 1 1 1040 1", " 10 1 1 1050 1"," 10 1 1 1060 1", " 10 1 1 1 100 1"," 10 1 1 10300"

Fifty two

53 Ningxia

Yinchuan, Guyuan, Zhongwei, Shizuishan and Wuzhong

55 " 10 1 170 10 1"," 10 1 17040 1"," 10 1 17050 1", " 10 1 17020 1"," 10 1 17030 1"

Fifty-six

57 Gansu

Lanzhou, Baiyin, Qingyang, Jiuquan, Tianshui, Wuwei, Zhangye, Gannan, Linxia, Pingliang, Dingxi and Jinchang.

59 " 10 1 160 10 1"," 10 1 16 130 1", " 10 1 16040 1", " 10 1 16080 1"," 10 1 16090 1", " 10 1 16050 1"," 10 1 16070 1", " 10 1050204", " 10 1 16 1 10 1"," 10 1 16030 1"," 10 1 16020 1"," 10 1 16060 1"

60

6 1 Qinghai

Xining, Haibei, Haixi, Huangnan, Guoluo, Yushu, Haidong and Hainan.

63 " 10 1 150 10 1"," 10 1 15080 1"," 10 1 15070 1", " 10 1 15030 1"," 10 1 15050 1"," 10 1 15060 1", " 10 1 15020 1"," 10 1 15040 1"

64

65 Hubei

Wuhan, Yichang, Huanggang, Enshi, Jingzhou, Shennongjia, Shiyan, Xianning, Xiangyang, Xiaogan, Suizhou, Huangshi, Jingmen and Ezhou.

67 " 10 1200 10 1"," 10 120090 1"," 10 120050 1", " 10 120 100 1", " 10 120080 1"," 10 120 120 1", " 10 120 1 10 1"," 10 120070 1", " 10 120020 1", " 10 120040 1"," 10 120 130 1"," 10 120060 1"," 10 120 140 1"," 10 120030 1"

Sixty-eight

69 Hunan

Changsha, Shaoyang, Changde, Chenzhou, Jishou, Zhuzhou, Loudi, Xiangtan, Yiyang, Yongzhou, Yueyang, Hengyang, Huaihua, Shaoshan and Zhangjiajie.

7 1 " 10 1250 10 1"," 10 125090 1"," 10 125060 1", " 10 125050 1", " 10 125 150 1"," 10 125030 1", " 10 125080 1"," 10 125020 1", " 10 125070 1", " 10 125 140 1"," 10 125 100 1"," 10 125040 1", " 10 125 120 1"," 10 1250202"," 10 125 1 10 1"

Seventy two

73 Zhejiang

Hangzhou, Huzhou, Jinhua, Ningbo, Lishui, Shaoxing, Quzhou, Jiaxing, Taizhou, Zhoushan and Wenzhou.

75 " 10 12 10 10 1"," 10 12 1020 1"," 10 12 1090 1", " 10 12 1040 1"," 10 12 1080 1"," 10 12 1050 1", " 10 12 1 100 1", " 10 12 1030 1"," 10 12 1060 1", " 10 12 1 1 10 1"," 10 12 1070 1"

76

77 Jiangxi

Nanchang, Pingxiang, Jiujiang, Shangrao, Fuzhou, Ji 'an, yingtan, Yichun, Xinyu, Jingdezhen and Ganzhou.

79 " 10 1240 10 1"," 10 124090 1"," 10 124020 1", " 10 124030 1", " 10 124040 1"," 10 124060 1", " 10 124 1 10 1"," 10 124050 1", " 10 124 100 1", " 10 124080 1"," 10 124070 1"

80

8 1 Fujian

Fuzhou, Xiamen, Longyan, Nanping, Ningde, Putian, Quanzhou, Sanming and Zhangzhou.

83 " 10 1230 10 1"," 10 123020 1"," 10 123070 1", " 10 123090 1", " 10 123030 1"," 10 123040 1"," 10 123050 1", " 10 123080 1"," 10 123060 1"

84

85 Guizhou

Guiyang, Anshun, Chishui, Zunyi, Tongren, Liupanshui, Bijie, Kaili and Duyun.

87 " 10 1260 10 1"," 10 126030 1"," 10 1260208"," 10 126020 1", " 10 126060 1"," 10 126080 1"," 10 126070 1"," 10 126050 1"," 10 126040 1"

88

89 Sichuan

90 Chengdu, Luzhou, Neijiang, Liangshan, Aba, Bazhong, Guangyuan, Leshan, Mianyang, Deyang, Panzhihua, Ya 'an, Yibin, Zigong, Ganzi Prefecture, "

9 1 " 10 1270 10 1"," 10 127 100 1"," 10 127 120 1", " 10 127 160 1"," 10 127 190 1"," 10 127090 1", " 10 1272 10 1", " 10 127 140 1"," 10 127040 1", " 10 127200 1"," 10 127020 1", " 10 127 170 1", " 10 127 1 10 1"," 10 127030 1"," 10 127 180 1", " 10 127060 1"," 10 127 130 1"," 10 127080 1", " 10 127070 1"," 10 127 150 1"," 10 127050 1"

92

93 Guangdong

Guangzhou, Shenzhen, Chaozhou, Shaoguan, Zhanjiang, Huizhou, Qingyuan, Dongguan, Jiangmen, Maoming, Zhaoqing, Shanwei, Heyuan, Jieyang, Meizhou and Zhongshan.

95 " 10 1280 10 1"," 10 128060 1"," 10 128 150 1", " 10 128020 1", " 10 128 100 1"," 10 128030 1", " 10 128 130 1"," 10 128 160 1", " 10 128 1 10 1", " 10 128200 1"," 10 128090 1"," 10 1282 10 1", " 10 128 120 1"," 10 128 190 1"," 10 128040 1", " 10 128 170 1", " 10 1280905"," 10 128 180 1", " 10 128 140 1"," 10 128070 1"," 10 128050 1"," 10 1280800"

96

97 Guangxi

98 "Nanning", "Guilin", "Yangshuo", "Liuzhou", "Wuzhou", "Yulin", "Guiping", "Hezhou", "Qinzhou", "Guigang", "Fangchenggang", "Baise", "Beihai", "Hechi", "Laibin", ".

99 " 10 1300 10 1"," 10 130050 1"," 10 13005 10", " 10 130030 1", " 10 130060 1"," 10 130090 1"," 10 1300802", " 10 130070 1", " 10 130 1 10 1"," 10 130080 1", " 10 130 140 1"," 10 130 100 1", " 10 130 130 1", " 10 130 120 1"," 10 130040 1"," 10 130020 1"

100

10 1 Yunnan

102 Kunming, Baoshan, Chuxiong, Dehong, Honghe, Lincang, Nujiang, Qujing, Simao, Wenshan, Yuxi, Zhaotong, Lijiang and Dali

103 " 10 1290 10 1"," 10 129050 1"," 10 129080 1", " 10 129 150 1", " 10 129030 1"," 10 129 1 10 1", " 10 129 120 1"," 10 129040 1", " 10 129090 1", " 10 129060 1"," 10 129070 1"," 10 129 100 1"," 10 129 140 1"," 10 129020 1"

104

105 Hainan

106 Haikou, Sanya, Danzhou, Qiongshan, Tongshi and Wenchang

107 " 10 13 10 10 1"," 10 13 1020 1"," 10 13 10205", " 10 13 10 102"," 10 13 10222"," 10 13 102 12"

108

109 Xinjiang

1 10 Urumqi, Altay, Aksu, Changji, Hami, Hotan, Kashgar, Karamay, Shihezi, Tacheng, Korla, Turpan and Yining.

1 1 1 " 10 1 130 10 1"," 10 1 13 140 1", " 10 1 13080 1", " 10 1 13040 1"," 10 1 13 120 1", " 10 1 13 130 1", " 10 1 13090 1"," 10 1 13020 1", " 10 1 13030 1", " 10 1 13 1 10 1"," 10 1 13060 1", " 10 1 13050 1"," 10 1 13 100 1"

1 12

1 13 Tibet

1 14 Lhasa, Ali, Qamdo, Naqu, Shigatse, Shannan and Linzhi

1 15 " 10 1 140 10 1"," 10 1 14070 1", " 10 1 14050 1", " 10 1 14060 1"," 10 1 14020 1", " 10 1 14030 1"," 10 1 14040 1"

1 16

1 17 Taiwan Province province

1 18 "Taipei" and "Kaohsiung"

1 19 " 10 1340 102"," 10 134020 1"

City code

It looks messy, and the corresponding relationship is one line for each province and city and one line for each code, which is enclosed by quotation marks and separated by commas. There is no symbol separation between each line, and there is no reference to provincial names. The first thing is to remove the provincial names, because each city name is different. I want to deal with it every two lines, but it is also very laborious and prone to mistakes. Just come up with an algorithm that simply handles it once.

Ps: The interface is very simple, with input data above, conversion in the middle and output data below.

Backstage main code:

[csharp] view plain text

Private void button 1_Click (object sender, EventArgs e)

{

String data = textBox 1. Text.Replace("r "，" ")。 Replace ("n", ""). Replace ("t", ""). Replace ("",""). Replace ("",""). Replace ("","");

match collection matchsdata = matches(data，" "[sS]*？ "");

String[,] temps = new string [matchsdata. Count / 2，2]；

int count0 = 0

int count 1 = 0；

String input = string. Empty;

Foreach (matching m in matchsdata)

{

string tempdata = m.Value.Replace(" "，" ")；

attempt

{

int tryp = int。 parse(tempdata)；

temps[count 1， 1]= tempdata；

count 1++；

}

Catch (exception ex)

{

temps[count0，0]= tempdata；

count 0++；

}

}

for(int I = 0； I < (match the data. count/2)； i++)

{

Input+= "insert into TBL _ citycode (c _ city, c_code) value (+temps[i, 0]+,+temps[i,1]+) rn";

}

Text box 2. Text = input;

}

Public static MatchCollection match (string str, string exp)

{

Returns a regular expression. Match (string, expression, regular expression. ignore case)；

}

The first is to process the input data and delete line breaks, spaces and so on. Then you should get a row of data, and then match all the referenced data through regular expressions. You will find that all the data you need are quoted, but how to distinguish the city name from the city code? They are mixed together. Don't worry, did you find out? The city name is a string and the city code is a string of numbers. We only need to traverse the matched data array, and each line of data will be converted into int type, so that the line of city name will be reported as an error and captured in catch, that is, the city name, that is, the city code. You only need to save the data into a two-dimensional array and corresponding columns at a time. This will get the corresponding city name and city code. The generated sql statement should correspond to the corresponding database table.

Table structure:

After the conversion, just put the generated sql statement into the inquirer for execution. * * * 349 cities were dealt with.

Finally, I didn't trust my own algorithm, and I chose a few data casually, and there were no mistakes.

& ltscript type = " text/JavaScript " & gt； & lt！ -Google _ ad _ client = " ca-pub- 1944 176 156 128447 "； /* cnblogs home banner */Google _ ad _ slot = "5419468456"; google _ ad _ width = 728google _ ad _ height = 90//-& gt； & lt/script & gt； & ltscript type = " text/JavaScript " src = "/pagead/show _ ads . js " & gt； & lt/script & gt；