Getting started

Why an expense CSV opens garbled in Excel, and how to fix it

Expense CSV showing everything in one column or with strange characters in Excel? Here is how to fix delimiters, encoding, dates and amounts in the US.

Editorial illustration for Why an expense CSV opens garbled in Excel, and how to fix it

An expense CSV looks garbled in Excel for three usual reasons: Excel guessed the wrong delimiter, it guessed the wrong text encoding, or it quietly converted dates and amounts into something else. You can fix all three without editing the file by importing it through Excel's Data tab instead of double-clicking it.

What does garbled actually look like?

Match your symptom first, because each one has a different cause.

What you seeLikely causeWhere to fix it
Everything crammed into column AComma or semicolon delimiter not recognisedChoose the delimiter in the import dialog
Strange characters where accented letters or symbols should beFile is UTF-8 but Excel read it as another encodingSet File Origin to UTF-8
Dates swapped, such as 03/04 becoming 4 March instead of 3 AprilMonth-first versus day-first mismatchImport the date column as Text, then convert deliberately
Amounts left-aligned and not summingNumbers arrived as text, often with a thousands separator or currency signClean with Text to Columns or a helper column
Leading zeros gone from reference numbersExcel treated them as numbersImport that column as Text

How do I import the file properly instead of double-clicking?

  1. Save the CSV somewhere you can find it and do not open it by double-click.
  2. Open a blank workbook, go to the Data tab and choose From Text/CSV.
  3. Pick the file. A preview appears. If the preview shows one long column, change the Delimiter box to Comma, Semicolon or Tab until columns line up.
  4. Set File Origin to 65001: Unicode (UTF-8) if you see odd characters.
  5. Click Transform Data if you want to set each column type yourself, otherwise Load.
  6. Check the first and last rows against the original file before you trust the totals.

In Google Sheets the equivalent is File, then Import, then Upload. The import dialog lets you choose the separator type and whether to convert text to numbers and dates, so untick that conversion if dates keep flipping.

Why do commas inside the data break my columns?

A merchant name such as Smith, Jones & Co or an amount such as 1,234.50 contains a comma. Well-formed CSV files wrap those fields in double quotes so the comma is not treated as a separator. If your columns shift to the right on only some rows, the export probably lacks quotes on those fields. Open the file in a plain text editor and look at a bad row: if you see a stray comma outside quotes, the fix is to export again with text qualifiers on, or to use a different delimiter.

Some computers set Excel's list separator to a semicolon, usually where the decimal mark is a comma. If your computer's region settings differ from the file's, the same symptom appears. Check the region setting before you assume the file is broken.

A worked example with made-up numbers

Say a file has the row 2026-03-04,Grocery run,"1,234.50",Card. Opened by double-click on a machine expecting semicolons, the whole row lands in one cell. Imported with the Comma delimiter and quote handling, you get four columns: date, description, amount and method. The amount arrives as 1234.50 only if Excel recognises the quoted field as a number. If it shows as text, add a helper column with =VALUE(SUBSTITUTE(C2,",","")) and fill it down.

To confirm nothing was lost, compare the count of rows and one total. Say the source shows 48 rows and a total of $6,420.75. In Excel, =COUNTA($A$2:$A$500) should return 48 and =SUM($D$2:$D$500) should match $6,420.75 to the cent. If they differ, some amounts are still text.

How do I stop dates from flipping?

The safest route is to import the date column as Text, then convert with a formula you control. For an ISO date such as 2026-03-04 stored as text, =DATE(LEFT(A2,4),MID(A2,6,2),RIGHT(A2,2)) builds a true date no matter what your region settings say. For a month-first text such as 03/04/2026, =DATE(RIGHT(A2,4),LEFT(A2,2),MID(A2,4,2)) does the same. Format the result as a date afterwards.

When is the export itself the problem?

If the file has uneven numbers of fields per row, mixed encodings, or notes with line breaks that are not quoted, no import setting will rescue it. Ask whoever produced the export for a cleaner one, or use an app whose CSV download is built for spreadsheets. SpendixAI's CSV download from its report builder is meant for this, though it is built around India and INR, so check that it suits you before relying on it from elsewhere.

Does the operating system change anything?

Slightly. On Windows, double-clicking a CSV hands it to Excel with your regional settings. On a Mac, Excel behaves similarly but may guess a different encoding for files made elsewhere. If the same file looks right on one machine and wrong on another, the difference is almost always the regional list separator or the encoding guess, not the file. Opening through the import dialog removes the guesswork on both.

Quick checklist before you analyse the data

  • Delimiter chosen so every row has the same number of columns.
  • Encoding set to UTF-8 if symbols look wrong.
  • Dates converted deliberately, not by guess.
  • Amount column sums to the same total as the source.
  • Original CSV kept separate from your working file.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports