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.

Re-tested on 23 September 2026 against the deployed tree; GDAL 3.13.3 "Iowa City" for the command-line transcripts. Where the tool changed since the first run, the case below says so.

What the viewer looks for

The first line is the header — always row 0 — and the delimiter is chosen by counting ,, ; and tabs across the first three non-blank lines and summing them; the highest total wins, a tie goes to the comma. Quoted cells are honoured, and so is a line break inside one. There is a delimiter override: a Delimiter dropdown at the top of the picker, pre-filled with what was detected, offering Comma (,), Semicolon (;) and Tab — change it and the header and the column list are read again from your choice. 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, the exported GeoJSON rounding to -46.63, -23.55 (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, but it no longer votes alone: across the first three lines the semicolons beat the commas 6 to 4, so ; wins and the title line — which contains no semicolon — becomes a header of exactly one column. Both dropdowns then show that whole title line, Open on map is greyed out, and the file never opens: a one-column header leaves the picker with no second column to offer as longitude, so there is no map, no counts line and no counter (fixture 3). A title with no commas at all behaves identically. Force the Delimiter dropdown to Comma (,) and the old reading comes back — Levantamento de campo pre-selected as latitude, setembro 2026 as longitude — and then the map is empty and the banner reads No valid coordinate columns found. Check that the selected columns contain numeric latitude and longitude values.. The three points that used to draw at [55, 1], [79, 2] and [12, 3] in the Indian Ocean, north of the Seychelles, are not reachable by any route we measured: a silent wrong plot became a visible refusal. What the viewer still does not do is explain itself — a form that looks filled in, a button that does nothing, and no message.

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 still makes the mistake the viewer used to make, 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.

If they are all in the same wrong place, it is not a swap. An empty coordinate cell never reaches the map: it is a counted skip, like the degrees-and-minutes cell below. A cell holding a literal zero does reach it, and draws at 0, 0, a real position in the Gulf of Guinea — the point at (0, 0) tells one failure from the other.

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. Nothing. The picker pre-selects lat and lon as it should, you click Open on map, and every row is rejected: no layer is created, no counts line appears, and the viewer shows No valid coordinate columns found. Check that the selected columns contain numeric latitude and longitude values. (fixture 6). The coordinate cell is read as a whole numeric literal now, so a cell that starts with digits and then carries a degree sign is simply not a number — where the old parser read the leading digits, threw away the minutes and the hemisphere letter that carried the minus sign, and drew three points in central Saudi Arabia with no message at all. That is the change worth knowing about: the file that used to open wrong now refuses to open. Put one good row in the same file and the counter appears instead — 2 points · 1 row without valid coordinates — because a rejected row is only counted when a layer survives to carry the counter; when every row has a degree sign in it there is no layer, and the banner is what you get.

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)

The formula gives: 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 takes the cell as it is written: 23°33'S 46°38'W was refused for want of seconds and is accepted now, giving -23.55000000 and -46.63333333 — the same pair as 23°33'00"S 46°38'00"W. That change unlocked every two-group form, decimal minutes included: 23°33.5'S 46°38.2'W was refused before it and is read now. Hemisphere letters are still required on both sides — 23°32'56.10" 46°38'20.30" gives DMS format not recognized. — and a lone 23°33'S is refused too; space-separated 23 32 56.1 S 46 38 20.3 W and lowercase hemisphere letters both work. Read the target before you trust the numbers: Target projection (CRS) starts on SIRGAS 2000 / UTM 23S (EPSG:31983), and on that target the same string returns 333283.881280 and 7394643.649020 — metres, not degrees. For degrees, leave the source on WGS 84 (EPSG:4326) and set the target to SIRGAS 2000 (EPSG:4674).

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. This warning will no longer be emitted, and writes "geometry":null for every row — a file that "converted" with no points in it.

The forms that parse, in one place. Which DMS spellings convert and which are refused is a longer matrix than this section needs: degrees, minutes and seconds to decimal degrees works through the whole set, with the spreadsheet formula and the converter side by side.

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 27,447 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 23 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.