ICON
Help Center / How to export maps to Excel? /

How to export maps to Excel?

Trying to export your map's data might result in an Excel file/CSV that's cluttered with long and confusing text fields. These text fields contain your dataset's geographical data (its geometry, the_geom). This data is helpful when plotting your coordinates on a map, but — as you can see in the example below — it can make the file appear unreadable.

this is an image

Keep reading to learn about exporting your map's data into a clearer, more readable format both with and without geometry data.

Export data without its geometry

The desired outcome of this export is a better-formatted csv without the exceptionally long text fields shown above. This is especially important if you'd like to later use this dataset in one of your Spotzi maps.

How can this be accomplished?

To remove these text fields from your CSV simply remove the file's geometry column using Excel.

1. Open your map and export the data to CSV.

this is an image

2. Open an empty data sheet in Excel and import your CSV by clicking DATA > From Text/CSV. Select the CSV file you have just downloaded.

this is an image

3. Excel will offer you a preview of how your data will be imported. Press LOAD to confirm the import.

this is an image

Noticing unusual text characters in your file?

If you notice any unusual character additions or replacements — for example, if Völkermarkt is appearing as "Völkermarkt" in your file — simply change the file origin to Unicode (UTF-8) in the top left corner of the file preview.

4. Select the geometry column and delete it from your dataset.

this is an image

5. You have successfully deleted the geometry column from your CSV file.

this is an image

Convert your geometry into proper coordinates before importing your data into Excel

Spotzi point datasets contain the_geom columns with latitude and longitude coordinates. Exporting the_geom converts this column into well-known Binary format (WKB). This means that a field containing "-73.9695, 40.6754" will then appear as "0101000020E6100000000000800D7E52C0E128A68274564440" in your CSV. You can export the_geom as latitude and longitude coordinates instead by modifying your dataset using an SQL query.

  1. Create one new number-type column for latitude, and one for longitude
  2. Use the Editor custom SQL query option. Enter:UPDATE my_dataset
    SET lon_column = ST_X(the_geom), lat_column = ST_Y(the_geom)
  3. Replace your datasets name with my_dataset
  4. Replace your new blank longitude column with lon_column
  5. Replace your new blank latitude column with lat_column

Click "apply query" to split the the_geom coordinates into latitude and longitude columns. These column values will now appear as numbers when you export your dataset as a CSV.

Related blog posts

Access the Free Spotzi Explorer

Gain access to our free catalog of maps and dashboards. Find Postal Codes, learn more about global income differences or just get inspired by our community maps. Spotzi Explorer is our free solution for every marketer, researcher, student and beyond. Available as a stand alone solution and part of our price plans.

Get free access to Spotzi Explorer Read more
thumbnail Increase Campaign Effectiveness with Near Real-Time OOH Measurement

Blog

Increase Campaign Effectiveness with Near Real-Time OOH Measurement