How to find and map Licensed AmRadio Operators (Hams) in your local area.
Posted December 8th, 2009 by Kim
in
Those attending our recent EmComm and Amateur Radio webinars have seen me import and map a spreadsheet of several hundred HAM licensees for my local community. Many have asked for the step-by-step procedures for getting such a list of local Hams from the FCC database. In response to these requests, I've built a power point presentation that shows the detailed, sequential steps for getting this information from the FCC database. You can watch this presentation here. And if you have Depiction, you can easily import and map the downloaded data to see the names, call signs and locations of the active HAM licensees in YOUR community.
Comments
Excel and CSV Issues
Well, I've had some intersting issues with this process. To be sure, the process described in the presentation to which this blog links works just as described. However...
In working with 2 different versions of Microsoft Excel, I have had interesting problems in getting a CSV file to the point that Depiction could import it without errors. I have used Excel in versions 2003 and 2000. (Haven't tried 2007 yet.) Here's what I've seen happen: When I import the raw text file (as sent by the FCC), I had no problem importing it as described, telling Excel it was a bar-delimited file. So far so good. I then manipulated it as described in the presentation, using only the EN prefix files. Also added a row one for column headers and set them to be things I wanted and that would fit into Depiction's overal schema.
BUT: When I saved the file as a CSV (from the XLS format), it turns out it did NOT put commas every place they should have been, thereby making Depiction believe the file was corrupt. I had to open the XLS file using Notepad (or some other generic text-only) text processing program. A word: Notepad is looking for TXT files - you will have to tell the progam to look for ALL file types, then it will see the CSV file. Once the CSV file was open, I could count the fields in the top row - i.e. my column headers. I could then see what fields below did not have commas, etc. What I found was that below 15-20 rows, Excel was NOT putting the commas in as field delimiters like it should when I tell it to save the whole thing as a CSV file.
Finally got the files so that Depiction could import them and use them as designed. Anybody else run into this? Have a different/better fix?
BTW - I've gotten the most wonderful and timely help from Kim, George, and David at Depiction over this and other issues! Their responsiveness can't be beat!
Thanks!
Bill Farnham KI4FZT / AAR4CN
Acting COML (certification pending)
Division of Emergency Communication
Roane County Office of Emergency Services & Homeland Security
Emergency Coordinator - Amateur Radio Emergency Service
Emergency Preparedness - spanning the gap between "it won't happen to me" and
"we're all going to die!"
Excel 2007
I have used 2007, but first in any data import to the file I go through each column and row for errors that could be in the cell. I remove any P.O. Box or busted addresses. After the reveiw I do a save as for the CSV file name that I will load. The latest version 1.2.1 provides a good selection for address selection as well as Lat/Long if that data was available in the records.
The issue I have is icon assignments: Licensed Ham, CERT Trained, ICS Trained, etc., so an icon representing the "asset" could be ploted for SA usage during a real incident. Tough job to get all ARES/RACES and ecomm folks to agree on what icons to use, but it sure would expedite development time if one group worked on this.
Similar with Red Cross volunteer trained and special skills. Scrolling down each volunteer in the view/edit or hovcer text selection makes it hard to select the "crew". It would be nice to have another menu for a pull down of resources defined (loaded) and not the Add menu to select from.
Greg, K7SDW