ZIP Code Auto Fill¶
The ZIP Code Auto Fill Rules snap-in can be populated with United States' and Canadian ZIP code information. This information is then used to auto populate City and State fields in the Address snap-ins. City and State fields will not be populated or changed by the ZIP code if they are already populated or one of the fields combined with the ZIP code does not match a rule. A default ZIP code can be set in the instance that a ZIP code contains several cities or spans multiple states. An extended ZIP (ZIP + 4 numbers) can used for more detail. Note: Users without access to System Administration can view the ZIP Codes System Report to look up ZIP codes by City, State, or ZIP.
Fnding the ZIP Code Auto Fill Rules Snap-In¶
Go to the Admin Landing Page by clicking on the Admin tab in the top bar. Click on General Configuration (in the left hand bar) -> ZIP Code Auto Fill. Tip: Use the ZIP Code, City, and State fields in the search snap-in at the top of the screen to find ZIP code information (once it has been entered). You can use a wildcard (*) character to perform these searches.
Creating ZIP Code Auto Fill Rules¶
- Add a new record.
- Select or type all required and relevant data for each available
field. Some fields may be required while others are not.
- ZIP Code: Required field based off US and Canadian Postal codes.
- City: Type in the city that is associated with the ZIP code. This city will populate an empty City field of an address when the ZIP code is entered.
- State: Select the state that is associated with the ZIP code. This state will be selected from the State drop-down list of an address when the ZIP code is entered.
- Default: Select this box to set the ZIP code as the default in the event that this ZIP code is associated with multiple cities and/or states. Only one ZIP code rule based off a ZIP code can be set to a default at a time. Setting a rule as a default will clear the current default.
- Notes: Type any related notes in this free-text field.
- Save the Session. (Click Save button or
Ctrl+S)
Importing ZIP Code Auto Fill Rules¶
Zip codes can be imported via various delimited value file types through the use of SQL scripting. When importing ZIP codes with one of these file types, it is important to note that all fields in the database table must be represented but only ZIP, Default, and either City or State are required. The other fields can be left blank but still need to be delimited. The Default field can be populated with either a 0 for false or 1 for true. Once imported, the defaults can be easily set by selecting the Default checkbox under Code Tables | Names| ZIP Code Auto Fill Rules. Default can be changed at anytime. In the event ZIP code is broken into separate fields, ZIP and the +4 code need to be combined into one field for import with the "-" between the ZIP code and the +4. Delimited file types can be edited quickly in many spreadsheet applications and then exported to a Comma Separated Values or CSV file. ZIP codes may have "ND" (non-deliverable) in the +4 section. This would need be replaced with "00". Taking the time to format the import values will make importing easier. Please visit the online help to see an example of an import script. A sample import script is provided below along with a sample ZIP code CSV file.
Here is a sample from a purchased ZIP code packages CSV file.
"ZIPCode","Plus4","Range","CityName","StateAbbr"
"00501","0001","0","HOLTSVILLE","NY"
"00501","0002","997","HOLTSVILLE","NY"
"00544","0001","0","HOLTSVILLE","NY"
"00544","0002","997","HOLTSVILLE","NY"
"00601","0001","146","ADJUNTAS","PR"
"00601","0201","15","ADJUNTAS","PR"
"00601","0221","39","ADJUNTAS","PR"
"00601","0381","79","ADJUNTAS","PR"
Here is a sample script based off the format of the above data.
/******************************************************************************************/
DECLARE @Include5Digit bit
DECLARE @Include9Digit bit
DECLARE @IncludeCanada bit
DECLARE @ExpandPlus4Ranges bit
DECLARE @Filter9DigitByState varchar(MAX)
DECLARE @TruncateZipCodeTable bit
DECLARE @USCsvFilePath varchar(255)
DECLARE @CanadaCsvFilePath varchar(255)
/*******************************************************************************************
* Configuration
*******************************************************************************************/
SET @USCsvFilePath = 'C:\9-digit Basic.csv'
SET @CanadaCsvFilePath = 'C:\6-digit Basic.csv'
SET @IncludeCanada = 1
SET @Include5Digit = 1
SET @Include9Digit = 1
SET @ExpandPlus4Ranges = 0
SET @Filter9DigitByState = null --This can be a comma separated list of state abbreviations ('UT,AZ,NV,ID,CO'). Set to null to import all states
SET @TruncateZipCodeTable = 1
/*******************************************************************************************
* Script
********************************************************************************************/
SET NOCOUNT ON
RAISERROR('Preparing for import...', 10,1) WITH NOWAIT
IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE [type] = 'U' and [object_id] = object_id(N'tempdb..#ZipCodeImportOptions'))
DROP TABLE #ZipCodeImportOptions
CREATE TABLE #ZipCodeImportOptions
(
Include5Digit bit,
Include9Digit bit,
IncludeCanada bit,
ExpandPlus4Ranges bit,
Filter9DigitByState VARCHAR(MAX),
TruncateZipCodeTable bit,
USCsvFilePath varchar(255),
CanadaCsvFilePath varchar(255)
)
INSERT INTO #ZipCodeImportOptions VALUES (@Include5Digit, @Include9Digit, @IncludeCanada, @ExpandPlus4Ranges, @Filter9DigitByState, @TruncateZipCodeTable, @USCsvFilePath, @CanadaCsvFilePath)
GO
IF EXISTS (SELECT * FROM sys.objects WHERE [name] = N'Gen4Digits' AND [type] = 'TF')
DROP FUNCTION [dbo].[Gen4Digits]
GO
CREATE FUNCTION [dbo].[Gen4Digits]
(@plus4 VARCHAR(7), @range VARCHAR(6)) RETURNS @t TABLE
(FourDigit int NOT NULL PRIMARY KEY, FourDigitZip VARCHAR(7))
AS
BEGIN
INSERT @t
SELECT CASE WHEN ISNUMERIC(@plus4) = 1 THEN CAST(@plus4 as int) ELSE 0 END, RIGHT('0000' + RTRIM(LTRIM(@plus4)),4)
IF ISNUMERIC(@plus4) = 0 OR ISNUMERIC(@range) = 0 RETURN
DECLARE @StartNumber int
DECLARE @MaxRowNumber int
SET @StartNumber = CAST(@plus4 as int)
SET @MaxRowNumber = @StartNumber + CAST(@range AS int)
WHILE @@ROWCOUNT > 0
INSERT @t
SELECT t.FourDigit + x.MaxRowNum, RIGHT('0000' + CAST((t.FourDigit + x.MaxRowNum) AS VARCHAR(4)),4) FROM @t t
CROSS JOIN (SELECT MAX(FourDigit) - @StartNumber + 1 AS MaxRowNum FROM @t) x
WHERE t.FourDigit <= @MaxRowNumber - x.MaxRowNum
RETURN
END
GO
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[admZipCodeAutoFill]') AND name = N'IX_admZipCodeAutoFill_Zip')
DROP INDEX [IX_admZipCodeAutoFill_Zip] ON [dbo].[admZipCodeAutoFill] WITH ( ONLINE = OFF )
GO
DECLARE @Include5Digit bit
DECLARE @Include9Digit bit
DECLARE @IncludeCanada bit
DECLARE @ExpandPlus4Ranges bit
DECLARE @Filter9DigitByState VARCHAR(MAX)
DECLARE @TruncateZipCodeTable bit
DECLARE @USCsvFilePath VARCHAR(255)
DECLARE @CanadaCsvFilePath varchar(255)
SELECT @Include5Digit = Include5Digit,
@Include9Digit = Include9Digit,
@IncludeCanada = IncludeCanada,
@ExpandPlus4Ranges = ExpandPlus4Ranges,
@Filter9DigitByState = Filter9DigitByState,
@TruncateZipCodeTable = TruncateZipCodeTable,
@USCsvFilePath = USCsvFilePath,
@CanadaCsvFilePath = CanadaCsvFilePath
FROM #ZipCodeImportOptions
IF @TruncateZipCodeTable = 1
BEGIN
RAISERROR('Truncating admZipCodeAutoFill table...', 10,1) WITH NOWAIT
TRUNCATE TABLE admZipCodeAutoFill
END
IF @Include5Digit = 1 OR @Include9Digit = 1
BEGIN
IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE [type] = 'U' and [object_id] = object_id(N'tempdb..#USZipCodeDownloadImport'))
DROP TABLE #USZipCodeDownloadImport
CREATE TABLE #USZipCodeDownloadImport
(
ZIPCode VARCHAR(7),
Plus4 VARCHAR(6),
[Range] VARCHAR(7),
CityName VARCHAR(64),
StateAbbr VARCHAR(4)
)
DECLARE @usBulkImport VARCHAR(MAX) =
'BULK INSERT #USZipCodeDownloadImport
FROM ''' + @USCsvFilePath +
''' WITH
(
FIRSTROW = 2,
FIELDTERMINATOR ='','',
ROWTERMINATOR = ''\n''
)'
RAISERROR('Importing US zip code data...', 10,1) WITH NOWAIT
EXEC (@usBulkImport)
RAISERROR('Scrubbing data...', 10,1) WITH NOWAIT
UPDATE #USZipCodeDownloadImport -- If data comtains double quotes this will remove them.
SET
ZIPCode = REPLACE(ZIPCode, '"', ''),
Plus4 = REPLACE(Plus4, '"', ''),
[Range] = REPLACE([Range], '"', ''),
CityName = REPLACE(CityName, '"', ''),
StateAbbr = REPLACE(StateAbbr, '"', '')
-- Makes sure all state abbreviations exsist. Otherwise the script fails,
-- If this fails update your ctblState table with the correct codes.
INSERT INTO ctblState (Code, [Description])
SELECT DISTINCT StateAbbr, StateAbbr FROM #USZipCodeDownloadImport z
WHERE NOT EXISTS (SELECT 1 FROM ctblState WHERE Code = z.StateAbbr)
IF @Include5Digit = 1
BEGIN
RAISERROR('Inserting 5 digit zip codes into admZipCodeAutoFill...', 10,1) WITH NOWAIT
INSERT INTO admZipCodeAutoFill (Zip, City, [State], [Default])
SELECT DISTINCT ZIPCode, CityName, StateAbbr, 0 FROM #USZipCodeDownloadImport
END
IF @Include9Digit = 1
BEGIN
DECLARE @FilterByState TABLE (StateAbbr CHAR(2))
INSERT INTO @FilterByState
SELECT Items FROM dbo.Split(@Filter9DigitByState, ',')
IF @ExpandPlus4Ranges = 1
BEGIN
RAISERROR('Inserting 9 digit zip codes with plus4 expansion into admZipCodeAutoFill...', 10,1) WITH NOWAIT
INSERT INTO admZipCodeAutoFill (Zip, City, [State], [Default])
SELECT z.ZIPCode + '-' + f.FourDigitZip, z.CityName, z.StateAbbr, 0 FROM #USZipCodeDownloadImport z
CROSS APPLY dbo.Gen4Digits(z.Plus4, z.[Range]) f
WHERE (SELECT COUNT(*) FROM @FilterByState) = 0
OR EXISTS (SELECT 1 FROM @FilterByState WHERE StateAbbr = z.StateAbbr)
END
ELSE
BEGIN
RAISERROR('Inserting 9 digit zip codes without plus4 expansion into admZipCodeAutoFill...', 10,1) WITH NOWAIT
INSERT INTO admZipCodeAutoFill (Zip, City, [State], [Default])
SELECT z.ZIPCode + '-' + z.Plus4, z.CityName, z.StateAbbr, 0 FROM #USZipCodeDownloadImport z
WHERE (SELECT COUNT(*) FROM @FilterByState) = 0
OR EXISTS (SELECT 1 FROM @FilterByState WHERE StateAbbr = z.StateAbbr)
END
END
END
/****************************Canada**********************************************/
IF @IncludeCanada = 1
BEGIN
IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE [type] = 'U' and [object_id] = object_id(N'tempdb..#CanadaZipCodeDownloadImport'))
DROP TABLE #CanadaZipCodeDownloadImport
CREATE TABLE #CanadaZipCodeDownloadImport
(
PostalCode VARCHAR(20),
CityName VARCHAR(255),
ProvinceAbbr VARCHAR(5)
)
DECLARE @canadaBulkImport VARCHAR(MAX) =
'BULK INSERT #CanadaZipCodeDownloadImport
FROM ''' + @CanadaCsvFilePath +
''' WITH
(
FIRSTROW = 2,
FIELDTERMINATOR ='','',
ROWTERMINATOR = ''\n''
)'
RAISERROR('Importing Canada zip code data...', 10,1) WITH NOWAIT
EXEC (@canadaBulkImport)
RAISERROR('Inserting Canada province abbreviations into ctblState...', 10,1) WITH NOWAIT
INSERT INTO ctblState (Code, [Description])
SELECT DISTINCT ProvinceAbbr, ProvinceAbbr FROM #CanadaZipCodeDownloadImport c
WHERE NOT EXISTS (SELECT 1 FROM ctblState WHERE Code = c.ProvinceAbbr)
RAISERROR('Inserting Canada zip codes into admZipCodeAutoFill...', 10,1) WITH NOWAIT
INSERT INTO admZipCodeAutoFill (Zip, City, [State], [Default]) --What is the default??
SELECT PostalCode, CityName, ProvinceAbbr, 0 FROM #CanadaZipCodeDownloadImport
END
RAISERROR('Cleaning up...', 10,1) WITH NOWAIT
IF OBJECT_ID(N'[dbo].[Gen4Digits]','FN') IS NOT NULL
DROP FUNCTION [dbo].[Gen4Digits]
GO
IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE [type] = 'U' and [object_id] = object_id(N'tempdb..#ZipCodeImportOptions'))
DROP TABLE #ZipCodeImportOptions
GO
IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE [type] = 'U' and [object_id] = object_id(N'tempdb..#USZipCodeDownloadImport'))
DROP TABLE #USZipCodeDownloadImport
GO
IF EXISTS (SELECT * FROM [tempdb].sys.objects WHERE [type] = 'U' and [object_id] = object_id(N'tempdb..#CanadaZipCodeDownloadImport'))
DROP TABLE #CanadaZipCodeDownloadImport
GO
RAISERROR('Recreating index on admZipCodeAutoFill table for Zip column...', 10,1) WITH NOWAIT
CREATE NONCLUSTERED INDEX [IX_admZipCodeAutoFill_Zip] ON [dbo].[admZipCodeAutoFill]
(
[Zip] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
PRINT('Finished')
SET NOCOUNT OFF