Importing IP2Location data into MSSQL (IPv6)

The aim of this guide is to demonstrate how to import IP2Location data (DB26) in CSV form into Microsoft SQL Server 2008 R2 and then query the data in VB.NET.

First of all, you will need to download the IP2Location DB26 CSV file.
Download commercial version at https://ip2location.com/download?code=DB26IPV6

Extract out the IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV file from the downloaded zipped file and save it to C:\ folder.

Important Note

We will not cover the installation of MSSQL and .NET in this guide. We will assume you have already setup MSSQL and you have .NET 4.0 on your Windows platform.

Creating the table in the database

Run the below SQL command to create a new table in the database for storing the IP2Location data.

CREATE DATABASE ip2location
GO
 
USE ip2location
GO
 
CREATE TABLE [dbo].[ip2location_db26](
      [ip_from] [char](39) NOT NULL,
      [ip_to] [char](39) NOT NULL,
      [country_code] [nvarchar](2) NOT NULL,
      [country_name] [nvarchar](64) NOT NULL,
      [region_name] [nvarchar](128) NOT NULL,
      [city_name] [nvarchar](128) NOT NULL,
      [latitude] [float] NOT NULL,
      [longitude] [float] NOT NULL,
      [zip_code] [nvarchar](30) NOT NULL,
      [time_zone] [nvarchar](8) NOT NULL,
      [isp] [nvarchar](256) NOT NULL,
      [domain] [nvarchar](128) NOT NULL,
      [net_speed] [nvarchar](8) NOT NULL,
      [idd_code] [nvarchar](5) NOT NULL,
      [area_code] [nvarchar](30) NOT NULL,
      [weather_station_code] [nvarchar](10) NOT NULL,
      [weather_station_name] [nvarchar](128) NOT NULL,
      [mcc] [nvarchar](256) NOT NULL,
      [mnc] [nvarchar](256) NOT NULL,
      [mobile_brand] [nvarchar](128) NOT NULL,
      [elevation] [real] NOT NULL,
      [usage_type] [nvarchar](11) NOT NULL,
      [address_type] nvarchar(1) NOT NULL,
      [category] nvarchar(10) NOT NULL,
      [district] nvarchar(128) NOT NULL,
      [asn] nvarchar(10) NOT NULL,
      [as] nvarchar(256) NOT NULL
) ON [PRIMARY]

Importing the CSV data into MSSQL

Before import, you will need to create an FMT file to control how the import will load the data into the table.

Copy and paste the code below into a text file called DB26.FMT then save it to the same folder that you have saved the CSV file.

10.0
28
1 SQLCHAR 0 1 "\"" 0 first_double_quote  Latin1_General_CI_AI
2 SQLCHAR 0 39 "\",\"" 1 ip_from ""
3 SQLCHAR 0 39 "\",\"" 2 ip_to ""
4 SQLCHAR 0 2 "\",\"" 3 country_code Latin1_General_CI_AI
5 SQLCHAR 0 64 "\",\"" 4 country_name Latin1_General_CI_AI
6 SQLCHAR 0 128 "\",\"" 5 region_name Latin1_General_CI_AI
7 SQLCHAR 0 128 "\",\"" 6 city_name Latin1_General_CI_AI
8 SQLCHAR 0 20 "\",\"" 7 latitude ""
9 SQLCHAR 0 20 "\",\"" 8 longitude ""
10 SQLCHAR 0 30 "\",\"" 9 zip_code Latin1_General_CI_AI
11 SQLCHAR 0 8 "\",\"" 10 time_zone Latin1_General_CI_AI
12 SQLCHAR 0 256 "\",\"" 11 isp Latin1_General_CI_AI
13 SQLCHAR 0 128 "\",\"" 12 domain Latin1_General_CI_AI
14 SQLCHAR 0 8 "\",\"" 13 net_speed Latin1_General_CI_AI
15 SQLCHAR 0 5 "\",\"" 14 idd_code Latin1_General_CI_AI
16 SQLCHAR 0 30 "\",\"" 15 area_code Latin1_General_CI_AI
17 SQLCHAR 0 10 "\",\"" 16 weather_station_code Latin1_General_CI_AI
18 SQLCHAR 0 128 "\",\"" 17 weather_station_name Latin1_General_CI_AI
19 SQLCHAR 0 256 "\",\"" 18 mcc Latin1_General_CI_AI
20 SQLCHAR 0 256 "\",\"" 19 mnc Latin1_General_CI_AI
21 SQLCHAR 0 128 "\",\"" 20 mobile_brand Latin1_General_CI_AI
22 SQLCHAR 0 20 "\",\"" 21 elevation Latin1_General_CI_AI
23 SQLCHAR 0 11 "\",\"" 22 usage_type Latin1_General_CI_AI
24 SQLCHAR 0 1 "\",\"" 23 address_type Latin1_General_CI_AI
25 SQLCHAR 0 10 "\",\"" 24 category Latin1_General_CI_AI
26 SQLCHAR 0 128 "\",\"" 25 district Latin1_General_CI_AI
27 SQLCHAR 0 10 "\",\"" 26 asn Latin1_General_CI_AI
28 SQLCHAR 0 256 "\"\r\n" 27 as Latin1_General_CI_AI

