Why Excel Breaks CSV Files (and How to Stop It)

CSV is plain text. Excel is not.

A CSV file is plain text. That is all it is. Rows sit on lines. A comma splits each row into cells.

The file holds no type clues. It does not say this cell is a date. It does not say that cell is a code. It does not say a third cell is text.

The Library of Congress notes this fact. A CSV has no type data in it. So each app must guess. Each app reads the text and makes a call.

Excel makes bold guesses. It tries to help you fast. It sees a shape and picks a type. A date shape turns into a date. A math shape turns into a sum form.

That help can hurt a clean file. A code is not a number to add up. A gene name is not a date. A ZIP code is not a math value. Yet Excel may treat each one as if it were.

Excel is built for sheets, not plain text. A sheet file stores types with the data. It stores fonts and widths too. A CSV stores none of that. When Excel opens a CSV, it must fill the gaps.

It fills them with its own rules. Those rules come from your locale too. A US setup reads dates one way. A UK setup reads them a flipped way. The same file can shift on two screens.

The National Archives makes a like point. Excel may need you to set the split marks by hand. If you do not, columns can merge. One wide cell can hold a whole row. That is not a file flaw. It is a guess that missed.

Once you see this, the fix is clear. Do not let Excel guess in the dark. Tell it what each column is. Or skip Excel for the clean step. Use a tool made for plain text files.

Keep a raw copy at all times. Name it and leave it alone. Work on a copy, not the source. If a guess wrecks the copy, the source stays safe.

Dates that change on their own

Dates are the top trap. You type a plain date. Excel sees it and stores its own form. It may show the date in a new shape. It may swap the day and month.

The swap trap is sly. It hits dates like 03/04/2025. In the US, that means March 4. In much of the world, it means 3 April. Excel uses the locale on your screen. Open the file on a new screen, and the date can flip.

No warn pops up when it flips. The cell still looks like a date. The day count just moved by weeks. A bill date can shift. A ship date can shift. A birth date can shift too.

Some text turns into dates by force. Type MAR1 for a code or a gene. Excel may show March 1. Type 1-2 for a range or a code. Excel may show a date in that month.

Once Excel stores it as a date, text is lost. You can change how it looks. You can not get the raw text back with ease. The cell now holds a date value, not your code.

This is why pros fix dates with care. If your file has mixed date shapes, read how to fix inconsistent dates in one pass. Pick one date shape. Use it for each row. Test the import with a few rows first.

The Centre College guide makes this point too. Dates need the right format in clean data. If the format is loose, tools will guess. Loose dates are a top cause of bad imports.

A safe rule is to use year first. Write dates as 2025-03-04. That shape is clear in most lands. It sorts well too, from old to new. Still, check what your end tool wants. Clean for that tool first.

Do not fix dates by eye in Excel. You may fix the look, not the value. The saved file may still hold the wrong day. Check the raw text in a plain text app. What you see there is what you send.

If a date column must stay text, mark it so. Tell the import tool it is text. We show that step in part five. It takes one click and saves a whole column.

Numbers that turn into dates or lose digits

Codes look like numbers to Excel. ZIP codes, SKUs, and IDs all use digits. Excel treats them as math at first sight. That is where digits get lost.

Lead zeros go first. Type 00501 for a ZIP code. Excel stores it as the number 501. The two zeros are gone. Save the file, and they stay gone. The ZIP is now wrong.

This trap has its own deep guide. Read why leading zeros disappear if ZIPs fill your file. The short fix is the same. Force the column to text before Excel can guess.

Long numbers break in a new way. Excel shows them in short form. A long ID like 123456789012 may show as 1.23E+11. That short form can get saved to the file. Then your long ID is lost for good.

Phone fields hit a like trap. Excel may drop a lead plus sign. It may add gaps or drop zeros. It may turn a long phone into short form too. Clean phones as text, not as math.

Some codes turn into dates. We saw MAR1 and 1-2 above. Any code with a month name can flip. Any code with bars or slashes can flip too. SEPT1, OCT2, and DEC1 are at risk. So are codes like 11/12 or 3-4.

Look at each row in that table. None of them had a flaw at first. Each one was fine as plain text. The flaw came from the guess. The guess came from how Excel opens files.

You can not turn off all guesses at once. There is no one switch for that. You have to guide the open step. Or you keep codes out of Excel. Both paths work if you stick to them.

