Instructions for an orderly POI file for quick and easy editing
 |
    14 years
|
Since I am hunkered down at home (retired and at high risk for Corona virus complications), I decided to take a break from my genealogy research. This is my 2nd FAQ in a week.
I have been maintaining the Bank of America (BoA) POI file for 10 years now. During those 10 years it went up to about 10,355 locations (started from 9,515 locations). Then BoA had problems from expanding too fast and started closing branches and adding ATMs near the old branch. It now contains 7,207 locations and took a lot of work to keep up with the rapid decline in some areas and growth in others. I am glad that BoA changed their "Locator" web page to only display the locations in the town/city that you clicked on in the state's list. It used to list all the locations in a 100 mile radius of the town or city you selected. That meant that if you selected a small town near a big city. You would not only get the locations in that town, but you would also get the locations in the nearby city, or anything else in the 100 mile radius. The list was limited to 50 locations. On top of that, not all of the town locations were at the beginning of the list and could be scattered throughout the list and took up time searching through it (I think the locator search program used the geographical center of the Zip Code, rather than the actual address). I'm not really complaining, I'm just explaining some of the challenges the BoA file was and why I had to learn how to put order in the file to make it easier to maintain and have less errors or duplicates (dupes).
I have created another FAQ for POI file maintainers that will help folks maintain the file (especially large ones that change a lot) and will help a maintainer to avoid creating dupes.
The FAQ is rather long and is very detailed, but I wrote it so that even an Excel newbie could use it. Experienced Excel users should be able to zip through the steps quickly and be able to remember how to do it. Remembering how to create the Zip Code column will probably be the hardest because it uses a little known Excel formula that is rarely used.
Be aware that a POI file that is arranged in address order and then in Zip Code order will make it easier for the user as well to edit the file if they only want their state or locality in the file. There is an exception to that though. For some reason VA starts at Zip Code 20101 and stops at 20199. Then it begins again at 22003 and stops at 26886. MD is in between those 2 blocks starting at 20601 and ends at 21930. Had to be a committee that made that decision, LOL
Thank goodness my wife has over 30 years of Excel experience - she taught me a lot and I also bought a "How To" book.
If you find any errors or an easier way, let me know.
getting something done
Congratulations on getting something done while staying home, especially a highly technical and detail-oriented task. I am unable to do that right now, but your initiative has inspired me a little bit.
.
dobs108
Great Job
Thanks for your commitment.
Thanks
Thanks, dobs108 & MMtoTSS
Although the steps are lengthy due to detail, it takes only a few minutes to perform the operations once the fields are formatted. BUT,
it will save you hours of work in the future - especially on large POI files!
Metricman Nuvi 3597 GTM-60 Traffic Receiver Williamsburg, VA
Sorting advantages
No offence to Donaldb530, just using his file as an example.
I download several POI files before travelling any distance as there is nothing more disappointing then driving off the interstate hwy several miles to get a burger and discovering that the business is closed up for good! That's why it's good to update you POI files.
Anyway, when we travel cross country, I D/L the Walmart file in Rand McNally CSV format (it also contains Sam's Club and Murphy's Gas Stations).
I then use the "Concatenate" formula in Excel to create new columns in the format I prefer. Then I save the file as a .csv file.
Next I load the new .csv file into a new Excel file. I delete the old columns and Voila! I now have a new file that I can sort by column C to get all the different brand names into their own files.
It's a little bit of work, but call me "Anal Retentive" if you want to. LOL
Metricman Nuvi 3597 GTM-60 Traffic Receiver Williamsburg, VA
using excel on csv files
I am still using MSOffice Excel 2007. One of the free programs I use all of the time in Excel is ASAP. There is a version you can pay for. The free version has served me well for many years. Check with your better half, I am sure she can help you with all of ASAP's usefulness. One example is using the TEXT option to remove all leading, trailing and even excess spaces. This helps, as you mentioned, to avoid messing up when using formulas such as your RIGHT command. One can also Find & Replace almost any character that is unwanted or needs to be changed. The free version of ASAP has to be updated on an annual basis, but is well worth the effort - it even notifies you when a new version is available.
Sorry to take up your time here; however, my attempt to email you from the listing on this site resulted in "an un-deliverable" message.
Kudos to all at POI Factory - a great site!
Seems like
I am still using MSOffice Excel 2007. One of the free programs I use all of the time in Excel is ASAP. There is a version you can pay for. The free version has served me well for many years. Check with your better half, I am sure she can help you with all of ASAP's usefulness. One example is using the TEXT option to remove all leading, trailing and even excess spaces. This helps, as you mentioned, to avoid messing up when using formulas such as your RIGHT command. One can also Find & Replace almost any character that is unwanted or needs to be changed. The free version of ASAP has to be updated on an annual basis, but is well worth the effort - it even notifies you when a new version is available.
Sorry to take up your time here; however, my attempt to email you from the listing on this site resulted in "an un-deliverable" message.
Seems like it could be useful, but I really don't use Excel often enough to see a need for using it - the paid version that is. I may check out the free version, but MS has often squashed these add-ons by incorporating their own look-alike tools. It used to be that you couldn't save an office sheet as a PDF file. Then a company in New England came out with an add-on to create a PDF (was a company called ABC something). And they sold a ton of them as it was one way you could put something on the web that everyone could see. Well, sure enough, MS added that option in the next upgrade. I have Office 2016 which I haven't installed yet. It wouldn't surprise me if all the utilities in ASAP are now included.
Metricman Nuvi 3597 GTM-60 Traffic Receiver Williamsburg, VA
Thank You!
Thank you for the information and thank you for maintaining the Bank of America file. I can't tell you how many times it has come in handy for me while traveling out of town and trying to save ATM fees.
ATM fees
Thank you for the information and thank you for maintaining the Bank of America file. I can't tell you how many times it has come in handy for me while traveling out of town and trying to save ATM fees.
Thanks for the Thanks. I did it for me as well as for you and other users.
It's ridiculous how much you get can get charged. Not only does the Off Brand ATM charge a fee, then your own bank will charge you a fee for not using their bank's ATM. I know they have to pay for an armored truck to put the money in the ATM, but it can't cost that much. Just mostly profit in the fees.
Metricman Nuvi 3597 GTM-60 Traffic Receiver Williamsburg, VA
Thanks to POI Factory
I used MS Word to create a PDF file of the lengthy instructions and Jonathan figured out to create a download link to the file.
You can now click on the link at the top of the page and once the PDF appears, right click on the page and select "Print" in the dropdown. Or you can do a "Save as".
Thanks to Jon for figuring out how to manipulate the "permissions". Now you won't have to flip between windows.
Metricman Nuvi 3597 GTM-60 Traffic Receiver Williamsburg, VA