Fix CSV for Excel: Delimiter, Leading Zeros, Dates, Encoding

How to use the Fix CSV for Excel: Delimiter, Leading Zeros, Dates, Encoding

1. Open the file or paste the textOpen the CSV file, or paste its contents. A file is better, because its encoding can be detected from the raw bytes. The page finds the separator, the header row, and the number of rows and columns.
2. Read the problemsThe Problems tab lists what is wrong in the file, with the line numbers. Each fixable problem has a checkbox, and the safe fixes are already ticked. Fixes that guess, such as joining extra cells, are off until you tick them.
3. Check what Excel would changeThe Excel tab lists the columns where Excel would alter values and gives examples from your own data. Columns that would lose information are marked to be kept as text. Untick a column if you want Excel to treat it as numbers.
4. Look at the previewThe Preview tab shows the repaired rows. Cells that Excel would change are highlighted. If the columns look wrong, choose another separator under Settings.
5. Save the resultChoose an Excel file to keep the protected columns as text, a CSV file for other programs, or JSON. For a CSV the page can add the byte order mark so that Excel reads accents correctly.

Why a CSV file breaks in Excel

CSV stands for comma-separated values, and it is only text. Nothing in the file says that 00501 is a ZIP code and not the number 501, or that 3-4 is a part number and not the third of April. When Excel opens a CSV file it reads each cell and turns anything that looks like a number, a date, or a formula into one. That is convenient for a table of sales figures and harmful for identifiers.

Microsoft documents the most serious limit: Excel keeps 15 digits of precision for a number. A number of 16 digits or more has everything after the 15th digit replaced by zeros, and no formatting brings the digits back. Once a file has been opened and saved by Excel, the original values are gone. That is why the fix has to happen before the file is opened, or the file has to be handed to Excel in a form that carries the instruction to keep text as text.

What the CSV format says, and what it does not

RFC 4180 is the closest thing to a standard. It says each record is on its own line, an optional header line comes first, and every line should have the same number of fields. A field may be enclosed in double quotes. A field that contains a line break, a double quote, or the separator has to be enclosed in double quotes, and a double quote inside such a field is written as two double quotes. Spaces are part of the field. The RFC describes the comma only and is not a binding standard, which is why so many variants exist.

In practice the separator can be a semicolon, a tab, or a pipe. Programs in many European countries write semicolons, because the comma is their decimal mark, and Excel decides which separator to expect from the list separator in the regional settings of the computer. A file with the other separator opens in one column. The page tries the four common separators and picks the one that splits the file into the same number of cells on most rows, so a semicolon file with decimal commas is not mistaken for a comma file.

Quotes and line breaks: the damage is rarely local

A quote that is opened and never closed is the most damaging mistake. After it, a reader treats everything as part of one cell until it finds another quote. A single stray quote, such as an inch mark typed as 5" pipe at the start of a cell, can pull hundreds of rows into one enormous cell, and the rest of the file shifts out of line. The page counts the lines that were swallowed and names the line where the quote was opened.

The repair looks only at rows that run over several lines and do not fit the column count that the rest of the file has. For those rows it ends the row at every line break instead, and keeps the repair only if the lines then fit the columns better than before. A legitimate cell with a line break inside quotes is left alone, because it fits the columns. Text right after a closing quote, and quotes in the middle of an unquoted cell, are reported, and saving the file again writes them correctly.

What Excel changes, and the evidence

The Excel tab names each change it expects and quotes examples from your file. It is based on documented behavior and on well-known cases, and it flags patterns, so the real result can depend on the version and the regional settings.

• Leading zeros: 00501 becomes 501. ZIP codes, product codes, and account numbers are the usual victims.
• Digits past the 15th: a number of 16 or more digits is rounded to 15 significant digits, as Microsoft's specifications say.
• Scientific notation: a number of 12 to 15 digits is kept but is usually displayed as something like 1.23457E+11, and text such as 1E5 is read as the number 100000.
• Dates: values such as 1-2 and 3/4, or SEPT2 and MARCH1, can be read as dates. A 2016 study found gene names damaged this way in about a fifth of the genetics papers it checked, and the committee that names human genes later renamed genes such as SEPT1 to SEPTIN1 and MARCH1 to MARCHF1 for that reason.
• Formulas: a cell that starts with =, +, -, or @ can run as a formula. OWASP calls this CSV injection and says there is no single sanitizing method that is safe for every spreadsheet program and every later use of the file.
• ID at the start of the file: Excel takes a text file that begins with the capital letters ID for an old SYLK file and reports that it is not valid. Writing the first cell in quotes avoids it.
• Limits: a worksheet holds 1,048,576 rows and 16,384 columns, and a cell holds 32,767 characters, according to Microsoft.

Keeping the values: an Excel file, a CSV file, or the import wizard

A CSV file cannot say that a column is text. So there are three ways to keep the values safe. The first is to open the file through Data, then From Text/CSV, in Excel, where a preview appears and a column can be set to text before the data is loaded, and not to double-click it. The second is to save the result here as an Excel file, which stores the columns you protect as text, so the zeros and the long IDs are there when Excel opens it. The third is to leave Excel out of it and use the CSV in a program that reads it as text.

The Excel file written here has one sheet with a bold header row that stays in view. Protected columns are stored as text with the text format, and numbers in the other columns are stored as numbers. Everything else, including dates, is kept as the text it was. It is a plain workbook: it holds no formulas, charts, or formatting beyond that.

Encoding and the byte order mark

If accents show as strange characters, the file is probably UTF-8 and Excel read it in an older encoding. A CSV saved as UTF-8 with a byte order mark, the three bytes at the start of the file, tells Excel on Windows to read it as UTF-8. The page adds the mark when you save a CSV, unless you turn it off. When a file is not UTF-8, it detects the encoding from the raw bytes and offers the other likely readings. If text inside the file is already damaged, such as é written as é, use the Fix Garbled Text tool first.

What each fix does, and what it does not

The fixes are small on purpose, and each one is listed with the number of changes it made. Nothing is changed unless a fix is ticked, and the original file is never altered, because the page only reads it.

• Fill short rows with empty cells: keeps the columns lined up. It cannot know which cell is missing, so check the lines listed.
• Join the extra cells of long rows into the last column: repairs an unquoted separator inside a text cell, such as a comma in an address. It is off by default, because it is only right when the last column holds free text.
• Remove blank rows and rows that are exact copies of an earlier row.
• Trim spaces around cells, give empty and repeated column names a unique name, and remove columns that are completely empty.
• Remove control characters, such as the null character, which are not allowed in an Excel file.
• Treat a broken quote as plain text and keep each line as its own row, for the rows described above.

Limits and accuracy

• The Excel behavior is taken from Microsoft's documentation and from widely reported cases. The page flags values that match those patterns, and the real result depends on your version of Excel and your regional settings. It was not tested by opening files in Excel itself.
• The Excel file was checked by reading it back with another library and by inspecting its structure, not by opening it in Microsoft Excel.
• Other programs behave differently. Google Sheets and LibreOffice make their own guesses about numbers and dates.
• A quote problem can be repaired only when the lines still make sense one by one. If the damage is in the original export, check the result against the source.
• Only a single-character separator is supported. Fixed-width files and files with several separators in one are not handled.
• Characters that were already replaced by � in the file are gone and cannot be restored.
• A file can be up to 20 MB. The preview shows the first 1,000 rows, and the saved file contains all of them.

Frequently asked questions