How to clean a messy CSV export without losing data

Exports from a shop, a CRM or an accounting tool break in the same few ways. Names turn into "José". A phone number loses its leading zero. A date flips from 03/04 to 04/03. Opening the file in Excel and saving it makes some of these worse, because Excel quietly changes what it thinks it understands.

The safer way is a short script that reads the original file and writes a new one. You can read it, rerun it next month on the next export, and the original stays untouched.

What usually goes wrong

• Encoding. Accents and symbols appear as odd characters. The file is probably UTF-8 and was read as something else, or the reverse.
• Delimiter. Everything lands in one column, or a comma inside an address splits it. Some regions export with semicolons.
• Leading zeros. Postcodes, phone numbers and product codes lose the zero when treated as numbers.
• Long numbers. Order and account IDs turn into 1.23E+11 in Excel. The digits are gone once saved.
• Dates. Day-first and month-first mixed, or text dates that do not sort.
• Headers. Duplicate column names, trailing spaces, or two header rows.
• Blank and placeholder values. Empty, "N/A", "null" and "-" mean the same thing in different rows.
• Duplicates. The same record exported twice, with small differences.

The prompt

Do not paste the whole file. Paste the header row and about 20 representative rows, with anything private removed or replaced. Then use this.

I have a CSV export I need to clean. Write a Python 3 script (standard library plus pandas if you need it) that reads the original file and writes a cleaned copy. It must never overwrite the original.

Facts about the file:
- Where it came from: [for example, a Shopify orders export]
- What I want at the end: [for example, one row per order, dates in YYYY-MM-DD, postcodes as text]
- Day-first or month-first dates: [say which]

First, from the sample below, list what you think is wrong with each column and the rule you propose for each. Mark any rule that needs my decision, such as which of two duplicates to keep.

Then write the script. Requirements:
- Read every column as text first, so nothing loses leading zeros or long digits.
- Print a summary: rows in, rows out, rows changed per rule, rows dropped and why.
- Write a second file listing every changed cell (row, column, old value, new value, rule).
- Do not guess values. If a value is ambiguous, leave it as it was and list it in a file called needs_review.csv.

SAMPLE (header and rows):
[paste here]

Run it and check

1. Run the script on a copy of the full export.
2. Compare rows in with rows out. The difference should match the duplicates you agreed to drop.
3. Total any number column before and after. If the totals differ, the change log should explain why.
4. Open the change log and read 20 lines against the original.
5. Open needs_review.csv and decide those by hand.
6. Import the cleaned file into a test copy of the destination first, not your live data.

If the assistant cannot run code, it will give you the script to run yourself. Read it before you run it, and be wary of any line that deletes or overwrites files.

Limits

• The script is only as good as the sample you showed. A rare format that was not in the sample can slip through. That is what the totals and the review file are for.
• Rules like "these two customers are the same person" are your decision, not the script's.
• Large files may need chunked reading. Ask for that if the file is bigger than your memory.
• Never send customer data to a tool you are not allowed to send it to. Replace names and emails in the sample with fake ones.
• This does not fix data that was wrong at the source.

Browse all skills in the catalog