# Formatting your spreadsheet ## How to prepare your spreadsheet for geocoding How to best prepare your spreadsheet (Excel or CSV file) to give us the best chance of geocoding each line correctly ### Overview This page lists advice for formatting your spreadsheet so we can geocode each row By following these guidelines you give us the best possible chance of geocoding the contents of your file correctly ### Step by step guide to formatting your spreadsheet for geocoding 1. #### DO upload only spreadsheets in a supported file format The file needs to be a format we support. Good: `addresses.xlsx` Not Good: `addresses.pdf` Not Good: `addresses.doc` We support the following file types: .csv, .ods, .tsv, .txt, .xls, .xlsx 2. #### DO put the data in the first sheet If you are using a software like Excel where you can have multiple "sheets", please put the data you want geocoded in the first sheet. 3. #### DO add a header row Use the first row of the file to tell us what the columns mean so we don't have to guess. Good: | | A | B | C | | --- | --- | --- | --- | | 1 | id | latitude | longitude | | 2 | 123 | 52.5432379 | 13.4142133 | | 3 | 456 | 51.522664 | -0.102534 | Not Good: | | A | B | C | | --- | --- | --- | --- | | 1 | 123 | 52.5432379 | 13.4142133 | | 2 | 456 | 51.522664 | -0.102534 | Good: | | A | B | | --- | --- | --- | | 1 | id | address | | 2 | 123 | Pappelallee 78/79, 10437 Berlin, Germany | | 3 | 456 | 82 Clerkenwell Road, London, EC1M 5RF, United Kingdom | Not Good: | | A | B | | --- | --- | --- | | 1 | 123 | Pappelallee 78/79, 10437 Berlin, Germany | | 2 | 456 | 82 Clerkenwell Road, London, EC1M 5RF, United Kingdom | 4. #### DO NOT send us personal or confidential data in your file Your file is sent to us for processing, so only include what is actually needed to geocode each row. See [our GDPR / data protection page](https://opencagedata.com/gdpr) for more on how we handle data. Not Good: | | A | B | C | | --- | --- | --- | --- | | 1 | employee name | salary | address | | 2 | Alice | 65000 | Pappelallee 78/79, 10437 Berlin, Germany | | 3 | Bob | 50000 | 82 Clerkenwell Road, London, EC1M 5RF, United Kingdom | Good: | | A | B | | --- | --- | --- | | 1 | id | address | | 2 | 123 | Pappelallee 78/79, 10437 Berlin, Germany | | 3 | 456 | 82 Clerkenwell Road, London, EC1M 5RF, United Kingdom | 5. #### DO ensure your file obeys the row limits - **Free trial accounts** can geocode spreadsheets of up to 100 rows. - **Subscription customers** can geocode spreadsheets up to 10,000 rows. - **One-time plan customers** can geocode spreadsheets up to the number of requests the have remaining or 10,000 rows. If your file exceeds the row limits, the upload will be rejected rather than silently processing only part of the file. 6. #### DO ensure your file obeys the file size limits Files can be at most 10 MB. If your file exceeds the file size limits, the upload will be rejected rather than silently processing only part of the file. 7. #### DO put the address in a single column When forward geocoding, ideally you should put the entire address in a single column to reduce the chance we make a mistake when assembling the address pieces. Good: | | A | B | | --- | --- | --- | | 1 | id | address | | 2 | 123 | Pappelallee 78/79, 10437 Berlin, Germany | | 3 | 456 | 82 Clerkenwell Road, London, EC1M 5RF, United Kingdom | Less Good: | | A | B | C | D | E | | --- | --- | --- | --- | --- | --- | | 1 | id | street | postcode | city | country | | 2 | 123 | Pappelallee 78/79 | 10437 | Berlin | Germany | | 3 | 456 | 82 Clerkenwell Road | EC1M 5RF | London | United Kingdom | 8. #### DO follow our advice for formatting the address We do much better if you can follow our [query formatting guidelines](https://opencagedata.com/guides/how-to-format-your-geocoding-query), for example things like telling us which country the address is in. Good: | | A | B | | --- | --- | --- | | 1 | id | address | | 2 | 123 | Pappelallee 78/79, 10437 Berlin, Germany | | 3 | 456 | 82 Clerkenwell Road, London, EC1M 5RF, United Kingdom | Not Good: | | A | B | | --- | --- | --- | | 1 | id | address | | 2 | 123 | Pappelallee 78/79, 10437 | | 3 | 456 | 123 Main St, 81003 | 9. #### DO put coordinates in separate columns and format them correctly When reverse geocoding, you should have one column for latitude, and one column for longitude. The values should be in decimal format. Watch out for spreadsheet software silently mangling coordinates: Excel in particular can convert a long decimal like `13.4142133 ` into scientific notation ` 1.34E+01` if the column is formatted as "General" Some locales use a comma rather than a period as the decimal separator, which can corrupt the column entirely when exported to CSV. Before uploading, format the latitude/longitude columns as plain "Number" (with enough decimal places) or "Text", Spot-check a few rows after saving to confirm nothing was rounded or reformatted. Good: | | A | B | C | | --- | --- | --- | --- | | 1 | id | latitude | longitude | | 2 | 123 | 52.5432379 | 13.4142133 | | 3 | 456 | 51.5226742 | -0.1025238 | Not Good: | | A | B | | --- | --- | --- | | 1 | id | coordinates | | 2 | 123 | 52.5432379, 13.4142133 | | 3 | 456 | 51.5226742, -0.1025238 | Not Good: | | A | B | C | | --- | --- | --- | --- | | 1 | id | latitude | longitude | | 2 | 123 | 52° 32' 35.65644'' N | 13° 24' 51.16788'' E | | 3 | 456 | 51° 31' 21.62712'' N | 0° 6' 9.08568'' E | 10. #### DO remove duplicate rows Each row counts as an API request. Good: | | A | B | | --- | --- | --- | | 1 | id | address | | 2 | 123 | Pappelallee 78/79, 10437 Berlin, Germany | Not Good: | | A | B | | --- | --- | --- | | 1 | id | address | | 2 | 123 | Pappelallee 78/79, 10437 Berlin, Germany | | 3 | 456 | 82 Clerkenwell Road, London, EC1M 5RF, United Kingdom | | 4 | 123 | Pappelallee 78/79, 10437 Berlin, Germany | 11. #### DO save your file as UTF-8 if it contains non-ASCII characters If your addresses include accented characters, umlauts, or non-Latin scripts, make sure you export/save the file as UTF-8. Some spreadsheet software defaults to a different encoding when saving as CSV, which can silently corrupt those characters, turning input like `München ` into ` München`. ### Final thoughts You might ask why you need to bother doing all this, surely we should catch common problems on our side? A fair question. We do try to catch obvious things, of course. As you can imagine though, it's difficult for us know all the peculiarities of your data in your language and country. The more you can do to simplify, clean, and correct your spreadsheet, the better a chance we have to geocode each row quickly and correctly. ### More about spreadsheet geocoding You may want to [read our detailed spreadsheet geocoding tutorial](https://opencagedata.com/tutorials/geocode-a-spreadsheet), or watch [a tutorial video](https://www.youtube.com/watch?v=dxbPXHMWIlo) (on YouTube). ### Happy geocoding!