Practical data-cleaning guide
How to clean CSV data before opening it in Excel
A CSV file can look fine in a text editor and still open as one long column, split a company name across several cells, or produce rows with different widths. These problems usually come from inconsistent separators, missing quotes, duplicate records, or data copied from a formatted document instead of a spreadsheet.
What a clean CSV row needs
Every row should use the same delimiter and contain the expected number of columns. A value containing the delimiter must be surrounded by quotation marks. For example, the comma inside $1,200.50 or Atlanta, Georgia must not be mistaken for the boundary between two cells.
Messy pasted table
Date Transaction_ID Amount Category 09/10/2026 TXN-9901 $1,200.50 Software SaaS 09/11/2026 TXN-9902 $45.00 Office Supplies 09/10/2026 TXN-9901 $1,200.50 Software SaaS
Clean CSV
Date,Transaction_ID,Amount,Category 09/10/2026,TXN-9901,"$1,200.50",Software SaaS 09/11/2026,TXN-9902,$45.00,Office Supplies
A reliable cleanup workflow
- Keep an untouched copy. Never make the only copy of an export your working file.
- Identify the separator. Common formats use commas, tabs, or semicolons. Tables copied from email and PDF files may use repeated spaces.
- Preview the rows and columns. A visual table makes a shifted value or uneven row visible before it reaches Excel.
- Remove exact duplicate records. Confirm that repeated rows are accidental rather than legitimate recurring transactions.
- Drop columns only when every cell is empty. A column with one populated value still contains information.
- Check dates and numbers. CSV files store text, so Excel may interpret dates and decimal separators according to the computer’s regional settings.
- Export and verify. Compare the cleaned file’s row count, totals, and several records with the original source.
Clean the data with TextGleam
Open the CSV and table cleaner, paste the data, and select Convert to interactive table. TextGleam detects commas, tabs, semicolons, and columns separated by repeated spaces. You can edit individual cells, add a row or column, remove duplicate rows, and download a UTF-8 CSV or Excel workbook.
Open the private CSV and table cleaner
Common CSV problems
Everything opens in one Excel column
The file may use a delimiter that differs from the separator expected by Excel under the current regional settings. Import the file through Excel’s text/CSV importer and select the actual delimiter, or convert it to the format your workflow expects.
Commas create extra columns
Values containing commas need correct CSV quotation. A standards-aware parser treats "Smith, Jordan" as one value and preserves escaped quotation marks inside quoted fields.
Rows have different numbers of cells
Look for an unquoted separator, a missing closing quote, or a line break embedded in a field. Determine which field the extra value belongs to before editing it.
Leading zeros disappear
Excel may convert identifiers such as ZIP codes, account numbers, and product codes into numbers. Import those columns as text when leading zeros carry meaning.
Final verification checklist
- The header names match the source.
- Every row has the expected number of columns.
- Quoted commas and multiline fields remain intact.
- Duplicate removal did not erase valid repeated activity.
- Dates, currencies, identifiers, and leading zeros retained their meaning.
- Totals and record counts match the original export.
For sensitive business data, follow your organization’s handling rules even when using a local browser tool. Automated formatting can expose structural problems, but the owner of the data should verify the final spreadsheet.