Do not add spaces to fool Excel. A space at the start may break the next tool. Do not add a quote mark by hand. That mark can end up in your data. Use a real text flag in the import step. It is clean and it sticks.

The save step that rewrites your file

Most harm shows up at save time. You fix a cell or two by hand. You hit save out of habit. Excel writes the whole file back out. It writes what it holds, not what you had.

What it holds may have changed. Dates now hold date values. Codes now hold plain numbers. Long IDs now hold short forms. The save locks all that in. The raw text is gone from that file.

The split marks can shift too. Your source may use commas. Excel may save with a new mark based on locale. Some locales use a semi mark in place of a comma. The next tool then sees one wide column.

Quote marks can shift as well. Text with a comma needs quotes in a CSV. Excel may add quotes in new spots. It may drop quotes it deems moot. Most tools cope, but some strict tools balk.

The code page can shift too. Names with marks like é may break. Excel may save in a form the next tool reads wrong. You then see odd signs in place of letters. That harm is hard to undo.

This is why a quick peek is a risk. You open the file just to look. You close it and hit save by reflex. That one click can wreck a good file. Train your hand to hit Do Not Save. Or open a copy, not the file you will send.

If harm has been done, go back to source. Do not try to patch a wrecked file by eye. Get a fresh export from the source tool. Clean that fresh file the safe way. It is faster and far more sound.

How to open a CSV in Excel the safe way

You can still use Excel with care. The trick is to guide the open step. Do not double click the file. That path lets Excel guess with no input from you.

Use the import flow in its place. Open Excel to a blank sheet first. Then use the Data tab to pull in text. That flow asks you key things. It asks what splits the columns. It asks what type each column is.

Step 1: Start from a blank sheet

Open Excel with no file loaded. Go to the Data tab at the top. Pick the from text or CSV choice. Browse to your file and pick it.

Step 2: Set the split mark

Check the split mark on screen. Most CSV files use a comma. Some use tabs or semi marks. The screen shows a peek of your rows. Columns should line up in the peek. If they do not, change the mark till they do.

Step 3: Set code columns to text

Click each code column in the peek. Set its type to Text. Do this for ZIPs, SKUs, and IDs. Do this for phone fields too. Do this for any code that starts with zero.

Step 4: Set date columns with care

Click each date column in the peek. Set the date shape your file uses. Match the day, month, and year order. If your file uses year first, say so. Do not let the tool assume.

Step 5: Check before you save

Scan the sheet once it loads. Check ZIPs still have lead zeros. Check long IDs still show in full. Check dates match the source file. If a check fails, close with no save. Start the import once more.

Make a small test file for practice. Put a ZIP with lead zeros in it. Put a code like MAR1 in it. Put a long ID in it. Open it the safe way. Then open it with a double click. See the gap with your own eyes. That one test will change your habits.

Share the safe steps with your team. Write them on one page. Post the page where folks can find it. Most file harm comes from habit, not malice. New habits fix most of it.

When to skip Excel and clean the file first

Some files should not touch Excel at all. Big files with lots of codes are one case. Files bound for a strict import are a case too. Files with mixed dates are a third case.

If the file is just for import, skip the sheet step. A sheet adds risk and no gain. Clean the plain text as plain text. Then send it straight to the end tool. Test a few rows there and check them.

RowSift is built for that path. It runs in your browser. You can clean the file in your browser with no sheet step. It flags rows that look wrong. It then exports a clean CSV for import.

Use RowSift to strip stray spaces. Use it to fix mixed date shapes. Use it to find dupe rows and blank cells. Fix each flaw in the text, not in a sheet. The file keeps its raw form till you export.

This path helps with lead zeros too. A text tool does not eat zeros. It keeps codes as you wrote them. It keeps long IDs in full. It keeps phone fields as digits and a plus. No guess step stands in the way.

It helps with split marks as well. Your export keeps commas as splits. Quote marks go where the rules need them. Names with marks like é stay sound. The end tool gets the file it expects.

That does not mean Excel is bad. Excel is great for sums and charts. It is great for a quick look at small data. Just do not use it as a CSV editor. That one job is where it does harm.

Build a flow your team can repeat. Raw file in a safe spot. Clean in a text tool. Test a small import. Then import the full file. Check the counts at the end. Save the clean file with a clear name.

Follow that flow and files stop breaking. Dates stay as you wrote them. Zeros stay where they belong. IDs stay long and true. Your imports land clean, time after time.