Back to Blog

CSV with Latitude and Longitude on a Map: Open It in the Browser and Fix the Seven Things That Go Wrong

Three columns — an id, lat and lon — saved as CSV and dropped into the csv-viewer: that is the whole tutorial for a clean file. Nothing uploads; the file is parsed inside your browser tab. If the header says lat and lon, the column picker is already filled in and you click Open on map.

The rest is about files that are not clean — Excel pt-BR or es writes -23,55 and ;, a title row sits above the header, degrees and minutes share one cell — and a viewer that guesses wrong does not always tell you. Each case: the file, what the viewer shows (quoted from our run, including where it shows nothing), the fix in Excel, LibreOffice Calc or Google Sheets, and the fix with ogr2ogr.

Tested on 15 September 2026 against static-page-tools@6016a1b plus the csv-viewer robustness change — decimal comma, skipped-row counter, Latin-1 fallback — unreleased at the time of writing; GDAL 3.13.3 "Iowa City" for the command-line transcripts.

What the viewer looks for

The first line is the header — always row 0 — and the delimiter is chosen by counting ,, ; and tabs on that line alone; the most frequent wins, a tie goes to the comma. Quoted cells are honoured; a line break inside one is not. No delimiter override, no paste box, no URL box; one file per drop, no byte limit.

The coordinate columns are found by name — case-insensitive, exact match — in three lists:

  • Latitude — lat, latitude, latitudine, latitud, y, y_coord, ycoord, ylat, lat_dd, latitude_dd.
  • Longitude — lon, lng, long, longitude, longitudine, longitud, x, x_coord, xcoord, xlon, lon_dd, longitude_dd.
  • WKT geometry — wkt, geom, geometry, wkb, shape, the_geom, geo, geomwkt, wkt_geom.

No exact match: any column containing lat, and any containing lon or lng; still nothing: the first two columns. These are pre-selections. The picker Select coordinate columns opens on every file, even a perfect one, with the hint Select which columns contain the coordinates. Auto-detection has pre-selected the most likely fields., a Latitude column and a Longitude column dropdown, and Open on map. A WKT column opens the same picker on its WKT / Geometry tab; there is no "mapped instantly" path.

On confirm, the layers panel prints 3 points for a three-row file and, when rows did not survive, a second counter: 1 row without valid coordinates — the message's plural form reads 2 rows without valid coordinates. A row is counted when its latitude or longitude is not a number after parsing or is outside ±90 and ±180. The coordinate columns leave the attribute table; every other column stays.

The decimal comma: -23,55

Symptom. Excel pt-BR or es, "Save as CSV": -23,55 with ; between columns. Google Sheets, LibreOffice Calc and a workbook dropped in directly (the .xlsx case below) write commas between columns and quote the decimal-comma cells: 1,"-23,55","-46,63",São Paulo. A tab export gives -23,55 with tabs.

What the viewer shows. All three read correctly: 3 points, no counter, first point on São Paulo at -46.63, -23.55 in the exported GeoJSON (fixtures 1, 2, 1b). The rule is applied to the cell, not the file: the viewer reads -23,55 as −23.55 whatever the delimiter — ;, tab, or a quoted cell in a comma file, which is what Google Sheets, LibreOffice Calc and a workbook dropped in directly (the .xlsx case below) write for a pt-BR or es sheet. The one thing it cannot rescue is an unquoted comma-decimal in a comma file, because 1,-23,55,-46,63 is five cells before any parser sees it — and no spreadsheet writes that. The boundary is 1,234: it reads as 1.234, because a value with a thousands separator cannot be a degree coordinate. Attributes are left alone — a preço of 12,50 stays the text 12,50.

Fix in the spreadsheet, for tools that need a dot. Excel: Options → Advanced, untick "Use system separators", decimal separator .. Calc: Options → Language Settings → Locale setting → English (USA). Sheets: File → Settings → Locale → United States. Then export.

Fix on the command line. On GDAL 3.13.3, naming the columns is enough: ogr2ogr -f GeoJSON out.geojson decimal-comma-semicolon.csv -oo X_POSSIBLE_NAMES=lon -oo Y_POSSIBLE_NAMES=lat writes "coordinates":[-46.63,-23.55], and the tab and quoted-comma files give the same pair. What -oo AUTODETECT_TYPE=YES does for the other columns depends on the delimiter: in a ; file lat becomes Real -23.55; in a quoted comma or tab file the same cell stays String -23,55. For attributes, and for any tool other than GDAL, the locale-aware export is the honest fix.