Run the below SQL command to import the IP2Location data into the table.

BULK INSERT [ip2location].[dbo].[ip2location_db26]
FROM 'C:\IPV6-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED-AREACODE-WEATHER-MOBILE-ELEVATION-USAGETYPE-ADDRESSTYPE-CATEGORY-DISTRICT-ASN.CSV'
WITH
(
    FORMATFILE = 'C:\DB26.FMT'
)

Padding the IP From and IP To fields to enable sorting as string

Due to the fact that SQL Server 2008 R2 does not support a number with more than 38 digits, we have to store the IP From and IP To fields as zero-padded strings to enable sorting.

Run the below SQL command to pad the 2 fields in the table.

update ip2location_db26 set
ip_from = (SUBSTRING(REPLICATE('0', 39), 1, 39 - LEN(ip_from)) + ip_from),
ip_to = (SUBSTRING(REPLICATE('0', 39), 1, 39 - LEN(ip_to)) + ip_to);

Create index to speed up queries

Run the below SQL command to create a clustered index on the IP To field.

CREATE UNIQUE CLUSTERED INDEX [ip_to_index] ON [dbo].[ip2location_db26]
(
    [ip_to] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

 

Querying the IP2Location data from VB.NET winform

In Visual Studio 2010 (or later), create a blank new windows form project which will create a Form1.vb file.

Paste the following VB.NET code into the Form1.vb file and run the code:

Imports System.Data.SqlClient
Imports System.Numerics
Imports System.Net
Imports System.Text
 
Public Class Form1
 
    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim ip As String = "2404:6800:4001:c01::67"
 
        IP2Location(ip)
    End Sub
 
    Private Sub IP2Location(ByVal myip As String)
        Dim address As IPAddress = Nothing
 
        If IPAddress.TryParse(myip, address) Then
            Dim addrBytes() As Byte = address.GetAddressBytes()
            LittleEndian(addrBytes)
 
            Dim ipno As BigInteger = BigInteger.Zero
 
            If addrBytes.Length > 8 Then
                ipno = BitConverter.ToUInt64(addrBytes, 8)
                ipno <<= 64
                ipno += BitConverter.ToUInt64(addrBytes, 0)
 
                Dim sql As String = "SELECT TOP 1 * FROM ip2location_db26 WHERE ip_to >= '" + ipno.ToString().PadLeft(39, "0") + "'"
                Dim conn = New SqlConnection("Server=yourserver;Database=yourdatabase;User Id=youruserid;Password=yourpassword;")
                Dim comm = New SqlCommand(sql, conn)
 
                Dim reader As SqlDataReader
                comm.Connection.Open()
 
                reader = comm.ExecuteReader(CommandBehavior.CloseConnection)
                Dim x As Integer = 0
                Dim sb = New StringBuilder(250)
 
                If reader.HasRows Then
                    If reader.Read() Then
                        For x = 0 To reader.FieldCount() - 1
                            sb.Append(reader.GetName(x) & ": " & reader.GetValue(x) & vbNewLine)
                        Next
                    End If
                End If
                reader.Close()
 
                MsgBox(sb.ToString())
            End If
        End If
    End Sub
 
    Private Sub LittleEndian(ByRef byteArr() As Byte)
        If BitConverter.IsLittleEndian Then
            Dim byteList As New List(Of Byte)(byteArr)
            byteList.Reverse()
            byteArr = byteList.ToArray()
        End If
    End Sub
 
End Class