Find us on FacebookFollow us on Twitter  Find us on Facebook and Twitter







Welcome to the CDXZipStream December 2010 Newsletter


Our route optimization template now has the ability to output an optimized list of addresses to a gpx file that can then be downloaded to a GPS device, such as Garmin or TomTom.  We discuss a common support question that comes up when using zip code and address functions.  And finally, we review our fully updated list of available demographic fields and definitions. 

Here are our new December blog articles:
   - Why Can't MapPoint Find My Address?
   -Enabling Macros in Our CDXZipStream Excel Templates

   -What's a ZCTA?

Download an Optimized Route to Your GPSDownload an optimized route to your GPS


Last month we talked about free Microsoft Excel templates that allow you to use CDXZipStream with just a click.  As a result of customer requests in the interim, our route optimization template now has a new feature:  the ability to create a gpx file of the optimized route which can be downloaded to a GPS device such as Garmin.  It can also be downloaded to Microsoft MapPoint, a new feature that Microsoft introduced this year.


To use the template, just enter a list of addresses, click a button, and the list is automatically reordered into an optimized route.  Click another button to create a gpx file of the route.  It's that simple.  You're now ready to download the file to your GPS; no manual data entry to the GPS is necessary.


If you are unfamiliar with downloading data files to your GPS, please refer to the manufacturer's website for more information. For example, the free software POI Loader is used to import data to Garmin devices, and can be downloaded directly from their website here. If you need to convert the gpx file to another format, there are also a number of free converters available, such as Extra_POI_Editor.  We do expect to be adding other file formats to the template in the near future; please contact us if you would like to provide input.


Why Can't Excel Find My Zip Code?

One of the most common support questions we get involves difficulties matching up zip codes in a spreadsheet analysis.  A typical situation is a radius search where an Excel function named VLookup is used to compare a list of addresses to a set of nearby zip codes within a certain distance.  Users often report that all the formulas return #NA indicating that no matches were found.  Another concern is not being able to find the matching zip code from a set of addresses obtained from a database or copied from the internet.  So what's causing this and what can you do about it?


In most cases, the problem is due to extra data, often hidden, appearing with the imported address or zip code data.  Importing data from other sources often brings along non-printing characters.  This causes a zip code such as "07869" to instead be entered as "07869 " (with a blank space at the end) which will not compare correctly.  You may not be able to see this problem simply by looking at your spreadsheet, but if you inspect the data in the Excel formula bar to edit it you will see the extra characters represented as blank spaces.


For a radius search you need to make sure that you are comparing similar data.  The CDXRadius function returns a list of 5 digit zip codes in text format and need to be matched up with identical items in your address list.  If the address list contains Zip + 4 or other numeric data, problems will occur.  And if  zip code data is input into a cell formatted for numbers, all leading zeroes will be dropped causing further difficulties matching up results.


In addition to making sure that you have your data formatted as text you can also use the Excel LEFT function to return only the first five characters of your zip code column.  We've written a support article on doing this in a radius search which can be found here.  The CDXZipCode function can also return a properly formatted zip code from your data.  This will automatically trim Zip + 4 entries to five characters and restore any dropped leading zeroes.


If your data is text and doesn't involve zip codes, other functions Excel functions can be used.  Suppose you have a valid city, state combination but it has extra blanks or non-printing characters.  Simply enter the formula "=CLEAN(TRIM(D2))" in an adjacent column, where D2 in this case is the cell containing the data you need to modify.


Using the above techniques with Excel and CDXZipStream should in almost all instances solve your problems with matching data in a zip code analysis.


Demographic Data Fields and Definitions


With the extensive data fields available through our zip code and other data feeds, it was time to update and expand the list of all fields, along with their definitions.  You can view the new list as one of our technical articles, or download it as an Excel file.  This covers all fields for the following data feeds:


   CDXZipCode (Basic and Premium)                       CDXCensus

   CDXAreaCode                                                    CDXCensus2

   CDXState                                                           CDXACSCBSA  (Core Based Statistical Area)

   CDXCounty                                                        CDXACSCounty

   CDXCity                                                             CDXACSPlace  

   CDXCBSA (Core Based Statistical Area)              CDXACSState


The last four data feeds are from the U.S. Census Bureau American Community Survey (ACS), which provides very detailed demographic data and has replaced the old long form survey from the 10-year census.  The term "place" as used in the CDXACSPlace data feed, refers to a U.S. Census Burea -defined area and is a catch-all term that includes cities, townships, boroughs, municipalities, hamlets, etc.



As always, we hope you find the information in our newsletter useful.  You can contact us with your feedback and suggestions by replying to this email.

Have a great holiday season and happy new year!
The Team at CDX Technologies


December 2010




Map of the World


In This Issue


Download an Optimized Route to Your GPS


Why Can't Excel Find My Zip Code?


Demographic Fields and Definitions



Get our free route optimization template with GPX file creation


Join Our Mailing List 





CDX Technologies | 2 West Hanover Avenue | Suite 212 | Randolph | NJ | 08033