IP Geocoding via CSV

[jamzar]

Hi,

I am using a function to that goes out to ipinfodb.com and works however it is very slow due (>10 hours) to the mass number of distinict IP addresses that we have in the database.

I have downloaded a CSV file that contains the information that the API returns hoping that it will be quicker however I have having troubles.

Table 1: (Small Snippet)

IPAddress

2.2.2.2

168.217.146.56

8.8.7.5

203.68.56.2

102.14.56.90

Table 2: (Small Snippet)

Start IP               End IP                Country

1.0.0.0                1.0.0.255            Australia

1.0.1.0                1.0.3.255            China

2.0.0.0                2.15.255.255      France

8.7.245.0            8.14.255.255      United States

There is no relationship between Table 1 and Table 2.

What I would like to happen is to create a new column in Table 1 called "IPCountry" based on:

If 'Table 1'[IPAddress] >= 'Table 2'[Start IP] && 'Table 1'[IPAddress] <= 'Table 2'[End IP] then 'Table 2'[Country]

I'm at a bit of a loss on how to do this.

Any suggestions?

Regards

Stewy

[TomMartens · Accepted solution]

Hey,

I guess this blog post I did, is exactly what you're are looking for:

https://www.minceddata.info/2018/07/27/using-power-query-to-conver-an-ip4-address-into-a-numeric-val...

Regards,

Tom

[jamzar]

Morning Tom,

After reading your blog, I have been able to fill in the gaps that I was missing and was able to get this working.

Thank you so much.

Regards

Stewy