Reply To: Address validation in NAV/BC
-
::
I think the USPS has a validation API portal: Web Tools APIs | USPS
Also internally using SQL and Management Studio or Power BI Reports with dataset similar to the following you can do some of this validation and evaluate some of the efforts.
————————————————-
/****** Script for Contact: estimate effort for data validation for E-mail Addresses ******/
SELECT COUNT(*) AS [TotalContacts], COUNT(CASE WHEN [E-Mail] IS NULL THEN 1 END) AS [NullEmailCount], COUNT(CASE WHEN [E-Mail] NOT LIKE ‘%@%._%’ THEN 1 END) AS [InvalidEmailCount], COUNT(CASE WHEN [E-Mail] IS NOT NULL AND [E-Mail] LIKE ‘%@%._%’ THEN 1 END) AS [ValidEmailCount], COUNT(CASE WHEN [E-Mail 2] IS NULL THEN 1 END) AS [NullEmail2Count],
COUNT(CASE WHEN [E-Mail 2] NOT LIKE ‘%@%._%’ THEN 1 END) AS [InvalidEmail2Count],
COUNT(CASE WHEN [E-Mail 2] IS NOT NULL AND [E-Mail 2] LIKE ‘%@%._%’ THEN 1 END) AS [ValidEmail2Count]
FROM [NAV16].[dbo].[nvdbcompany$Contact]
WHERE ([E-Mail] IS NOT NULL OR [E-Mail 2] IS NOT NULL)
AND ([E-Mail] NOT LIKE ‘%@%._%’ OR [E-Mail 2] NOT LIKE ‘%@%._%’)
————————————————-
/****** Script for Contact: estimate effort for data validation other than e-mail addresses ******/
SELECT COUNT(*) AS [TotalContacts], COUNT(CASE WHEN [Address] = ” THEN 1 END) AS [BlankAddressCount], COUNT(CASE WHEN [City] = ” THEN 1 END) AS [BlankCityCount],
COUNT(CASE WHEN [Country_Region Code] = ” THEN 1 END) AS [BlankCountryCodeCount],
COUNT(CASE WHEN [Post Code] = ” THEN 1 END) AS [BlankPostalCodeCount],
COUNT(CASE WHEN [Address] IS NULL THEN 1 END) AS [NullAddressCount],
COUNT(CASE WHEN [City] IS NULL THEN 1 END) AS [NullCityCount],
COUNT(CASE WHEN [Country_Region Code] IS NULL THEN 1 END) AS [NullCountryCodeCount],
COUNT(CASE WHEN [Post Code] IS NULL THEN 1 END) AS [NullPostalCodeCount]
FROM [NAV16].[dbo].[nvdbcompany$Contact]
————————————————-
/****** Script for Contact: names, addresses, and phone/mobile numbers ******/
SELECT [Name], [Address], [Phone No_], [Mobile Phone No_], COUNT(*) AS [DuplicateCount]
FROM [NAV16].[dbo].[nvdbcompany$Contact]
WHERE [Name] IS NOT NULL
AND [Address] IS NOT NULL
AND ([Phone No_] IS NOT NULL OR [Mobile Phone No_] IS NOT NULL)
GROUP BY [Name], [Address], [Phone No_], [Mobile Phone No_]
HAVING COUNT(*) > 1
ORDER BY [DuplicateCount] DESC
————————————————-
usps.com
USPS Web Tools APIs let ecommerce website and shipping software developers access USPS data and features free of charge. Check shipping rates, verify addresses, track packages, schedule package pickups, and more through USPS Web Tools.