A title row above the header

Symptom. A title line — Levantamento de campo, setembro 2026, equipe A — above the real header id;lat;lon;nome, rows ;-separated with decimal commas.

What the viewer shows. The title has two commas and no semicolon, so the comma wins and the title becomes the header: the picker pre-selects Levantamento de campo as latitude and setembro 2026 as longitude. The real header line has no comma, so it becomes one data row whose "latitude" is id;lat;lon;nome — not a number — and is counted: 3 points · 1 row without valid coordinates. Each data row splits on its commas into 1;-23, 55;-46 and 63;São Paulo; the parser reads the leading digits, and the three points draw at [55, 1], [79, 2] and [12, 3] — the Indian Ocean, north of the Seychelles (fixture 3). A silent wrong plot, with a counter that blames the one row that was right; the surviving attribute is equipe A, holding 63;São Paulo. This is not "everything lands in one column" — that needs a title with no comma; a title with commas shreds every row into as many cells as it has commas, plus one.

Fix in the spreadsheet. Delete the title row so the header is row 1 — Excel, Calc and Sheets alike; a units row under the header is only counted as one row without valid coordinates — it is the title row above the header that shreds everything.

Fix on the command line. GDAL's CSV driver has no "skip N lines" option (HEADERS takes only YES, NO or AUTO), so drop the line, then name the separator: tail -n +2 title-row.csv > title-row-noheader.csv, then ogr2ogr -f GeoJSON title-row-fixed.geojson title-row-noheader.csv -oo SEPARATOR=SEMICOLON -oo X_POSSIBLE_NAMES=lon -oo Y_POSSIBLE_NAMES=lat -oo AUTODETECT_TYPE=YES writes "coordinates":[-46.63,-23.55], decimal commas included. Read as-is, ogrinfo makes the viewer's mistake and prints Levantamento de campo (String) = 1;-23.

Latitude and longitude swapped

Symptom. Columns named lat and lon, values the other way round: 1,-46.63,-23.55,São Paulo — a lon,lat export pasted under a lat,lon header; GeoJSON, WKT and most APIs write longitude first.

What the viewer shows. Two outcomes. If both values are within ±90 — every Brazilian point, since the country spans roughly −74° to −29° of longitude — the map draws in the wrong ocean and says nothing: 3 points, no banner, no counter, first point [-23.55, -46.63], in the South Atlantic about 3,290 km south-east of São Paulo (fixture 4). If a longitude is beyond ±90 — Tokyo's 139.69 in the lat column — that row is dropped and counted: 2 points · 1 row without valid coordinates, while the two Brazilian rows in the same file still draw in the South Atlantic (fixture 5). The counter says something was wrong; it cannot say the surviving points are wrong too.

The fix is the picker. Nothing detects a swap — latitude −46.63 is a perfectly good point. Set Latitude column to lon and Longitude column to lat, click Open on map, and the same file draws on São Paulo, first point [-46.63, -23.55] (measured on fixture 4). Or swap the two header names in the sheet.

Degrees, minutes and a hemisphere letter in one cell

Symptom. 23°33'S in the latitude cell, 46°38'W in the longitude cell — a GPS export or a field notebook typed up.

What the viewer shows. 3 points, no message, first point [46, 23]: 23° north, 46° east, central Saudi Arabia (fixture 6). The parser reads the leading digits and stops at the degree sign; the minutes are lost and so is the hemisphere letter that carried the minus sign. The robustness change did not touch this: a cell with a degree sign is the reader's job.

Fix in the spreadsheet. One formula converts degrees-minutes-hemisphere to decimal degrees and negates both S and W. With the text in A1, in Calc or Sheets on a pt-BR or es locale (; between arguments; Excel in English wants ,):

=IF(OR(RIGHT(A1;1)="S";RIGHT(A1;1)="W");-1;1)*(VALUE(LEFT(A1;FIND("°";A1)-1))+VALUE(MID(A1;FIND("°";A1)+1;FIND("'";A1)-FIND("°";A1)-1))/60)

