ContextCleaner: see every entry in the Windows 11 right-click menu, and which app added it — on the Microsoft Store.
Guide CSVPrune

Cleaning CSV Files: Fixing Encoding, Delimiters, Leading Zeros and Duplicates Before Import

Clean a CSV before import: fix garbled text and one-column files, keep leading zeros, remove duplicates, open huge files and convert to XLSX or JSON.

Published October 28, 2026 12 min read

Most CSV problems come from three things: the wrong encoding (accented letters turn into symbols like é), the wrong delimiter (everything lands in one column), and Excel guessing data types (leading zeros vanish and long numbers turn into 1.23E+15). To avoid all three, do not double-click the file. In Excel, use Data, Get Data, From Text/CSV, choose the encoding (usually UTF-8) and the delimiter, and set ID, ZIP and phone columns to Text before loading. Then trim spaces, standardise values and remove duplicate rows before you import the file anywhere else.

This guide covers why CSVs open badly, how to tell encodings apart, how to keep leading zeros, how to remove duplicates, how to open files too big for Excel, how to convert CSV to XLSX or JSON, how to repeat a cleanup every week, and how the tools compare. Each section links to a deeper guide on this site.

Why does my CSV open as one column or garbled text?

A CSV is plain text with a simple rule: one row per line, values separated by a delimiter. But the format leaves a lot unsaid, which is where problems start.

  • One column: the file uses a delimiter your Excel does not expect. Excel uses your Windows list separator, which is a comma in some regions and a semicolon in many others. A semicolon file opened on a comma system lands in one column, and the reverse.
  • Garbled characters: the file was saved in one encoding and opened in another. “Café” showing as “Café” is UTF-8 read as a Western European encoding.
  • Broken rows: a value contains a comma, a quote or a line break and was not quoted correctly, so one record spills across several rows.

The CSV rules for quoting are set out in RFC 4180: values containing commas, quotes or line breaks go in double quotes, and a quote inside a value is doubled. RFC 4180 explained shows examples of each.

UTF-8, UTF-16 and Latin-1: which encoding is it?

An encoding is the rule for turning characters into bytes. CSV files do not say which one they use, so programs guess.

EncodingWhere you meet itSigns
UTF-8Most modern exports, web apps, databasesHandles every language; may start with a byte order mark (BOM)
UTF-8 with BOMExcel’s “CSV UTF-8” save optionOpens correctly in Excel; a few importers show a stray character at the start
UTF-16Some Windows and Excel “Unicode text” exportsOften tab-separated; looks spaced out or full of nulls in simple editors
Latin-1 or Windows-1252Older systems, Excel’s plain “CSV (Comma delimited)“Fine for English and Western European text; other scripts are lost

To check a file, open it in Notepad: the status bar shows the encoding it detected, and Save As lets you choose another. For the Excel side, CSV UTF-8 vs CSV (comma delimited) in Excel explains which save option to pick and why.

Most importers today expect UTF-8. If yours garbles names, re-save the file as UTF-8 and try again.

Why does Excel drop leading zeros and change long numbers?

When you double-click a CSV, Excel decides each column’s type for you. A column of ZIP codes like 02134 looks like numbers, so Excel stores 2134. A 16-digit card or account number looks like a number too, but Excel keeps only 15 significant digits, so the last digits become zeros, and it displays it as 1.23457E+15.

To keep the values exactly as they are:

  1. Open a blank workbook instead of double-clicking the file.
  2. Go to Data, Get Data, From Text/CSV and select the file.
  3. Check the preview. Set File Origin to the right encoding (65001: Unicode UTF-8 for most files) and the delimiter to match.
  4. Select Transform Data to open Power Query.
  5. Set ID, ZIP, phone and account columns to Text. Right-click the column header, choose Change Type, then Text. Replace the existing step if asked.
  6. Close and Load. The values arrive exactly as written in the file.

Once Excel has dropped a zero and you save, the zero is gone from the file. Always keep the original. Excel removes leading zeros from CSV: why and how to fix covers more methods.

Dates that come out wrong

Dates have the same problem. Excel reads them using your Windows regional settings, so 03/04/2026 becomes 3 April on one PC and 4 March on another, and values that do not fit your format, such as 25/12/2026 on a US system, stay as text. A column can end up half converted and half not, which is hard to spot. To avoid it, import date columns as Text, or set their type in Power Query with Change Type, Using Locale, and pick the locale the file was written in. For files you produce yourself, the ISO format 2026-12-25 is read the same way everywhere.

How do I remove duplicate rows, including near-duplicates?

Exact duplicates are easy. In Excel, select the data and use Data, Remove Duplicates, choosing which columns define a duplicate. In Power Query, use Remove Rows, Remove Duplicates.

Near-duplicates are harder and more common in contact and product lists: “Jane Smith” and “jane smith ”, “ACME Inc.” and “Acme Inc”, or an email with different capitals. Exact matching misses them. Clean first, then dedupe:

  • Trim leading and trailing spaces.
  • Standardise case, especially for emails, which most systems treat as case-insensitive.
  • Normalise punctuation, company suffixes and phone formats.
  • Then remove duplicates on the cleaned columns.

Fuzzy matching goes further, comparing how similar two values are. Power Query supports fuzzy merge, and dedicated tools offer fuzzy duplicate removal. Review fuzzy matches before deleting, since similar names can belong to different people. Keep a copy of the rows you removed, so you can restore one if a match turns out to be wrong.

