Data · THE NO-PANIC PLAN
Clean an Excel Export Before Converting It
An Excel workbook can contain multiple sheets, formulas, hidden data, formatting and automatic type conversions that disappear or change during export. This workflow helps you inspect and clean a copy before creating a CSV, then reopen and reconcile the result against the original. Use Excel or Power Query for the review and transformations; the linked Nirmion Excel to CSV tool converts a file locally in your browser and does not perform data cleaning. Check your organization's data-handling rules before using any online utility, even one that processes locally.
MISSION Prepare an Excel workbook export for reliable downstream use while preserving the original, protecting identifiers and verifying the converted output.
Inspect the workbook and target format before convertingTHE REAL-WORLD BIT
What happens outside this browser tab?
Protect the source and define the target schema; inspect every relevant worksheet and hidden data; preserve identifiers and choose explicit data types; clean with recorded, reversible rules; check duplicates and reconcile totals; convert only the intended worksheet or sheets; reopen the result and verify fidelity before import.
YOUR CHECKLIST, WITH FEWER DRAMATIC SIGHES
One step at a time.
Follow the order below. If a step names a Nirmion tool, its link is right there with it.
- 01
Preserve the source and define what the receiving system needs
Save an untouched copy of the export and work from a separate version; record its source, export date, owner, expected row count and destination system. Ask the receiving system owner for the required columns, names, data types, delimiter, encoding, date convention and whether it expects one file per worksheet. Define the row key before changing or deduplicating anything, and identify fields such as postal codes, product IDs or account references that are labels rather than quantities. If the workbook contains personal, financial or confidential information, confirm your organization's handling rules before opening it in a new service. This baseline gives you evidence to compare after transformations and helps prevent silent loss during conversion.
- 02
Inspect worksheets, hidden content and formulas
Open the workbook in Excel and inspect the sheet tabs, including hidden worksheets, before choosing what to export. Check each relevant sheet for the true header row, blank or merged cells, filters, totals rows, comments, formulas and external links. A hidden worksheet is still present in the workbook and may contain data referenced elsewhere, so do not assume that hidden means disposable. Identify whether the destination needs the displayed result of a formula or the formula itself; CSV cannot preserve workbook formulas, formatting, multiple sheets, charts or workbook connections. Keep a note of which sheet is the intended dataset and ask the workbook owner about unfamiliar hidden sheets or calculations rather than deleting them.
- 03
Set types deliberately and protect identifier values
Review a sample from the beginning, middle and end of each column, then set types based on meaning and the destination schema. Treat postal codes, phone numbers, SKU values and identifiers with leading zeros as text; Excel can remove zeros, convert digit strings into dates or truncate numeric values beyond 15 significant digits. Review dates under the workbook's locale so day and month are not reversed, and check decimal and thousands separators against the receiving system. Power Query can import and transform the data while leaving the original source unchanged, but its automatic type detection can make assumptions, so set important column types explicitly and preview them. If a value was already rounded or stripped in the source, formatting cannot recover the missing digits; return to the original system for a fresh export.
- 04
Clean a working copy with explicit, reversible rules
Use Excel or Power Query on the working copy to apply only transformations justified by the receiving system: standardize headers, remove genuinely empty rows, trim whitespace where appropriate, and flag invalid or ambiguous values for review. Record each rule and the number of rows or cells it changes. If you remove duplicates, choose the columns that define a duplicate record and preview the result first; matching on too few columns can erase distinct records. Preserve rejected rows in a separate review sheet or log instead of silently dropping them. Keep formulas or a documented calculated-value output when the destination requires their results. Save the cleaned workbook as a new file and retain the untouched source, transformation notes and exception list so the data owner can audit or reverse the work.
- 05
Reconcile the cleaned workbook before export
Compare the cleaned copy with the source using stable row keys, column names, row and column counts, duplicate counts, blanks and important totals. Check representative values from each type-sensitive column, including leading-zero IDs, long numeric strings, dates, decimals and calculated results. Confirm that every removed or changed record is explained by a logged rule and that no ambiguous person, order or transaction was merged without approval. Resolve unexplained differences with the data owner before creating a delivery file. If the destination expects a single flat table, make sure the chosen worksheet has one header row and no notes, subtotals or unrelated content below the data. This is the last point to correct workbook structure before information is flattened into CSV.
- 06
Convert only the reviewed worksheet or worksheets
Choose CSV only if the receiving system accepts a plain text table and does not need formulas, formatting, multiple worksheets, charts, comments or workbook metadata. Excel's CSV format saves the text and displayed values from the active worksheet, so select the intended sheet and confirm whether the destination expects one CSV per sheet. Use the published Excel to CSV tool for a reviewed copy when your data policy allows it; its current tool definition says conversion is local to your browser and formulas are not executed. The tool converts workbook content but does not clean, validate or approve it. For confidential or regulated exports, use an organization-approved offline workflow unless your policy explicitly permits this browser utility. Keep the original XLSX and the cleaned workbook alongside the CSV until the import has been verified.
- 07
Reopen the CSV and verify it in the target system
Open or import the CSV with a controlled preview so you can specify delimiter, encoding, locale and column types instead of letting software guess. Confirm the header names, field count on every row, text encoding, row count, identifier zeros, long numbers, dates, decimal values and key totals. CSV quoting matters when fields contain commas, quotes or line breaks; use a CSV-aware importer rather than splitting lines manually. Compare the imported result with the cleaned workbook and ask the destination owner to confirm that required fields landed correctly. Record the output filename, conversion date, transformation version and any accepted exceptions. Retain or delete the source files under your organization's retention policy and do not share the CSV through an unapproved channel.
THE HELPER CREW
Tools for the fiddly bits.
These are the currently published Nirmion tools matched to this guide. Open a tool page for its accepted inputs and limits.
RECEIPTS, PLEASE
Sources & review notes
Each source is linked to the steps it supports. Open it to check its scope and current guidance.
Source checked 2026-10-10
- Microsoft Support — About Power Query in Excel
- Microsoft Support — Keep or remove duplicate rows in Power Query
- Microsoft Support — Keeping leading zeros and large numbers
- Microsoft Support — Excel formatting and features not transferred to other file formats
- Microsoft Support — Import or export text and CSV files
- IETF RFC 4180 — Common Format and MIME Type for Comma-Separated Values