Run through LibreOffice: 23°33'S → -23.55, 15°47'S → -15.78333, 46°38'W → -46.63333. Put the result in a column named lat or lon and the viewer picks it up by name.

For a handful of points, the coordinate-converter has a DMS field with one catch we measured: it requires seconds. 23°33'S 46°38'W gives DMS format not recognized.; 23°33'00"S 46°38'00"W gives -23.55000000 and -46.63333333. Add the 00".

On the command line, nothing. GDAL has no DMS parser: ogrinfo -oo AUTODETECT_TYPE=YES types the cell String, and ogr2ogr with -oo X_POSSIBLE_NAMES=lon -oo Y_POSSIBLE_NAMES=lat exits 0, prints Warning 1: Invalid value type found in record 1 for field lat, and writes "geometry":null for every row — a file that "converted" with no points in it.

UTM eastings and northings pasted as latitude and longitude

Symptom. 333624,7394648 — metres in SIRGAS 2000 / UTM zone 23S, columns easting and northing.

What the viewer shows. Neither name is an alias and neither contains lat or lon, so the picker falls back to the first two columns and pre-selects id and easting. Confirming that, or choosing easting and northing by hand, gives the same result: every row fails the range check, zero points survive, and the viewer shows No valid coordinate columns found. Check that the selected columns contain numeric latitude and longitude values. (fixture 7). The message is about the values, not the names; there is no CRS input.

Fix on the command line. Name the columns, declare the source CRS, ask for WGS 84: ogr2ogr -f GeoJSON utm-fixed.geojson utm.csv -oo X_POSSIBLE_NAMES=easting -oo Y_POSSIBLE_NAMES=northing -s_srs EPSG:31983 -t_srs EPSG:4326 puts the first point at -46.6300, -23.5500 — São Paulo, a residual of a few millionths of a degree being the metre rounding of our fixture. 31983 is SIRGAS 2000 / UTM zone 23S; read that name out of gdalsrsinfo -o wkt1 EPSG:31983, because 31982 and 31984 are the neighbouring zones (about 6° off, no error), 3198 is a valid Libyan CRS, and only 319830 fails. The EPSG cheat sheet for Brazil and Latin America has the zone codes. GDAL writes a crs member (CRS84) into that GeoJSON by default; -lco RFC7946=YES drops it and rounds to seven decimals, which is what a viewer wants.

In the spreadsheet, no. UTM to geographic is projection maths; a sheet with easting renamed to lon is the silent version of this failure.

Latin-1 from Excel: São Paulo or S�o Paulo

Symptom. Excel on Windows, "CSV (comma delimited)" — not "CSV UTF-8" — writes Windows-1252: ã is the single byte 0xE3, invalid as UTF-8. The coordinates are ASCII and never cared; the attributes break.

What the viewer shows. 3 points, the correct first point, and São Paulo in the attribute table — not S�o Paulo (fixture 8). The file is decoded as strict UTF-8 first; when that fails on a byte like 0xE3, it is decoded as Windows-1252 instead. A real UTF-8 file is unaffected.

Fix in the spreadsheet. Excel: Save As → "CSV UTF-8 (Comma delimited)". Calc: Text CSV → Edit filter settings → Character set "Unicode (UTF-8)". Sheets always writes UTF-8.

Fix on the command line. iconv -f WINDOWS-1252 -t UTF-8 latin1.csv > latin1-utf8.csv — byte 0xE3 becomes C3 A3. GDAL on the raw file shows the old damage (ogrinfo prints nome (String) = S�o Paulo), so run iconv before ogr2ogr too.

Dropping the .xlsx in directly

Symptom. You drop the workbook itself. The viewer reads .xls and .xlsx, first sheet only.

What the viewer shows. It depends on how each cell is stored, so we built a pt-BR workbook with both kinds and measured per cell (fixture 9). A numeric cell holding -23.55 with number format 0.00 — what Excel pt-BR displays as -23,55 — comes out as -23.55; the display locale is not in the file. A cell holding the text -15,79, kept as text by Excel, comes out quoted, "-15,79" — the shape of a Sheets export — and is read as −15.79. Both land where they belong: 3 points, no counter, [-46.63, -23.55] and [-47.88, -15.79].

