Business

CSV Opens Garbled in Excel? Fix Squashed Columns and Odd Characters

When a CSV opens in Excel as one crowded column or with strange symbols, the cause is usually the delimiter or encoding; here is how to fix both with $ examples.

Editorial illustration for CSV Opens Garbled in Excel? Fix Squashed Columns and Odd Characters

A CSV that opens in Excel as one long column, or with strange symbols where letters should be, has almost always met a mismatch in delimiter or text encoding. Do not edit the garbled sheet by hand. Import the file again with Excel's text import and tell it explicitly how the file is built.

Why does the same CSV look fine in one program and broken in another?

A CSV is plain text. Nothing in it says which character separates the columns or which encoding was used to store letters like an e with an accent. A program has to guess, and Excel's guess depends on your computer's regional settings and on whether the file starts with a hidden marker. When the guess is wrong, you see the symptoms below.

What you seeLikely causeFix
Everything in column A with commas visibleExcel expected a different separatorImport and choose comma as the delimiter
Accented names show as odd pairs of charactersEncoding read as the wrong typeImport and choose UTF-8
Long numbers turn into 1.23E+15Excel converted them to numbersSet that column to Text on import
Leading zeros vanish from IDsSame conversionSet the column to Text on import

How do you import the file instead of double-clicking it?

  1. Open a blank workbook in Excel.
  2. Go to the Data tab and choose From Text/CSV.
  3. Select the file and wait for the preview.
  4. Check the file origin box and pick UTF-8 if names look wrong.
  5. Check the delimiter box and pick comma, semicolon or tab until the preview shows tidy columns.
  6. Choose Transform Data if any column needs to be Text, or Load if the preview looks right.

The preview is your friend. If the columns line up and a merchant such as a cafe with an accent in its name reads correctly, load it. If not, change one setting at a time.

A worked example with dollar amounts

Say a file holds three rows: Coffee 4.50, Office chair 189.00 and Reference 000123 with amount 62.25. Opened by double-click, the reference can lose its zeros and become 123. The amounts still add up, 4.50 + 189.00 + 62.25 = 255.75, so the total looks right while the reference is silently wrong. That is why a quiet fix, such as setting the reference column to Text, matters for later matching.

What if the file will not behave at all?

  • Open it in a plain text editor and look at the first line to see which separator is used
  • Check for quotation marks around descriptions that contain commas
  • Try the same import in Google Sheets and compare the preview
  • Re-export from the source, choosing CSV rather than a spreadsheet format if you have the choice
  • Ask whoever made the file whether it is UTF-8

Where an expense app fits

If you export from a tracker, a tidy file at the source saves cleaning later. SpendixAI is built around India and INR, so it suits rupee records rather than dollar ones, but it offers CSV download of reports and transactions, which keeps columns plain and predictable.

Why not just fix it by hand?

Hand-fixing works for five rows and fails for five hundred. Retyping a mangled name also creates a mismatch with the source, so later lookups, such as matching a reference against the bank, will miss. Import the file properly once, save the result as a workbook, and use that workbook from then on. If the same source produces the same problem every month, note the settings you used, such as UTF-8 and a comma delimiter, so that next month's repair is a two-minute routine.

Short checklist

  • Import through Data, From Text/CSV
  • Set origin to UTF-8 if names look wrong
  • Pick the right delimiter
  • Mark ID and reference columns as Text
  • Keep the original file untouched
SPENDIXAI JOURNAL

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

Explore your reports