When Excel Eats Your CSV

Stefan Fischerländer

Stefan Fischerländer

— 2 min read

This is already issue #003 ofThe Missing Header — formerly known as the Tablecruncher Newsletter*

Opening a CSV file in Excel feels easy. You double-click, it loads, and voilà — your data. Or so you think.

But what actually happens under the hood can quietly corrupt your data without you ever noticing. Here’s why:

1. Excel guesses your separator

Excel doesn’t really understand CSVs. It assumes the correct column separator based on your system’s regional settings. On a German system, for example, Excel expects a semicolon (;) rather than a comma (,). If the separator doesn’t match, Excel loads the entire row into a single column.

That’s why so many people end up using Data > Text to Columns just to get their data back into shape.

2. Import it properly — don’t just open it

Don’t just open your CSV file — import it properly using Excel’s modern import wizard.

  • Windows: On the Data tab, in the Get & Transform Data group, click From Text/CSV.
  • Mac: On the Data tab, click Get Data (Power Query) and choose Text/CSV.

You’ll be able to:

  • Select the correct encoding
  • Set the right delimiter
  • Preview the data before importing
  • Optionally open the Power Query editor for advanced cleanup or reshaping

Power Query is powerful — and, yes, a bit overwhelming at first — but it’s your best defense against Excel silently corrupting your CSV data.

This gives you control over how your data is interpreted — something Excel’s default open absolutely does not provide. It’s not perfect, but at least you stay in control — but even then, trust but verify.

3. Excel quietly misinterprets your numbers

The real danger begins when numeric formats collide with regional settings.

Many global tools — like Google Search Console — export CSV files in US-style number formatting (with dots as decimal separators). But if your computer is set to a different locale (like German), Excel will happily reinterpret those numbers into something completely wrong.

Here’s what happened to me — and still happens today.

I exported a CSV from Google Search Console:

page,clicks,impressions,ctr,position
URL A,58,12351,0.47%,33.57
URL B,49,1582,3.1%,17.55
URL C,33,868,3.8%,10.76

After opening it in Excel (with German regional settings), the position column looked like this:

33.57  
17.55  
Okt 76

That last line? The value 10.76 was misinterpreted as a date: October 1976. No warning. No clue that something broke. Just silent, irreversible corruption.

And when you save the file again — poof — the original numbers are gone.
BTW: after this happened to me the third time, I decided to write Tablecruncher ;-)

🧠 What we learn

Opening a CSV in Excel is a one-way import. Excel doesn’t treat your file as structured data — it treats it as input to interpreted based on its own assumptions. Sometimes it gets it right. Often, it doesn’t.

And that’s not the worst part.

If the failure is obvious (e.g. everything ends up in column A), you’ll probably catch it. But if Excel silently mangles row 4237, good luck finding out — until your client calls and asks why their invoice totals are off.

🧮 The Missing Number

1,048,576 — the maximum number of rows in an Excel worksheet.

What's your favorite CSV-related Excel fail?
Just hit reply — I’d love to hear it.

Thanks for reading,
Stefan

* I’ve rebranded the newsletter to avoid confusion with my CSV app. It’s now The Missing Header. Same author, same focus — all about the weird and wonderful world of tabular data.

Read more