To extract city and country from an address list, import a CSV or Excel file with a Location column, run Datablist's Location Lookup, and map the returned City, Country, Country Code, Latitude, and Longitude to new properties. Start with a small sample and check ambiguous place names before using the coordinates.

For example, Location = Paris, France can produce City = Paris, Country = France, and Country Code = fr. Coordinates are returned when the lookup finds them. A bare Paris is less specific, so add a country when you know it.

Extracting City and Country From a Location String

A location string such as Leiston, Suffolk, UK contains place names with different roles: a town, a county, and a country. Splitting on the first comma works only when every row follows that same pattern. It breaks on a full street address, a multiword city, or a value containing only a country.

Map the complete source string to Location so the lookup can try to resolve it. If your input is just Leiston, add the county and country only when you have that information from the source. Keep the original and supplemented text in separate properties. Do not add a guessed country to force a match.

After the sample, compare City and Country with the original text. A country-only result can still be useful even when the city and coordinates are blank. If the returned place is wrong, correct the source context and test that row again before processing similar values.

In this step-by-step guide, you will learn:

Get structured city and country data from unstructured location texts

Step 1: Import a CSV or Excel file with your list of addresses

Prepare a CSV or Excel file with one location per row, such as Paris, France, Leiston, Suffolk, UK, or a full street address. Keep the original text so you can check the lookup result.

To begin, you need to create an account on Datablist.

Once you are registered, create a collection and load your CSV file with your addresses.

Create a collection and choose Import CSV/Excel to load your file.

Create a collection
Create a collection

Check our documentation for importing data if needed.

Step 2: Configure the "Location Lookup" enrichment

After the import, you should have a collection with at least one property containing unstructured data location data.

Your location data can contain:

  • Single Country
  • Single City
  • Combinaison of City and Country
  • Full address

Click Enrich to open the catalog.

Open Enrichments Drawer
Open Enrichments Drawer

Then, select the "Location Lookup" enrichment.

Select Location Lookup
Select Location Lookup

Enrichments work with "Inputs" and "Outputs".

"Inputs" are the data you give to the enrichment, and the "Outputs" are the data that the enrichment returns to you.

Map your Location property to the Location input. Check any suggested mapping rather than assuming it selected the right column.

Map Inputs and Outputs
Map Inputs and Outputs

Then, to configure your "Outputs":

  • Click on the "+" button to automatically create a property and auto-map with the output
  • Or select an existing property from your collection

The "Location Lookup" enrichment returns the following outputs:

  • City - The city name found for your location
  • Country - The country name found in your location text or the country extrapolated from the city
  • Country Code - The two-letter ISO 3166-1 alpha-2 code when available.
  • Latitude - The latitude coordinate when available (for city or full address)
  • Longitude - The longitude coordinate when available (for city or full address)
  • Processed GeoFinder - A checkbox indicating the lookup was attempted, including rows without a location result.

After mapping the outputs, your collection will look like this:

Mapped Outputs
Mapped Outputs

Step 3: Run the enrichment to extract structured parts from your location text

Datablist will run the enrichment on your "current items", which are in priory order:

  • Your selected items
  • Your filtered items
  • All your collection items otherwise

Run a small sample first. Compare the city, country, and coordinates with the source text, then run the remaining view items if the matches look right. The catalog lists 0.05 credits per processed location; a nonempty lookup can use credits even if it finds no result.

A counter will appear to show you how many items are currently in the current batch, and how many items have already been processed.

Processing Data
Processing Data

When the run finishes, inspect blank and ambiguous rows. Do not treat a guessed city or coordinate as a verified street address.

Results of the Location Lookup Enrichment
Results of the Location Lookup Enrichment

Step 4: Export your data as CSV or Excel file if needed

Keep Location, City, Country, Country Code, Latitude, and Longitude together while checking the sample. Before using coordinates in another tool:

  • Confirm that the returned country matches your source and that the city is the place you intended. A city-level match is not proof of a street-level position.
  • Keep latitude and longitude in separate numeric columns. Check the destination tool's required order: swapping them can plot the wrong place.
  • Leave missing coordinates blank. Replacing a blank with 0 would turn an unknown result into a different location. Negative coordinates can be valid.
  • Treat Processed GeoFinder as an attempt flag, not an address-validation or accuracy score.

Click Export and choose Export all items, or Export filtered items for a filtered review segment. Choose CSV or Microsoft Excel in the dialog. If you export only visible properties, make the source and required result columns visible first. Reopen the file and check a few rows before importing it into a map or CRM.

Export Data as CSV or Excel files
Export Data as CSV or Excel files

Use Cases for the Location Lookup Enrichment

Extract City and Country from LinkedIn location data

Location Lookup can structure location strings from a prospect list, including location text copied from public LinkedIn profiles. Keep the source text to review places that share a name.

Export your LinkedIn profiles to a CSV file, then import it into Datablist to process your lead locations.

Normalize LinkedIn Location
Normalize LinkedIn Location

Filter a list of addresses with their latitude and longitude coordinates

For local marketing, you might want to filter a list of addresses to exclude the addresses that are far from a GPS point. For that, you will need to extract the latitude and longitude coordinates for your addresses.

Then, click on the "Filters" button to open the filtering modal.

Add filter to your address list
Add filter to your address list

To make an approximate geographic box, add lower and upper bounds for both Latitude and Longitude. One degree of latitude is roughly 111 km; the distance represented by one degree of longitude changes with latitude. A rectangular filter is not a radius search, so verify boundary cases before using the segment.

Filter Latitude or Longitude coordinates
Filter Latitude or Longitude coordinates

FAQ

How many location lookups can I do with Datablist?

The current catalog lists 0.05 credits per processed location. A city-and-country value may match the local lookup; an ambiguous or full address may use external geocoding. Nonempty lookups can consume credits even when no location is found. Check your plan, credits, and sample output before a large run.

How long does it take to process my location lists?

Time varies with input quality, list size, and whether the lookup needs an external geocoder. Run a sample to estimate completion time for your addresses.

What if my output properties already contain data?

Check the Existing data rule in the run settings. The current default for an existing-data conflict is to overwrite mapped outputs. Choose Skip items with existing values or Update only empty cells if you need to preserve them. Preview the sample before running the rest.

Overwrite Data
Overwrite Data