Clean a CSV export from your CRM

Addresses in a CRM decay: customers move, streets are renamed, and half of them were typed in a hurry. Before a campaign or a migration you want to know which rows are good, which can be fixed automatically and which a person has to look at.

There are two routes. POST /api/v1/bulk/cleanse takes the whole file and runs datavask on every row on the server. A loop over GET /datavask/adresser in your own code gives you full control over each call. Both give the categories A, B and C. The section Which one should you choose? compares them.

The bulk job#

File requirements#

• The file name must end in .csv, otherwise the API answers 400 with the code csv_required.
• At most 50 MB. A larger file gives 413 with the code file_too_large.
• Semicolon, comma or tab as the separator. The job detects the separator and uses the same one in the result file. A BOM character from Excel is removed, and old Excel files in Windows-1252 are read too.
• The first line holds the column names, case-insensitive. The defaults are vejnavn, husnr, etage, dør (or doer), postnr and postnrnavn. If you have the whole address in one column, call it betegnelse. If your columns are called something else, send the names in the form fields vejnavn_col, husnr_col, etage_col, doer_col, postnr_col, postnrnavn_col and betegnelse_col.

Upload the file#

curl -s "https://api.danadresse.dk/api/v1/bulk/cleanse" \
  -H "X-Api-Key: $DANADRESSE_API_KEY" \
  -F "file=@kunder.csv" \
  -F "vejnavn_col=Gade" \
  -F "husnr_col=Nr" \
  -F "postnr_col=Postnummer"

The response comes back at once, before any rows are processed:

{
  "job_id": "3f9c2a7e5b0d4c1e9a8f6b2d7c4e1a05",
  "status": "queued",
  "status_url": "/api/v1/bulk/jobs/3f9c2a7e5b0d4c1e9a8f6b2d7c4e1a05",
  "download_url": "/api/v1/bulk/jobs/3f9c2a7e5b0d4c1e9a8f6b2d7c4e1a05/download"
}

Follow the job and fetch the result#

GET /api/v1/bulk/jobs/{job_id} returns status (queued, processing, completed or failed), processed and error. processed is updated every 100 rows. total is the number of rows in the file and is known as soon as the job starts, so you can show real progress. GET /api/v1/bulk/jobs lists your recent jobs.

Fetch the file from download_url once the status is completed. If you ask too early you get 425 with the code not_ready. The job is kept for one hour after it finishes. After that, status and download answer 404 with job_not_found, so download the file straight away.

import os
import time
import requests

API = "https://api.danadresse.dk"
H = {"X-Api-Key": os.environ["DANADRESSE_API_KEY"]}

with open("kunder.csv", "rb") as f:
    r = requests.post(
        f"{API}/api/v1/bulk/cleanse",
        headers=H,
        files={"file": ("kunder.csv", f, "text/csv")},
        data={"vejnavn_col": "vejnavn", "husnr_col": "husnr", "postnr_col": "postnr"},
        timeout=120,
    )
r.raise_for_status()
job = r.json()

while True:
    s = requests.get(API + job["status_url"], headers=H, timeout=10).json()
    print(s["status"], s["processed"])
    if s["status"] == "completed":
        break
    if s["status"] == "failed":
        raise SystemExit(s["error"])
    time.sleep(5)

out = requests.get(API + job["download_url"], headers=H, timeout=120)
out.raise_for_status()
with open("kunder_renset.csv", "wb") as f:
    f.write(out.content)

What is in the result?#

The file has your own columns plus seven new ones:

The bulk job runs the same datavask as GET /datavask/adresser on every row, so the categories mean the same: A is exact, B is one safe match with small spelling differences, and C is uncertain. Floor and door count. For C the best guess is in the _match_* columns, but let a person decide the row.

Your own loop over datavask#

If you want to control each call yourself, for example to show every C candidate to a user, call GET /datavask/adresser per row. Datavask takes one address per call.

Watch the per-minute limit. Every response carries X-RateLimit-Minute-Remaining. When it reaches 0, wait until X-RateLimit-Minute-Reset. If you still get 429, wait the number of seconds that Retry-After gives. See Limits, quotas and 429.

The example reads three rows from a string, so you can run it as it is. Replace io.StringIO(DATA) with open("kunder.csv", encoding="utf-8-sig") for a real file:

import csv
import io
import os
import sys
import time
import requests

DATA = """kundenr,vejnavn,husnr,etage,dor,postnr
1001,Strandvejen,100,1,,2900
1002,Strandvej,100,,,2900
1003,Rådhuspladsen,1,,,1550
"""

s = requests.Session()
s.headers["X-Api-Key"] = os.environ["DANADRESSE_API_KEY"]

def vask(row):
    params = {k: row[k] for k in ("vejnavn", "husnr", "etage", "postnr") if row[k]}
    if row["dor"]:
        params["dør"] = row["dor"]
    while True:
        r = s.get("https://api.danadresse.dk/datavask/adresser", params=params, timeout=10)
        if r.status_code == 429:
            time.sleep(int(r.headers.get("Retry-After", "5")))
            continue
        r.raise_for_status()
        if r.headers.get("X-RateLimit-Minute-Remaining") == "0":
            reset = int(r.headers.get("X-RateLimit-Minute-Reset", "0"))
            time.sleep(max(1, reset - int(time.time())))
        return r.json()

reader = csv.DictReader(io.StringIO(DATA))
cols = reader.fieldnames + ["_kategori", "_adresse_id", "_adgangsadresse_id", "_vejnavn"]
out = csv.DictWriter(sys.stdout, fieldnames=cols)
out.writeheader()
for row in reader:
    svar = vask(row)
    top = svar["resultater"][0]["adresse"] if svar["resultater"] else {}
    row.update({
        "_kategori": svar["kategori"],
        "_adresse_id": top.get("id", ""),
        "_adgangsadresse_id": top.get("adgangsadresseid", ""),
        "_vejnavn": top.get("vejnavn", ""),
    })
    out.writerow(row)

Use the rules from Validate the address at checkout: A and B you can store with the official spelling, C a person has to look at. With C, resultater holds several candidates, and the first is only the best guess.

Which one should you choose?#

A good workflow is to run the bulk job on the whole file, then send the C rows through the datavask loop so a person can choose between the candidates.

Good habits#

• Never overwrite source data directly. Write the result to new columns and let a person approve the C rows.
• Store the address id and the access-address id in the CRM. Next time you can validate by id instead of text. See Store the address id.
• Use a live key for large runs. Test keys have the free plan's limits per IP address.

Next steps#

• Add coordinates to a CSV file
• Limits, quotas and 429