How to Geocode Spreadsheets via API with the Geocodio Lists API

The following is a transcription of the Youtube video below. See for visual reference.

Hey everyone, Cory here, Sales and Support Engineer at Geocodio. Today we're diving into the Geocodio Lists API - a powerful feature that lets you upload spreadsheets programmatically. If you've ever needed to geocode several large datasets at once, this is going to save you tons of time!

By the end of this video, you'll learn how to upload a spreadsheet list to Geocodio, check its status, download your results, and manage your geocoding jobs. Let's get started!

What is the Lists API?

Our Lists API endpoints allow customers to process spreadsheets directly through our API. Functionally, it operates like our spreadsheet uploading tool, though it can be integrated into your own system to save you the effort of manually uploading each file.

Unlike our standard API endpoints, which accept either a single address string or batches of up to 10,000, the Lists API lets you upload CSV, TSV or Excel files containing up to 10 million addresses and Geocodio will process them all in the background.

Once the geocoding job is complete, you’ll be able to download the updated version of your file with new columns appended that return the data you’ve requested.

Just a heads up - your processed data is automatically deleted after 72 hours, so make sure to download your results in time!

Configuring Your API Key

If this is your first time using the Lists API, you may need to take a minute to configure your API Key to use these new endpoints. By default, they’re turned off, but you can access the Lists API permissions and switch them on at no additional cost to your existing account.

In your account dashboard, click on the “API Keys” tab. This will take you to a page that will list all of your active API Keys.

From here, choose the API Key you’d like to use and click “Edit”.

This will open a new menu with a number of different options. What we want to focus on are the three Lists API endpoints: POST, GET and DELETE. Assuming that you would like all of our available options, click the checkbox next to all three endpoints to provide permission for the API Key to use them. Then, click the “Save” button.

You’re ready to go!

Creating a New List

Let's start by creating a new list, which will allow you to upload your spreadsheet. You'll need to make a POST request to the /lists endpoint. Here's what a basic curl command looks like:

I’ll break down these parameters:

• file: This is where you’ll add the name of your spreadsheet file. Be sure to include the extension.
• direction: Here, you’ll tell us which direction you’d like to geocode your data: "forward" if you have a list of addresses to retrieve coordinates for, "reverse" if you have a list of coordinates to match with addresses.
• format: This is the magic sauce - it tells Geocodio how to read your spreadsheet.

The format parameter uses a simple template system. If your full address is in column A, use "{{A}}". If the street is in column A and ZIP is in column D, use "{{A}} {{D}}". You can even add static text like "{{A}} Washington DC" if all your addresses are in the same city. In this example, we have the street data in column A, the city in column B, the state in column C and the Zip Code in column D: thus, "{{A}} {{B}} {{C}} {{D}}".

You may also notice two optional parameters:

• filename: This is only required if file contents are sent inline
• callback: Here, you can provide a valid URL that a webhook should be set to upon completion of the spreadsheet geocoding job

You can use these optional parameters to meet your unique requirements, but the first three parameters are mandatory when creating any list.

If you run this curl command and it’s successful, you’ll get back a JSON response with a list ID - save this, you'll need it!

You can also use this response to verify that your headers are accurate and that the number of rows we’ve received matches the document you uploaded.

Checking List Status

Once you've uploaded your list, you'll want to check its progress. Use a GET request with the list’s ID to retrieve this information:

The response shows you everything you need to know:

• state: Will will indicate whether your file is "PROCESSING" or "COMPLETED"
• progress: Informs you of the percentage of the file that has completed processing
• time_left_second: Estimates how much time is left until the job is finished, in seconds
• download_url: Includes a link to download the finished spreadsheet. This only appears when the job is complete.

Quick tip: You can configure the callback parameter when creating your list to set up a webhook URL that will notify you automatically when the upload is finished!

Viewing All Your Lists

If you need to see all your spreadsheets, there's an endpoint for that as well.

This returns a paginated list of all your geocoding jobs, showing 15 at a time. It’s perfect for keeping track of multiple processing spreadsheets or for finding that list you uploaded yesterday. Each list returns with its ID, so if you lose track of an ID after an initial upload, this is a great resource to help you find it.

Downloading Your Results

Once your list shows the "COMPLETED" status, you can download the results using this command:

The important thing here is the -L flag - this tells curl to follow redirects.

Your results come back as a UTF-8 encoded CSV file with tons of useful information: You'll get your original data plus geocoding results, accuracy information, and standardized address components. If you choose to append any additional datasets, that information will be included as well.

Deleting Lists

Finally, you can delete spreadsheet lists to clean up your account:

This command permanently deletes the list and all its data. You can even use this to cancel a job that's currently processing - handy if you realize you made a mistake in your format string!

Quick note: Geocodio Unlimited customers can cancel or delete a spreadsheet upload at any time. Pay-As-You-Go customers can only cancel a spreadsheet upload if it was just recently started.

Your spreadsheet will always be deleted automatically after 72 hours, so if you forget to manually delete it yourself, we’ve got you covered!

Best Practices & Tips

Before we wrap up, here are some best practices to enhance your experience with the Lists API:

• Keep your spreadsheets under 10 million rows - larger batches should be split up.
• The file size limit is 1GB - but you probably won't hit that with text data. It’s just something to be aware of.
• Test your format string with a small sample spreadsheet first. If you use the exact same format in the sample, this might help you catch any potential formatting issues before you send the final job. That way there are no surprises in your results.
• Remember the 72-hour deletion policy - download your results promptly!
• Use webhooks for long-running jobs so you don't have to keep checking your status.

The API supports CSV, TSV, XLS, XLSX, and even ZIP files containing one of these formats.

Conclusion

And that's the Geocodio Lists API in a nutshell! It's incredibly powerful for bulk geocoding tasks and can save you hours of manual work.

As a final note: All of the official Geocodio libraries - including PHP, Node.js and Ruby - support our Lists API. You can also access it through our CLI tool, which I’ll provide a link to in the description down below.

Feel free to drop a comment or reach out to us at support@geocod.io if you have questions - we love helping out the community!

Until next time, happy geocoding!

Video Reference