Address Validation?

[Mark_Verschell]

(This might end up in Features request, depending on what I hear back).

Wondering if there is a way to do mailing address validation. I am looking for something like Smarty Streets or apparently there is an add-on to Excel (Windows only) from Pitney Bowes that will at least check to see if the address is possible (i.e., it will tell you that there is a 100 Block of Main Street, but not that 123 Main Street actually exists, as I understand it).

I am developing a Base with Form entry, where people can enter addresses. In the past (not on Airtable), I have had to export a special CSV file with addresses, submit it to Smarty Streets, reimport it into the sheet. I would love to have addresses validated as they come in.

Thanks!

[W_Vann_Hall]

It appears one could build a Zapier-driven interface between Airtable and the USPS’s Address Standardization API. I haven’t compared prices — Zapier/USPS vs Smarty Sheets — while the post office’s service is free of charge, you’d need a paid Zapier plan to use the three-stage Zap required (trigger on new Airtable record; pass address to USPS and receive response; update Airtable record with standardized data). You’d truly have to run it in triggered mode, as access to the API is intended to be used on an as-needed basis for real-time cleaning of a user-submitted address (as opposed to batch processing or validation of a mailing database).

If you’re interested, but not that interested, ping me about this in, oh, 4 to 6 weeks, as I may try to assemble this myself.[1] If so, I report back on what I’ve found.
. __________

1. As it turns out, I have this weird fascination with address management systems, no doubt dating back to my days as a publisher. Seems that, back around the turn of the century, I bought a newspaper — which, while not quite the stupidest thing I’ve ever done, certainly ranks in the Top 5. When I learned the previous owners had farmed out management of our astoundingly small weekly mailings for only slightly less than it would have cost to send everything by courier, I brought it back in-house — and spent the next six months agonizing over CASS certification, where to buy card stock of the correct shade of pink, and why I never seemed to be able to remember how much credit was still in the account.

[Mark_Verschell]

That’s fascinating. Hmm, there would be my serious introduction to AirTable. The 5 address restriction on the USPS API isn’t a problem, but I will have to check more clearly what info they return. One nice thing about SmartyStreets is they return the Lat/Lon which I use. It seems at first glance that would be an extra step in the process.

Thanks, I may just ping you back in a month or so.

[Tiago_Goncalves]

I can imagine you dont want to go through all the manual work of doing address validation with static data exports.
TomTom’s Search API provides the possibility to access TomTom Maps and Address Points database , including POIs as a service.

Find out more about it in this Address Validation blog post

[Rad_Garage]

Has anyone figured out a good solution for this? I would love to have address verification for my contact list.

[cgluvin]

I’d like to know when this solution is in place as well. Seems like it wouldn’t take much work as Airtable could leverage the free USPS APIs (Web Tools APIs | USPS) to perform real-time address validation for an address field. Kind of crazy this isn’t already in place given the importance of data integrity to take advantage of the power of this tool (e.g. the Maps app). Please get this done Airtable! :thinking:

[WilliamPorter]

I also would like to have this feature in Airtable and ASAP. Seems very much the kind of thing that there should be an add-on (“app”) for. Tadabase has address validation/verification built into its system and it’s a very very nice and useful feature. Should be available in Airtable too.

[Zach_Young]

@W_Vann_Hall, @cgluvin, and for anyone else…

I think it’s important to start off distinguishing between validating or just parsing / normalizing your address data:

• For concerns about how deliverable something is when USPS is the carrier, then validation is the closest you’ll get to 100% confidence that you’ve got the address right, mostly right, or just flat wrong.

• For concerns about how uniform your data looks, starting with “1600 Penn Avenue 20500” and getting back something like {"street_number": "1600", "street_name": "Pennsylvania Avenue", "post_directional": "NW", ...} then a mapping API that provides geo-coding is probably good enough. You’ll get coordinates too, and they may provide you with some confidence that address exists on that block.

  TomTom looks great, Freemium level and no commercial/automation restrictions, Can I use all APIs to build commercial applications?^5… Yes!. Unlike Google’s Geocode API, which is more restrictive^4 in general, and intended … to use geocoding data within [Google] maps^2.

With the USPS itself

I’ve looked at the USPS APIs in the past, and they’re only free if you’re using them as part of a financial transaction with the USPS itself… so not free as in “free beer”.

Here’s what the API documentation states:

1.3 Important Notice: Address Information API

The Address Validation APIs can be used in conjunction with USPS SHIPPING OR MAILING SERVICES ONLY. The Address API must only be used on an individual transactional basis, i.e. not batch processing or cleansing of a database, but as a customer enters the information into a form on a website. Failure to comply with these terms and conditions can result in termination of USPS API access without prior notice.

[Zach_Young]

@Mark_Verschell, @W_Vann_Hall

Here’s a Gist for a script I’ve developed, Validate an Address in Airtable, with SmartyStreets:

[ScottWorld]

For address validation, you can use any address validation tool on the web that has an API to tap into. Once you have access to that API, you can simply write a script, use DataFetcher.com, or use Make’s HTTP module to tap into that API. 

However, all of those methods require writing your own programming code.

For an easy, no-code way of doing this that doesn’t require writing any code at all, you can use a tool that already has native integration with Make.

For me personally, I use Make’s Byteplant Address Validator, which can easily be integrated & automated with Make’s Airtable automations & integrations.

If you’ve never used Make before, I’ve assembled some Make training resources in this thread, and I compare Make Vs. Zapier in this thread.

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld