Why does a GPS CSV file look wrong when you open it in Excel?

The short answer

Two separate things tend to go wrong, and they have different causes. Either the whole file lands in a single column with no split at all, or the coordinates split correctly but display as a shortened, rounded-looking number instead of the value that was actually exported. The first is a mismatch between the character CSV uses to separate values and the character Excel is set up to expect. The second is Excel's general-purpose number formatting doing what it always does to a long decimal, whether or not the number is a GPS coordinate. Neither means the underlying data is wrong.

Why the whole file sometimes dumps into one column

CSV stands for comma-separated values, and a comma is exactly what most exports use to mark the boundary between one field and the next. Excel, however, does not always treat a comma as that boundary when it opens a file directly. Which character it expects is set by the regional format Excel is configured to use, and in a lot of regions outside the US that character is a semicolon, not a comma — because in those regions the comma is already spoken for as the decimal point, so it cannot also be the thing separating columns. Open a comma-delimited file on a system set up that way and Excel dutifully looks for semicolons, does not find any, and concludes the entire line is one field. The result is every column of the export crammed into column A, still separated by commas, just never split apart.

This has nothing to do with GPS data specifically. Any comma-delimited file behaves the same way on a comma-decimal system — a spreadsheet of names and prices would land in one column just as readily as a spreadsheet of coordinates and timestamps.

Why a coordinate can look rounded off or truncated

Even once the columns are split correctly, a GPS coordinate can come out looking shorter than it went in. Opened as a plain number with no format specified, Excel applies its default General format, which sizes itself to the column width and to a cap on how many digits it displays at once. A latitude or longitude with several decimal places can end up visually clipped, or shown rounded to fewer digits than the file actually contains, purely because the cell is displaying its idea of a tidy number rather than the exact text that was in the file. Widening the column or reformatting the cell as plain text restores the full value — nothing about the underlying number was ever changed, only how many of its digits Excel chose to show.

This display shortening is a different problem from the one-column issue above, and the two often get blamed on each other. Splitting the columns correctly does not by itself fix how a coordinate is displayed, and fixing the display does not do anything about columns that never split in the first place.

How to bring a coordinate file in without either problem

The reliable fix for both issues at once is to import the file rather than just double-clicking it open, because importing lets you tell Excel exactly how to read it instead of letting it guess. Using the option to bring in data from a text or CSV file, rather than opening the file directly, gives a chance to set the delimiter explicitly to comma regardless of the regional default, and to mark the coordinate columns as plain text rather than General so nothing gets reformatted, rounded, or reinterpreted on the way in. Text columns display exactly what is in the file, character for character, which is the safest setting for a value like a coordinate where the exact digits matter more than how tidy the cell looks.

A quicker workaround for the column-splitting half of the problem, if the import route is not convenient, is to open the file in a plain text editor first and swap every comma delimiter for a semicolon before opening it in Excel on a comma-decimal system — though this only helps if none of the values themselves legitimately contain a comma, and it does nothing for the display-rounding issue on its own.

Things that trip people up

• Assuming the export itself is corrupted because the whole file lands in one column — this is almost always a delimiter mismatch caused by Excel's regional settings, not damage to the file.
• Editing and resaving a file that looks fine in a plain text editor, on the assumption the problem must be in the data — a text editor makes no assumptions about delimiters or number formatting, which is exactly why it shows the raw file correctly while Excel does not.
• Trusting a coordinate as soon as it looks like a normal, tidy number in a spreadsheet cell — tidy is often exactly what General formatting has quietly done to it, and it is worth checking the cell's actual contents in the formula bar before relying on the displayed digits.
• Fixing the column split and assuming the rounded-looking numbers are now correct too — the two problems have separate causes and need separate fixes.

Where TrailCam fits in

TrailCam can export a route as CSV as part of the optional TrailCam Pro upgrade, alongside GPX 1.1 export for uploading a track wherever GPX is read. Neither export changes shape depending on what eventually opens it — a spreadsheet program's own regional settings and number formatting are what decide how the values look once they land there, which is exactly why the same file can look perfectly fine in one program and oddly split or rounded in another. TrailCam itself is free to download.