Frequently Asked Questions
General
The database format is known as Comma Separated Values (CSV). All fields are separated by a comma and each individual line is a record by itself.
IP2Location is also available in binary format which works together with the IP2Location API in several programming languages.
2019 | 2020 | 2021 | 2022 |
1st January, 2019 | 1st January, 2020 | 1st January, 2021 | 1st January, 2022 |
1st February, 2019 | 1st February, 2020 | 1st February, 2021 | 1st February, 2022 |
1st March, 2019 | 1st March, 2020 | 1st March, 2021 | 1st March, 2022 |
1st April, 2019 | 1st April, 2020 | 1st April, 2021 | 1st April, 2022 |
1st May, 2019 | 1st May, 2020 | 1st May, 2021 | 1st May, 2022 |
1st June, 2019 | 1st June, 2020 | 1st June, 2021 | 1st June, 2022 |
1st July, 2019 | 1st July, 2020 | 1st July, 2021 | 1st July, 2022 |
1st August, 2019 | 1st August, 2020 | 1st August, 2021 | 1st August, 2022 |
1st September, 2019 | 1st September, 2020 | 1st September, 2021 | 1st September, 2022 |
1st October, 2019 | 1st October, 2020 | 1st October, 2021 | 1st October, 2022 |
1st November, 2019 | 1st November, 2020 | 1st November, 2021 | 1st November, 2022 |
1st December, 2019 | 1st December, 2020 | 1st December, 2021 | 1st December, 2022 |
NOTE: If your current subscription has less than 6 months left, it will be renewed first before the upgrade is performed. In other words, your subscription period will be extended for another year.
Technical
IP address (IPv4) is divided into 4 sub-blocks. Each sub-block has a different weight number each powered by 256. IP number is being used in the database because it is more efficient to search between a range of numbers in a database.
The Beginning IP number and Ending IP Number are calculated based on the following formula:
IP Number = 16777216*w + 65536*x + 256*y + z (1) where IP Address = w.x.y.z For example, if the IP address is "202.186.13.4", then its IP Number will be "3401190660", based on the formula (1). IP Address = 202.186.13.4 So, w = 202, x = 186, y = 13 and z = 4 IP Number = 16777216*202 + 65536*186 + 256*13 + 4 = 3388997632 + 12189696 + 3328 + 4 = 3401190660 To reverse IP number to IP address, w = int ( IP Number / 16777216 ) % 256 x = int ( IP Number / 65536 ) % 256 y = int ( IP Number / 256 ) % 256 z = int ( IP Number ) % 256 where % is the modulus operator and int returns the integer part of the division.
Function Dot2LongIP (ByVal DottedIP) Dim i, pos Dim PrevPos, num If DottedIP = "" Then Dot2LongIP = 0 Else For i = 1 To 4 pos = InStr(PrevPos + 1, DottedIP, ".", 1) If i = 4 Then pos = Len(DottedIP) + 1 End If num = Int(Mid(DottedIP, PrevPos + 1, pos - PrevPos - 1)) PrevPos = pos Dot2LongIP = ((num Mod 256) * (256 ^ (4 - i))) + Dot2LongIP Next End If End Function
function Dot2LongIP ($IPaddr) { if ($IPaddr == "") { return 0; } else { $ips = explode(".", "$IPaddr"); return ($ips[3] + $ips[2] * 256 + $ips[1] * 256 * 256 + $ips[0] * 256 * 256 * 256); } }
long Dot2LongIP(String ipstring) { String[] ipAddressInArray = ipstring.split("\\."); long result = 0; long ip = 0; for (int x = 3; x >= 0; x--) { ip = Long.parseLong(ipAddressInArray[3 - x]); result |= ip << (x << 3); } return result; }
function Dot2LongIP(ipAddress) { if(arguments.ipAddress EQ "") { return 0; } else { ips = ListToArray( arguments.ipAddress, "." ); return( ( 16777216 * ips[1] ) + ( 65536 * ips[2] ) + ( 256 * ips[3] ) + ips[4] ); } }
public double Dot2LongIP(string DottedIP) { int i; string [] arrDec; double num = 0; if (DottedIP == "") { return 0; } else { arrDec = DottedIP.Split("."); for(i = arrDec.Length - 1; i >= 0 ; i --) { num += ((int.Parse(arrDec[i])%256) * Math.Pow(256 ,(3 - i ))); } return num; } }
Public Function Dot2LongIP(ByVal DottedIP As String) As Double Dim arrDec() As String Dim i As Integer Dim intResult As Long If DottedIP = "" then Dot2LongIP = 0 Else arrDec = DottedIP.Split(".") For i = arrDec.Length - 1 To 0 Step -1 intResult = intResult + ((Int(arrDec(i)) Mod 256) * Math.Pow(256, 3 -i)) Next Dot2LongIP = intResult End If End Function
use Socket; sub dot2LongIP { my $ip_address = shift(@_); return unpack("N",inet_aton($ip_address)); }
require 'ipaddr' def dot2LongIP(ip) ipnum = IPAddr.new(ip) return ipnum.to_i end
import ipaddress def dot2LongIP(ip): return int(ipaddress.IPv4Address(ip))
uint32_t Dot2LongIP(char* ipstring) { uint32_t ip = inet_addr(ipstring); uint8_t *ptr = (uint8_t *) &ip; uint32_t a = 0; if (ipstring != NULL) { a = (uint8_t)(ptr[3]); a += (uint8_t)(ptr[2]) * 256; a += (uint8_t)(ptr[1]) * 256 * 256; a += (uint8_t)(ptr[0]) * 256 * 256 * 256; } return a; }
CREATE FUNCTION Dot2LongIP (ip text) RETURNS bigint BEGIN DECLARE ipnum bigint; SET ipnum = (SELECT INET_ATON(ip)); RETURN ipnum; END
Create FUNCTION [dbo].[Dot2LongIP]( @IP VarChar(15) ) RETURNS BigInt AS BEGIN DECLARE @ipA BigInt, @ipB Int, @ipC Int, @ipD Int, @ipI BigInt SELECT @ipA = LEFT(@ip, PATINDEX('%.%', @ip) - 1 ) SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipA) - 1 ) SELECT @ipB = LEFT(@ip, PATINDEX('%.%', @ip) - 1 ) SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipB) - 1 ) SELECT @ipC = LEFT(@ip, PATINDEX('%.%', @ip) - 1 ) SELECT @ip = RIGHT(@ip, LEN(@ip) - LEN(@ipC) - 1 ) SELECT @ipD = @ip RETURN ( @ipA * 256*256*256 ) + ( @ipB * 256*256 ) + ( @ipC * 256 ) + @ipD END RETURN @ipI END
CREATE OR REPLACE FUNCTION Dot2LongIP(text) RETURNS bigint AS ' SELECT split_part($1,''.'',1)::int8*(256*256*256)+ split_part($1,''.'',2)::int8*(256*256)+ split_part($1,''.'',3)::int8*256+ split_part($1,''.'',4)::int8; ' LANGUAGE 'SQL';
Convert IPv4 IP Address to IP Number in Decimal Integer (IPv4 IP Address is in cell A1): =((VALUE(LEFT(A1, FIND(".", A1)-1)))*256^3)+((VALUE(MID(A1, FIND(".", A1)+1, FIND(".", A1, FIND(".", A1)+1)-FIND(".", A1)-1)))*256^2)+((VALUE(MID(A1, FIND(".", A1, FIND(".", A1)+1)+1, FIND(".", A1, FIND(".", A1, FIND(".", A1)+1)+1)-FIND(".", A1, FIND(".", A1)+1)-1)))*256)+(VALUE(RIGHT(A1, LEN(A1)-FIND(".", A1, FIND(".", A1, FIND(".", A1)+1)+1)))) Convert IP Number in Decimal Integer to IPv4 IP Address (Decimal Integer is in cell A2): =IF(A2<>"", CONCATENATE(INT(A2/256^3), ".", INT(MOD(A2, (256^3))/(256^2)), ".", INT(MOD(MOD(A2, 256^3), 256^2)/256), ".", MOD(MOD(MOD(A2, 256^3), 256^2), 256)), "")
function Dot2LongIP($ipv6) { $int = inet_pton($ipv6); $bits = 15; $ipv6long = 0; while($bits >= 0) { $bin = sprintf("%08b", (ord($int[$bits]))); if($ipv6long){ $ipv6long = $bin . $ipv6long; } else{ $ipv6long = $bin; } $bits--; } $ipv6long = gmp_strval(gmp_init($ipv6long, 2), 10); return $ipv6long; }
java.math.BigInteger Dot2LongIP(String ipv6) { java.net.InetAddress ia = java.net.InetAddress.getByName(ipv6); byte byteArr[] = ia.getAddress(); if (ia instanceof java.net.Inet6Address) { java.math.BigInteger ipnumber = new java.math.BigInteger(1, byteArr); return ipnumber; } }
<cffunction name="Dot2LongIP" access="public" returntype="numeric"> <cfargument name="IPAddress" type="string" required="true" /> <cfif arguments.IPAddress EQ ""> <cfreturn 0 /> </cfif> <cfset IPV6Long = CreateObject("java","java.math.BigInteger") /> <cfset IPV6NetAddress = CreateObject("java","java.net.InetAddress") /> <cfset newIp = IPV6NetAddress.getByName(arguments.IPAddress) /> <cfset bytes = newIp.getAddress() /> <cfset bigInt = IPV6Long.init(1, bytes).toString() /> <cfreturn bigInt /> </cffunction>
public System.Numerics.BigInteger Dot2LongIP(string ipv6) { System.Net.IPAddress address; System.Numerics.BigInteger ipnum; if (System.Net.IPAddress.TryParse(ipv6, out address)) { byte[] addrBytes = address.GetAddressBytes(); if (System.BitConverter.IsLittleEndian) { System.Collections.Generic.List<byte> byteList = new System.Collections.Generic.List<byte>(addrBytes); byteList.Reverse(); addrBytes = byteList.ToArray(); } if (addrBytes.Length > 8) { //IPv6 ipnum = System.BitConverter.ToUInt64(addrBytes, 8); ipnum <<= 64; ipnum += System.BitConverter.ToUInt64(addrBytes, 0); } else { //IPv4 ipnum = System.BitConverter.ToUInt32(addrBytes, 0); } return ipnum; } }
Public Function Dot2LongIP(ByVal ipv6 As String) As System.Numerics.BigInteger Dim address As System.Net.IPAddress Dim ipnum As System.Numerics.BigInteger If System.Net.IPAddress.TryParse(ipv6, address) Then Dim addrBytes() As Byte = address.GetAddressBytes() If System.BitConverter.IsLittleEndian Then Dim byteList As New System.Collections.Generic.List(Of Byte)(addrBytes) byteList.Reverse() addrBytes = byteList.ToArray() End If If addrBytes.Length > 8 Then 'IPv6 ipnum = System.BitConverter.ToUInt64(addrBytes, 8) ipnum <<= 64 ipnum += System.BitConverter.ToUInt64(addrBytes, 0) Else 'IPv4 ipnum = System.BitConverter.ToUInt32(addrBytes, 0) End If End If Dot2LongIP = ipnum End Function
use NetAddr::IP; sub dot2LongIP { my $ip_address = shift(@_); my $ip_number = NetAddr::IP->new($ip_address) or die; return $ip_number->bigint; }
require 'ipaddr' def dot2LongIP(ipv6) ipnum = IPAddr.new(ipv6) return ipnum.to_i end
import ipaddress def dot2LongIP(ipv6): return int(ipaddress.IPv6Address(ipv6))
#include <arpa/inet.h> #include <inttypes.h> typedef unsigned __int128 uint128_t; uint128_t Dot2LongIP(const char* ipv6) { struct sockaddr_in6 sa; inet_pton(AF_INET6, ipv6, &(sa.sin6_addr)); uint128_t ipnum = 0; uint128_t octet = 0; int i; for (i = 0; i < (sizeof(sa.sin6_addr.s6_addr) / sizeof(sa.sin6_addr.s6_addr[0])); i++) { octet = ((uint128_t)sa.sin6_addr.s6_addr[i] << ((uint128_t)(15 - i) * 8)); ipnum = ipnum + octet; } return ipnum; }
Firstly, convert the IP address to IP number format. Search using IP number to match a record that has the IP Number between the Beginning IP Number and the Ending IP Number.
For example, IP Address "72.77.138.60" is "1213041212" in IP Number. It matched the following recordset in the database.
ASP without Proxy detection
<% ipaddress = Request.ServerVariables("REMOTE_ADDR") %>
ASP with Proxy detection
<% ipaddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR") if ipaddress = "" then ipaddress = Request.ServerVariables("REMOTE_ADDR") end if %>
PHP without Proxy detection
<?php $ipaddress = getenv("REMOTE_ADDR"); ?>
PHP with Proxy detection
<?php if (getenv("HTTP_X_FORWARDED_FOR")) { $ipaddress = getenv("HTTP_X_FORWARDED_FOR"); } else { $ipaddress = getenv("REMOTE_ADDR"); } ?>
JSP without Proxy detection
<% String ipaddress = request.getRemoteAddr(); %>
JSP with Proxy detection
<% if (request.getHeader("X_FORWARDED_FOR") == null) { String ipaddress = request.getRemoteAddr(); } else { String ipaddress = request.getHeader("X_FORWARDED_FOR"); } %>
ColdFusion without Proxy detection
<CFCOMPONENT> <CFSET ipaddress="#CGI.Remote_Addr#"> </CFCOMPONENT>
ColdFusion with Proxy detection
<CFCOMPONENT> <CFIF #CGI.HTTP_X_Forwarded_For# EQ ""> <CFSET ipaddress="#CGI.Remote_Addr#"> <CFELSE> <CFSET ipaddress="#CGI.HTTP_X_Forwarded_For#"> </CFIF> </CFCOMPONENT>
ASP.NET (C#) without Proxy detection
public string IpAddress() { return Request.ServerVariables["HTTP_X_FORWARDED_FOR"]; }
ASP.NET (C#) with Proxy detection
public string IpAddress() { string strIp; strIp = Request.ServerVariables["HTTP_X_FORWARDED_FOR"]; if (strIp == null) { strIp = Request.ServerVariables["REMOTE_ADDR"]; } return strIp; }
ASP.NET (VB.NET) without Proxy detection
Public Function IpAddress() IpAddress = Request.ServerVariables("HTTP_X_FORWARDED_FOR") End Function
ASP.NET (VB.NET) with Proxy detection
Public Function IpAddress() Dim strIp As String strIp = Request.ServerVariables("HTTP_X_FORWARDED_FOR") If strIp = "" Then strIp = Request.ServerVariables("REMOTE_ADDR") End If IpAddress = strIp End Function
Our subscribers can automate the download process using the free command-line script written in Perl which can be downloaded from our Web site. Please visit the following Web page for more information such as command syntax.
https://www.ip2location.com/free/downloader
Since the database is being updated every month at the beginning of the month, please download the database only once a month.
Weather.com XML Data Feed (Free for Personal Use or Paid for Commercial use)
For example, if the nearest Weather Station Code for one IP address is AAXX0001 in Aruba. The following are some sample sites with custom URL to retrieve the weather information and forecast.
Sample XML Data Feed using Weather.com
http://xoap.weather.com/weather/local/AAXX0001?cc=*&dayf=1&unit=m
Web-based Weather Information from Other Sites
https://weather.com/weather/today/l/AAXX0001
http://www.theweathernetwork.com/index.php?product=weather&placecode=aaxx0001
http://www.intellicast.com/Global/Satellite/Infrared.aspx?location=AAXX0001&lien=8
http://weather.aol.com/main.adp?location=AAXX0001
http://weather.msn.com/tenday.aspx?wealocations=wc:AAXX0001
http://weather.yahoo.com/forecast/AAXX0001.html
Database
First, import this database into your MS-SQL, MS-ACCESS, PL/SQL, MYSQL or other RDMS. Use an SQL query to get the matching recordset.
SELECT `ip_from`, `ip_to`, `country_code`, `country_name`, `region_name`, `city_name`, `latitude`, `longitude`, `zip_code`, `time_zone`, `isp`, `domain`, `net_speed` FROM `ip2location_db14` WHERE INET_ATON([IP ADDRESS]) <= ip_to LIMIT 1
SELECT TOP 1 [ip_from], [ip_to], [country_code], [country_name], [region_name], [city_name], [latitude], [longitude], [zip_code], [time_zone], [isp], [domain], [net_speed] FROM [ip2location_db14] WHERE [SEARCH IP NO] <= ip_to
First, import this database into your MS-SQL, MS-ACCESS, PL/SQL, MYSQL or other RDMS. Use an SQL query to get the matching recordset. The IPv4 is in IPv4-mapped IPv6 address form. Learn more at https://www.ip2location.com/articles/ip2location-ipv4-mapped-ipv6-address.
SELECT `ip_from`, `ip_to`, `country_code`, `country_name`, `region_name`, `city_name`, `latitude`, `longitude`, `zip_code`, `time_zone`, `isp`, `domain`, `net_speed` FROM `ip2location_db14` WHERE [SEARCH IP NO] <= ip_to LIMIT 1
SELECT TOP 1 [ip_from], [ip_to], [country_code], [country_name], [region_name], [city_name], [latitude], [longitude], [zip_code], [time_zone], [isp], [domain], [net_speed] FROM [ip2location_db14] WHERE [SEARCH IP NO] <= ip_to
CREATE DATABASE ip2location; USE ip2location; CREATE TABLE `ip2location_db14`( `ip_from` INT(10) UNSIGNED, `ip_to` INT(10) UNSIGNED, `country_code` CHAR(2), `country_name` VARCHAR(64), `region_name` VARCHAR(128), `city_name` VARCHAR(128), `latitude` DOUBLE, `longitude` DOUBLE, `zip_code` VARCHAR(30), `time_zone` VARCHAR(8), `isp` VARCHAR(256), `domain` VARCHAR(128), `net_speed` VARCHAR(8), PRIMARY KEY (`ip_to`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE DATABASE ip2location GO USE ip2location GO CREATE TABLE [ip2location].[dbo].[ip2location_db14]( [ip_from] bigint NOT NULL, [ip_to] bigint 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, ) ON [PRIMARY] GO CREATE CLUSTERED INDEX [ip_to] ON [ip2location].[dbo].[ip2location_db14]([ip_to]) ON [PRIMARY] GO
CREATE DATABASE ip2location WITH ENCODING 'UTF8'; \c ip2location CREATE TABLE ip2location_db14( ip_from integer(10) NOT NULL, ip_to integer(10) NOT NULL, country_code character(2) NOT NULL, country_name character varying(64) NOT NULL, region_name character varying(128) NOT NULL, city_name character varying(128) NOT NULL, latitude real NOT NULL, longitude real NOT NULL, zip_code character varying(30) NOT NULL, time_zone character varying(8) NOT NULL, isp character varying(256) NOT NULL, domain character varying(128) NOT NULL, net_speed character varying(8) NOT NULL, CONSTRAINT ip2location_db14_pkey PRIMARY KEY (ip_from, ip_to) );
CREATE DATABASE ip2location; USE ip2location; CREATE TABLE `ip2location_db14`( `ip_from` DECIMAL(39,0) UNSIGNED, `ip_to` DECIMAL(39,0) UNSIGNED, `country_code` CHAR(2), `country_name` VARCHAR(64), `region_name` VARCHAR(128), `city_name` VARCHAR(128), `latitude` DOUBLE, `longitude` DOUBLE, `zip_code` VARCHAR(30), `time_zone` VARCHAR(8), `isp` VARCHAR(256), `domain` VARCHAR(128), `net_speed` VARCHAR(8), PRIMARY KEY (`ip_to`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
CREATE DATABASE ip2location GO USE ip2location GO CREATE TABLE [ip2location].[dbo].[ip2location_db14]( [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, ) ON [PRIMARY] GO CREATE CLUSTERED INDEX [ip_to] ON [ip2location].[dbo].[ip2location_db14]([ip_to]) ON [PRIMARY] GO
CREATE DATABASE ip2location WITH ENCODING 'UTF8'; \c ip2location CREATE TABLE ip2location_db14( ip_from decimal(39,0) NOT NULL, ip_to decimal(39,0) NOT NULL, country_code character(2) NOT NULL, country_name character varying(64) NOT NULL, region_name character varying(128) NOT NULL, city_name character varying(128) NOT NULL, latitude real NOT NULL, longitude real NOT NULL, zip_code character varying(30) NOT NULL, time_zone character varying(8) NOT NULL, isp character varying(256) NOT NULL, domain character varying(128) NOT NULL, net_speed character varying(8) NOT NULL, CONSTRAINT ip2location_db14_pkey PRIMARY KEY (ip_from, ip_to) );
LOAD DATA LOCAL INFILE 'IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED.CSV' INTO TABLE `ip2location_db14` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
BULK INSERT [ip2location].[dbo].[ip2location_db14] FROM 'C:\[path to your CSV file]\IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED.CSV' WITH ( FORMATFILE = 'C:\[path to your DB14.FMT file]\DB14.FMT' ) GONOTE: You will need to copy the FMT code below and save it as a file named DB14.FMT on your computer. The first line of the FMT code indicates the version of bcp. Please change the version as according to your MS-SQL installed.
SQL Server 2016 | 12.0 |
SQL Server 2014 | 12.0 |
SQL Server 2012 | 11.0 |
Azure SQL | 10.0 |
SQL Server 2008/2008R2 | 10.0 |
SQL Server 2005 | 9.0 |
SQL Server 2000 | 8.0 |
SQL Server 7.0 | 7.0 |
SQL Server 6.5 | 6.5 |
IPv4 database
10.0 14 1 SQLCHAR 0 1 "\"" 0 first_double_quote Latin1_General_CI_AI 2 SQLCHAR 0 20 "\",\"" 1 ip_from "" 3 SQLCHAR 0 20 "\",\"" 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 "\"\r\n" 13 net_speed Latin1_General_CI_AI
IPv6 database
10.0 14 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 "\"\r\n" 13 net_speed Latin1_General_CI_AI
NOTE: Due to the fact that SQL Server 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. Please visit this tutorial on how to add the padding, enable cluster index and make the query.
COPY ip2location_db14 FROM 'IP-COUNTRY-REGION-CITY-LATITUDE-LONGITUDE-ZIPCODE-TIMEZONE-ISP-DOMAIN-NETSPEED.CSV' WITH CSV QUOTE AS '"';