Fix. Move the sheet with the coordinates to first position, or export it as CSV UTF-8 and use the lines above.

Test files you can use

No public host carries tiny test CSVs, so here are ours — three rows each. Copy a bullet into a text editor, one span per line, save as .csv, drop it into the csv-viewer. The bracket names the case above, in section order.

  • Decimal comma, semicolon (fixture 1) — id;lat;lon;nome · 1;-23,55;-46,63;São Paulo · 2;-15,79;-47,88;Brasília · 3;-3,12;-60,02;Manaus (case 1).
  • Decimal comma, quoted, comma-separated (fixture 1b) — id,lat,lon,nome · 1,"-23,55","-46,63",São Paulo · 2,"-15,79","-47,88",Brasília · 3,"-3,12","-60,02",Manaus (case 1).
  • Decimal comma, tab-separated (fixture 2) — fixture 1 with a Tab typed where it has ; (case 1).
  • Title row above the header (fixture 3) — Levantamento de campo, setembro 2026, equipe A · id;lat;lon;nome · 1;-23,55;-46,63;São Paulo · 2;-15,79;-47,88;Brasília · 3;-3,12;-60,02;Manaus (case 2).
  • Swapped, in range (fixture 4) — id,lat,lon,nome · 1,-46.63,-23.55,São Paulo · 2,-47.88,-15.79,Brasília · 3,-60.02,-3.12,Manaus (case 3).
  • Swapped, one row out of range (fixture 5) — id,lat,lon,nome · 1,-46.63,-23.55,São Paulo · 2,-47.88,-15.79,Brasília · 3,139.69,35.68,Tóquio (case 3).
  • Degrees and minutes (fixture 6) — id,lat,lon,nome · 1,23°33'S,46°38'W,São Paulo · 2,15°47'S,47°53'W,Brasília · 3,3°07'S,60°01'W,Manaus (case 4).
  • UTM zone 23S (fixture 7) — id,easting,northing,nome · 1,333624,7394648,São Paulo · 2,191433,8252182,Brasília · 3,687688,7465276,Rio de Janeiro (case 5; Rio, not Manaus, because Manaus is in zone 20S).
  • Latin-1 (fixture 8) — the control re-encoded with iconv -f UTF-8 -t WINDOWS-1252 all-valid.csv > latin1.csv (case 6).
  • A pt-BR-style workbook built to mimic Excel (fixture 9) — an .xlsx with the control header, row 2 numeric cells formatted 0.00, row 3 text cells -15,79 and -47,88, row 4 numeric — 3 points on Brazil (case 7).
  • Control (fixture 10) — id,lat,lon,nome · 1,-23.55,-46.63,São Paulo · 2,-15.79,-47.88,Brasília · 3,-3.12,-60.02,Manaus — 3 points on Brazil.

And one real file. INPE's Programa Queimadas publishes every active-fire detection in Brazil as a daily CSV: the file for 14 September 2026 is focos_diario_br_20260914.csv, 4,283,545 bytes, UTF-8, 16 comma-separated columns starting id,lat,lon,data_hora_gmt,satelite,municipio,estado,…, 27,447 rows. It answers 200 with Accept-Ranges: bytes and Access-Control-Allow-Origin: * — open, no account. There is no licence file beside the CSVs; INPE's data-governance note in focos/documentos/ (a sibling of focos/csv/) describes the data as available to any user via its open-data page, so credit INPE / Programa Queimadas.

lat and lon are exact alias matches and the leading spaces in the cells are trimmed: our run drew 27447 points, no skipped rows, first point [-45.2744, -5.2883] in Maranhão, 581 ms from Open on map to the layer row.

Two warnings, dated 15 September 2026: the daily directory keeps about a month of files, so the filename above rots — take the newest; and the monthly files beside it are around 99 MiB, a download rather than a drop.

For teams

Getting the points onto a map is the easy half. Someone sent you a CSV because a team has to do something at those points: a status per row, photos and a form from the field, a review, a report by Friday. Geodocs imports the same file onto a team map, keeps the columns as fields the team fills in, runs the review, and produces the report with the map in it. The viewer is where you check the file; the workspace is where it becomes work.

Related Articles

Navegação