純真QQIP庫匯入到SQL Server例項詳解

iSQlServer發表於2010-09-26
SQLServer
      相信很多人都是需要通過使用者的IP來確認使用者是屬於哪個區域的，最新的QQIP資料庫純真版20100515，裡面的IP資料記錄已經達到了：378448條。它收集了包括中國電信、中國移動、中國聯通、長城寬頻、聚友寬頻等 ISP 的最新準確 IP 地址資料。包括最全的網咖資料。本來這個IP庫的用意是在QQ上能顯示對方的地理位置，在我們的程式中也偶爾有需要的時候，所以把它匯入到我們的資料庫中成為一張表是非常有必要的。

　　但是很多人會說這有什麼難的呢?但是在實際的操作過程中出現了很多問題，比如：匯入到資料庫的時候報錯;還有就是匯入的記錄後發現記錄數有缺失;匯入時無法用，;等字元來區分欄位等。下面就讓我來告訴你如何成功匯入這378448條資料吧。

　　步驟1

　　點選上面的下載地址，解壓並執行ShowIP.exe，如下圖所示

1

　　(圖1：ShowIP.exe)

　　步驟2

　　點選上面的【解壓】按鈕，儲存檔案為QQIP.txt

　　步驟3

　　到vim.org中下載gvim72.exe程式，這裡為什麼需要下載這個檔案呢?因為如果使用SQL Server的匯入功能匯入檔案QQIP.txt的話會報錯，因為QQIP.txt檔案開啟一看感覺是使用了製表符\t來區分欄位，但是你錯了，無論是在SQL Server2000還是在SQL Server2005都無法識別得了，因為它是使用空格來調整的(見圖2)。既然無法直接匯入，那麼我們該如何使用特殊字元來替換這些空格呢?使用SQL Server2000的特定長度也是無法很好的區分開欄位。一般的文字工具，比如EMEditor就無法替換指定的空格，因為只能是全文替換，那麼當字元是：“我 是 聽風吹雨”，這這段字元中的空格也會給替換掉，而我們恰恰是不想見到這樣的情況的。vim就可以替換一行中的幾個空格。

1

　　(圖2：匯入QQIP.txt時無法識別列)

　　步驟4

　　下載好gvim72.exe後當然是安裝程式啦，安裝完畢後開啟雙擊gvim.exe進行操作。選擇檔案-開啟，選擇QQIP.txt檔案，按住Shift+分號鍵就可以進行執行命令了，輸入“%s/\s\+/$/ ”，按回車就會出現圖3的效果，這就已經替換了兩個字元之間的空格了，一共重複輸入3次。 把最下面的字元“IP資料庫共有資料$：$378448$條”刪除，存檔退出：wq。詳解：QQIP.txt有4列。分別是起始ip，結束ip，地區，說明。列之間用不等數量的空格間隔。為了將此文字檔案到入到SQL Server，需要處理掉這些空格。但是隻能處理掉前3列的空格，最後一列中的空格要保留。vi中輸入的命令意思是，把每一行第一個和其連續的空格替換成字元'$'。

1

　　(圖3：第一次執行命令後的效果)　　


　　步驟5

　　這樣就有了一個全新的QQIP.txt了，那麼接下來就是匯入到資料庫中了，因為資料中有些是沒有說明這一列的，所以使用SQL Server2000的【其它】中設定為“$”來分隔列，是沒有辦法做好的。所以這裡我們先把整個行做了一列匯入到資料中後再使用SQL進行字串分隔。需要注意的是圖5中設定欄位長度為500，如果你沒有設定導致報錯，那麼你就要先去資料庫中刪除這個表，之後再設定欄位長度為500並匯出，如果你沒有刪除表，那麼會一直報錯，因為已經有了這樣一個表了，即使最後報錯了。

1

　　(圖4：以一列作為匯入)

1

　　(圖5：設定欄位長度)

　　步驟6

　　現在已經有了一個叫做[QQIPbase]的表了，表的效果如圖6所示。使用SELECT count(1) FROM [QQIPbase]發現記錄數是378451條，和378448條不相等，再使用DELETE [QQIPbase] WHERE [列 0] =''刪除空白行。請確認資料庫的記錄數是否是：378448條。再執行分析器上執行下面的腳步，這個指令碼的作用就是把圖6中[列 0]欄位的字元進行分隔。

1

　　(圖6：剛匯入後的效果圖)

#div_code img{border:0px;}<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt-- Author:      <聽風吹雨>
-- Create date: <2010/05/27>
-- Description: <把字串@str以@split分隔符進行分隔，返回第@index次匹配的元素>
-- =============================================
CREATE FUNCTION [dbo].[Get_StrArrayStrOfIndex]
(
  @str VARCHAR(5000),  --要分割的字串
  @split VARCHAR(10),  --分隔符號
  @index INT --取第幾個元素
)
RETURNS VARCHAR(5000)
AS
BEGIN
  DECLARE @location INT
  DECLARE @start INT
  DECLARE @next INT
  DECLARE @seed INT
  SET @str=LTRIM(RTRIM(@str))
  SET @start=1
  SET @next=1
  SET @seed=LEN(@split)
  
  SET @location=CHARINDEX(@split,@str)
  WHILE @location<>0 and @index>@next
  BEGIN
    SET @start=@location+@seed
    SET @location=CHARINDEX(@split,@str,@start)
    SET @next=@next+1
  END
  IF @location =0 SELECT @location =LEN(@str)+1
  RETURN SUBSTRING(@str,@start,@location-@start)
END

相關文章