Check a CSV before importing it: encoding, IDs, dates, and duplicates
Written by Webutila · Updated · How we maintain our guides
A CSV is a text table, not a workbook with cell types. An import can silently change an identifier, a date, or a phone number even when the file opens without an error.
1. Keep a copy and check the encoding
Before cleaning an export, keep the original. Open it in CSV Cleaner and check the encoding and delimiter. If Japanese text looks garbled, try the appropriate UTF-8 or Shift_JIS setting before continuing. Saving already-garbled text as UTF-8 cannot reconstruct the intended characters.
2. Decide which columns must stay text
Codes such as 00123 and phone numbers beginning with zero should usually remain text. When importing into a spreadsheet, explicitly set those columns to text. CSV quotation marks separate fields; they do not universally force a spreadsheet to keep a field as text.
3. Try a cleanup with invented contact data
Download the synthetic contact CSV. It contains two rows that become identical after whitespace, recognizable date, and phone separator normalization. Enable those options and duplicate removal. Expect one output row containing Alice, 2026-01-03, and 09012345678.
The name and phone-like value are invented test data, not a contact directory. Matching whole rows does not establish that two real records refer to the same person. Do not remove duplicates solely on a shared name if different records need to remain separate.
4. Resolve ambiguous dates yourself
2026/1/3 can be normalized to 2026-01-03. A value such as 03/04/2026 needs source context because month-first and day-first readers disagree. Blanks also need a business rule: a missing quantity is not necessarily zero. CSV Cleaner does not guess ambiguous dates or fill missing values.
5. Compare stable spreadsheet exports
Download old inventory and new inventory. In Excel & CSV Diff, use the corresponding old and new inputs. Expect one changed cell, B2, from 12 to 18.
Cells are matched by sheet name and address, not a product identifier. Keep the same row order for a useful comparison. Inserting a row can shift later records and create many cell differences. A numeric value and a text value that look identical can also differ by type.
6. Understand conversion losses
Download a two-record JSON example and open it in Data Converter. Convert JSON to CSV. Expect the headings item and stock, with two data rows. Converting back from CSV makes stock text, because CSV carries no explicit numeric type. Nested objects are stored as JSON text inside a CSV cell rather than expanded into columns.
7. Check the saved file in the receiving application
Formula-like exported values may have an apostrophe prefix to reduce spreadsheet formula injection risk. Review that behavior when a destination needs exact text. Import a small sample, verify row counts, leading zeroes, dates, non-Latin characters, and blanks, and only then process the larger export.