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 see | Likely cause | Fix |
|---|---|---|
| Everything in column A with commas visible | Excel expected a different separator | Import and choose comma as the delimiter |
| Accented names show as odd pairs of characters | Encoding read as the wrong type | Import and choose UTF-8 |
| Long numbers turn into 1.23E+15 | Excel converted them to numbers | Set that column to Text on import |
| Leading zeros vanish from IDs | Same conversion | Set the column to Text on import |
How do you import the file instead of double-clicking it?
- Open a blank workbook in Excel.
- Go to the Data tab and choose From Text/CSV.
- Select the file and wait for the preview.
- Check the file origin box and pick UTF-8 if names look wrong.
- Check the delimiter box and pick comma, semicolon or tab until the preview shows tidy columns.
- 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
Useful ideas start with a clearer picture of your own spending.
Explore your reports
