How Do I Turn Spreadsheets into Lat/Long Coordinates?
The "Fast Lane" Answer
So you've got a spreadsheet full of addresses, and you need to convert them into geocodes. You don't want to enter them in one-by-one on a geocode provider's website, though, because that will take way too long. The answer to this problem is something called "batch processing," and it allows you to quickly and painlessly enter your data all at once.
Here at SmartyStreets, we process your batches for you (that's addresses, not cookies). It's remarkably simple, but we wanted to make it even simpler, so here's a brief rundown of how to use both our Web Interface and our Command-Line Interface.
Web Interface: Copy your addresses from a spreadsheet or similar file, and paste it into the empty white box. Hit the blue button. Enjoy!
Command-Line Interface: Download and set up the tool. Open your command line. Run the tool. Blink, and it's done!
And now for the same thing again, but with more words and pretty pictures.
The "Scenic Route" Answer
We have two speeds of batch processing: fast, and "Whoa! What was that?" fast. The former is a copy-paste method that can take up to 25,000 entries at once. The second is our Bulk Address Validation Tool Command-Line Interface that can take all your base and then some. The copy-paste Web Interface receives input that is, well, copied and pasted. The Command-Line Interface can upload whole CSV files (and only CSV files). Below is a step-by-step description of how to get the job done with either method—complete with pictures, because who doesn't like pictures?
Let's do the super user-friendly method first. The copy-paste method is the simplest solution for anyone processing 25,000 addresses or less, who maybe don't have the computer fluency to run something on the command line. Here's the step-by-step (complete with pictures!).
Step 2: Open up your spreadsheet of addresses.
Step 3: This is the copy part. Select the columns from your spreadsheet that have the relevant address data and copy them.
Step 4: Open the "List Processing" tab of your account.
Step 5: This is the paste part.
Step 6: Press the big
red blue button.
Step 7: Your list will probably be done processing by about the time you're done reading this sentence.
Step 8: Your new data arrives (isn't it cute?).
Step 9: Select the new data (there's even a handy button) and copy.
Step 10: Open a new spreadsheet. Warning: Never overwrite your original data!
Step 11: Paste your shiny new data into your immaculate spreadsheet.
Step 12: Save the file.
That's it. You're finished. You've successfully completed the copy-paste competency exam. We're betting it's the fastest test you've ever taken.
The copy/paste method is easy by design. It's hard to go wrong with a method you use all the time to grab chocolate chip cookie recipes off the internet. The Command-Line Interface is a little different. While it can handle limitless amounts of requests, it takes a little bit more time—and a little bit more tech know-how—to get it up and running. If you get clammy hands and start sweating any time you hear a phrase like "defragmenting the hard drive," then you have scrolled past the answer you need. Please hang up and try again.
For those of you who know recursion is self-referential (like this joke), you're probably familiar enough with a command prompt that discussing how to use one doesn't drain the blood from your face. So here's how to use the Command-Line Interface, broken into three parts.
Getting the Tool
Checkmate in three moves:
We'd get all fancy with extra words and details here, but someone beat us to it.
The Man Behind the Curtain
Open the command line on your computer. Do you know how to do this step? If answer=true, move on to the next section. If answer=false, shouldn't you be reading the copy-paste method?
Putting the Tool to Use
Step 1: Once you've sorted out the above details and gotten your config.json file set up, open the command line. (If you're wondering why there are no pictures here, see above.)
Step B: Enter the command
smartylist -input="path/to/your/file/here". (You can see a complete run-down of Command-Line Flags here.)
Step III: Hit Enter. The tool will give a summary of what it's about to run. Check to make sure everything looks right. If yes, enter "y".
Step 4th: Wait for it to process. (Less "elevator music," and more "Ding! You are here.")
Lastly: You're done! Gee, that didn't even give you time to grab a drink. In any case, your new geocodes (along with some other address mumbo-jumbo) will materialize in a new file labeled "output" (don't want to overwrite the original), in the same place the input file came from.
Processing large lists of addresses with SmartyStreets is quick and simple, because we don't like things to be slow or difficult. That's why none of us has never climbed Everest.
This has been an egregious exercise in over-simplification. We hope you've enjoyed it as much as we have.
And if anyone asks...no, this article is not purposefully recursive.