Spreadsheets and tabular data containing geographic coordinates can be uploaded to Mango in Comma Separated Value (CSV) format.
- The first line of your CSV file must contain the column names and include two columns for geographic coordinates.
- String values must be double-quoted. For example:
|1.||lat, lon, cu mi, name, description|
|2.||-11.984723, 34.488663, 2000, “Lake Malawi”, “Also known as Lake Nyasa in Tanzania and Lago Niassa in Mozambique”|
|3.||-6.258643, 29.543102, 4500, “Lake Tanganyika”, “Second oldest freshwater lake in the world”|
Mango supports the Decimal degrees (DD) format for coordinates in CSV: 12.235216, 13.252125
|Correct Coordinate Format||Incorrect Coordinate Formats|
|cafe||12.235216||13.252125||cafe||N 12.235216||E 13.252125|
CSV uploads must contain geographic coordinates in decimal degrees format in two columns: one for latitude and one for longitude. e.g.:
Valid column headings for coordinates:
Preparing Your CSV
To convert a spreadsheet into a CSV simply choose “Save As” in your spreadsheet program and choose CSV from the available file types. CSVs cannot contain styling or multiple sheets, so any styling or additional sheets contained in your spreadsheet will be lost when saving. Create a backup of your original spreadsheet prior to exporting to CSV to ensure your data is safe.
CSV Upload Errors
There are many different programs that can generate CSV’s and as there is no official file standard for CSV, some programs will format them in different ways to others.
If your CSV is formatted as per the instructions above please check the following using a spreadsheet program, such as MS Excel.
- Double check that your data contains Lat Long coordinates, and that the column names containing the coordinates are name correctly.
- Open the .csv file in a text editor (not spreadsheet program) and check for trailing commas.
- Each column in the CSV must have a name. The example below is missing column names. Either delete the column or add a name.
- There should be no text or spaces before or after the actual data. In the example below, the rows highlighted in red need to be deleted.
- There should be no carriage returns or line breaks in your column names or fields. This is indicated by names that break over two lines as shown below. Remove any carriage returns in your column names. In Excel, this can be automated with the Find/Replace function. In the find what field, click to place the cursor in the field, and press CTRL + J. A small dot will appear in the field, indicating a line break. In the replace with field, add a space, then click Replace all.
- Ensure there are no blank columns in the CSV
- Ensure there are only numbers (and a period “.”) in Lat/Lon columns
- Ensure your columns are separated by commas or semicolons (This will not be apparent in your spreadsheet application, but will be visible by opening your CSV in a text editor).
- Individual fields cannot contain a comma. If you see a column name with two names and a comma, as per the example below, you will need to edit the fields to separate them into multiple columns or wrap the text string in double quotes.
It is common that datasets will contain characters that have been replaced when moving between text encodings, and these are known to cause issues with CSV uploads.
For example, what may look like a normal character in a spreadsheet may be an encoded character from outside the valid range – sometimes it may appear as a question mark in place of an apostrophe, or as a series of ‘special characters’ indicating an accented character such as ñ or ó.
If these characters were encoded into a different character set (eg. ISO-8859-9 to UTF-8) which does not contain that character, they are replaced with new characters.
Using an advanced text editor with a regex search feature such as Sublime Text can help identify problem characters. You can identify all non-ascii characters using the following regex:
“My spreadsheet doesn’t contain coordinates. What should I do?”
If your CSV only contains street addresses, you must geocode the addresses into coordinates before uploading to Mango. Please read this tutorial for instructions
If your CSV does not contain coordinates but has other information such as administrative boundaries, fips codes, zip codes, county or state names, then please read this tutorial for instructions
Need more help with this?
✉ Email the Mango support team