Import targets each have their own rules for duplicates and required fields. Clean a CSV for Mailchimp import, HubSpot import CSV requirements and Shopify product CSV import errors cover three common ones.

How do I open a CSV too big for Excel?

An Excel worksheet holds 1,048,576 rows by 16,384 columns. Open a bigger CSV and Excel loads only part of it, warning that saving will lose the rest.

Options:

  • Load it into the Data Model through Data, Get Data, From Text/CSV, then Load To and the Data Model option. You can analyse every row with PivotTables, though not view them all in a sheet.
  • Filter or split in Power Query before loading, so only the rows you need reach the sheet.
  • Use a tool without the grid limit, such as a database, a script or a desktop CSV app.
  • Never save over the original after a partial load.

“Data set too large for the Excel grid”: CSV fixes has the details.

How do I convert CSV to XLSX or JSON?

To XLSX: load the CSV correctly (as above, so types are right), then File, Save As, Excel Workbook. Saving a double-clicked CSV as XLSX keeps whatever damage Excel’s guessing already did.

To JSON: Excel does not save JSON directly. Free options include:

  • PowerShell: Import-Csv data.csv | ConvertTo-Json | Set-Content data.json turns each row into an object. Add -Encoding utf8 to both ends if the file has non-English text.
  • PowerToys Advanced Paste: according to Microsoft’s documentation, it can paste copied CSV as JSON.
  • A CSV tool that exports JSON.

Check the result: everything from a CSV arrives as text, so numbers and true or false values may need converting for the system you feed.

How do I repeat the same cleanup every week?

If you get the same export every week, doing the cleanup by hand each time is slow and error-prone. Ways to repeat it:

  • Power Query. Build the steps once (types, trims, replacements, dedupe), then point it at the new file and select Refresh. This is free and strong for repeat work in Excel.
  • A script in PowerShell or Python, if you are comfortable maintaining one.
  • Saved recipes in a CSV cleaning tool, replayed on each new file.

Whatever you use, keep the steps in the same order and check the row count before and after, so you notice if an export changes shape. Before sharing cleaned files, consider whether they contain personal data that should be masked; see mask personal data in a CSV before sharing it.

A final check before you import

A quick check catches most problems before the target system rejects the file, or worse, accepts bad data:

  • Row count matches what you expect, and the header row is present once.
  • Required columns are filled, with no blank emails or SKUs.
  • Column count is the same on every row, which shows quoting is intact.
  • IDs and ZIP codes still have their leading zeros.
  • Special characters in names display correctly.
  • Dates follow the format the importer asks for.
  • The file is saved as UTF-8 with the delimiter the importer expects.

Most import tools also offer a test or preview mode. Use it with a small sample first.

Tools compared: Excel, Power Query, online cleaners and desktop apps

ToolCostStrengthsTrade-offs
Excel (double-click)With Microsoft 365 or OfficeFamiliarGuesses types; drops zeros; grid limit
Excel with Power QueryIncluded in ExcelCorrect types, repeatable steps, fuzzy mergeLearning curve
PowerShell or PythonFreeExact control, any sizeYou write and maintain code
Online CSV cleanersOften freeNothing to installYour data is uploaded to someone else’s server
Desktop CSV apps such as CSVPruneVaries; CSVPrune is a one-time purchaseLocal, built for cleanupA separate app

Online cleaners are convenient for public data, but think twice before uploading customer lists or anything with personal details. CSV files bound for spreadsheets have one more risk: cells starting with =, +, - or @ can run as formulas when opened. CSV injection (formula injection) explained shows how to spot and neutralise them.

How CSVPrune fixes this

CSVPrune is a desktop CSV cleaner for Windows. Its Microsoft Store listing describes:

  • Big files: opens files of 100,000 rows and more, with no row limit.
  • Encoding and delimiters: fixes UTF-8, UTF-16 and Latin-1 encoding, and comma, semicolon, tab and pipe delimiters.
  • Values kept intact: leading zeros and long IDs stay as they are.
  • Before and after: toggle between the original and the cleaned data.
  • Cleanup tools: trim whitespace, normalise emails, standardise text, numbers, dates and booleans, and find and replace with regular expressions scoped to a column.
  • Duplicates: exact and fuzzy near-duplicate removal.
  • Checks and privacy: column profiling and a data-quality score, formula columns, and masking for emails, phones and IDs.
  • Repeatable: save cleanup recipes and replay them on the next file; export to CSV, JSON or XLSX.
  • Local: no cloud, telemetry or network calls; files are processed in memory, and the recent files history can be cleared.

CSVPrune is a one-time purchase on the Microsoft Store.

Get CSVPrune from the Microsoft Store

Frequently asked questions

Why does my CSV open in one column in Excel?

The file’s delimiter does not match your Windows list separator. Import it with Data, Get Data, From Text/CSV and choose the right delimiter.

How do I stop Excel removing leading zeros from a CSV?

Import the file through From Text/CSV and set those columns to Text before loading. Do not double-click the file.

What encoding should a CSV use?

UTF-8 for almost everything today. Use Excel’s “CSV UTF-8” save option, or re-save in Notepad as UTF-8.

Is it safe to use an online CSV cleaner?

For public data, usually. For files with customer or personal data, a local tool avoids uploading it.