Spreadsheets and tabular data containing geographic coordinates can be uploaded to Mango in Comma Separated Value (CSV) format.

CSV are one of the most common file formats uploaded to Mango, but as they are essentially just a text file without a well adhered to standard, it's quite common to have a CSV that isn't supported by Mango.

Here's the low down on essential formatting, and content, that a CSV must contain in order to be processed successfully.

 

• Required geometry columns
• Valid coordinate column names
• “My spreadsheet doesn’t contain coordinates. What should I do?”
• Supported coordinate format
• Preparing Your CSV for import
• Common causes of CSV upload errors 
• My number columns are importing to Mango as text strings

 

Required columns: latitude and longitude

CSV uploads must contain geographic coordinates in decimal degrees format in two columns: one for latitude and one for longitude. Without coordinates, Mango cannot map the data.

We recommend that the first two columns of the table are used for coordinates.

 

 

Valid coordinate column names

Latitudelaty

Longitudelonlongx

There should be no spaces before or after the name.

lat ,long, 🚫

lat, long, 🚫

lat,long, 👍🏼

Latitude indicates position on the vertical, or y plane ↕

Longitude indicates position on the horizontal, or x plane ↔

The coordinate pairs x,y lat,lon, lat,long, latitude,longitude are reserved for coordinates. To avoid importing issues, use only one instance of these pairs in your header row. For example, if you have coordinates in columns latitude,longitude, do not also use use x,y, lat,lon, or lat,long for any other column names. 

 

Coordinates must be in Decimal degrees (DD) format

Supported decimal degrees coordinates: 

12.235216, 13.252125

Unsupported coordinates:

N 12.35216, E 13.252125

12°15'24.6276", 13° 11'42.5940"

12°15'24.6276"N, 13°11'42.5940"W

///brandishes.muffin.seabirds

SU01030503022

CT-QP-ED-TP

7GXHX4HM+3C

street a, street b

 

 

“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:
How to Map Spreadsheet Addresses for Powerful Insights

Areas and boundaries
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:
Create a Map from a Spreadsheet Containing Zip Codes, Counties or States

 

 

Preparing Your CSV for import

To convert a spreadsheet into a CSV simply choose “Save As” in your spreadsheet program and choose CSV from the available file types.

For best results, export to CSV with UTF-8 encoding. UTF-8-BOM and UTF-8-SIG are not supported.

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.

Save As .csv from your spreadsheet application

 

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 text editor such as Sublime Text or Notepad++.

• We support all encodings except UTF-8-BOM and UTF-8-SIG. Export the CSV in a supported encoding such as UTF-8.
• Ensure coordinates are decimal degrees format, using only characters 01234567890 - . .
• Double check that your data contains Lat Long coordinates, and that the column headers containing the coordinates are named correctly as per the above valid column names.
• Ensure that column headers contain text. Column headers that contain only numbers are not supported.
• Double check that your data contains Lat Long coordinates, and that the column names containing the coordinates are name correctly as per the above valid column names.
• Ensure there are no trailing commas at the end of the file. This will be read as records without geometry and may cause a processing error.

• Ensure there are no trailing commas at the end of each line. This indicates a new column. Given that all columns require a column name, this will cause a failure.

• Ensure each column that contains records must have a name in Row 1. The example below is missing column names as indicated in orange. Either delete the column, or add a name.

• Ensure there is no text or blank rows before the column names, or after the actual data. In the example below, the rows highlighted in red need to be deleted.

• Ensure there are no carriage returns in your column names. This is indicated by names that break over two lines as shown below. Remove any carriage returns in your column names.

• Ensure your columns are separated by commas, not semicolons. This will not be apparent in your spreadsheet application, but will be visible by opening your CSV in a text editor.
• Individual column names 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.

 

Simply opening the CSV in MS Excel and saving it will address some problems, such as blank rows or columns.

 

My number columns are importing to Mango as text strings

If your CSV imports but you're unable to find any of the expected number columns when setting class breaks in Quantity style, then it's likely your numbers have been converted to strings on import.

Why is that?

Unlike other file formats that have methods for explicitly defining field types, CSV is just plain text and includes no information that indicates the type of values contained in the file. In the case of a string of text, like an address that may contain commas, it's possible to wrap the string of text in quotes to ensure that the commas in the address aren't mistaken for a comma separator like this:

40.706344, -74.012514, "50 Broadway 23rd floor, New York, NY 10004, United States"

When processing a CSV upload, we analyse the content and take an educated guess as to the column format. It goes something like this:

Mango: Does this column contain values wrapped in quotes?

CSV: No.

Mango: Got it. Not a string. Does this column contain numbers?

CSV: Yes.

Mango: OK, it's probably a number field. Just to be sure, does this column contain *any* non-numeric characters?

CSV: Yes, there's a few N/A's to indicate there was no value in rows 25, 79, and 152.

Mango: Thanks! I can only store numbers in a number field, so I'm going to have to reassign this as a text string

To resolve this and allow your numeric data to be imported as a number field, you must remove ALL non-numeric characters from the column (except, of course, the column title and any minus signs to indicate a negative value), and reupload the dataset.

After reupload, you can open the styling panel and the columns will be available to use for Quantity class breaks.