How to import IP2Proxy CSV data into MSSQL 2017 (IPv4)

Intro

This guide aims to demonstrate how to load the IP2Proxy PX10 (IPv4) CSV file into a Microsoft SQL Server 2017 table.

If you’re using an earlier version of the SQL Server, you will need to refer to our FAQ page at https://www.ip2location.com/faqs/px10-ip-proxytype-country-region-city-isp-domain-usagetype-asn-lastseen-threat-residential#database for technical information about creating the table and importing the data.

 

Downloading the PX10 database for IPv4

You can subscribe for the PX10 database at https://www.ip2location.com/database/ip2proxy if you don’t have the subscription yet. If you’re already subscribed, just login to https://www.ip2location.com/log-in and download the PX10 CSV file (IPv4).

Once you have downloaded the zipped file containing the CSV, extract the CSV and store it somewhere on your computer.

 

Creating the database and table

Run the following SQL commands to create the database called ip2proxy and a table called ip2proxy_px10.

CREATE DATABASE ip2proxy

GO

USE ip2proxy

GO

CREATE TABLE [ip2proxy].[dbo].[ip2proxy_px10](

   [ip_from] bigint NOT NULL,

   [ip_to] bigint NOT NULL,

   [proxy_type] nvarchar(3) 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,

   [isp] nvarchar(256) NOT NULL,

   [domain] nvarchar(128) NOT NULL,

   [usage_type] nvarchar(11) NOT NULL,

   [asn] nvarchar(6) NOT NULL,

   [as] nvarchar(256) NOT NULL,

   [last_seen] int NOT NULL,

   [threat] nvarchar(128) NOT NULL

) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [ip_to] ON [ip2proxy].[dbo].[ip2proxy_px10]([ip_from], [ip_to]) ON [PRIMARY]

GO

 

 

Importing the data

Run the following SQL commands to import data from the CSV file into the table. Modify the below to reflect the path where you saved the extracted CSV file earlier.

BULK INSERT ip2proxy_px10

FROM 'C:\your_folder_name\IP2PROXY-IP-PROXYTYPE-COUNTRY-REGION-CITY-ISP-DOMAIN-USAGETYPE-ASN-LASTSEEN-THREAT-RESIDENTIAL.CSV'

WITH

(

FORMAT = 'CSV',

FIELDQUOTE = '"',

FIELDTERMINATOR = ',',

ROWTERMINATOR = '0x0A',

TABLOCK

)

GO

 

 

That’s all. Now you can query the table with an IP number.

 

Was this article helpful?

Related Articles