Messy spreadsheet in, clean import file out: a before/after sample

Before: the messy file (first 12 rows)

Source: messy_contacts.csv. Row numbers are the data rows in that file.

After: the same records cleaned

Source: cleaned.csv, showing the same records. The From row column is added on this page for reference; it is not in cleaned.csv. Columns are as the script wrote them, including the added First Name and Last Name columns.

Flagged for review (3 rows)

Source: flagged.csv. These rows are kept in the cleaned file so a person can check them. Nothing was deleted.

Removed as duplicates (4 rows)

Source: removed_duplicates.csv. Each removed row is listed with the row it duplicates. A row is an exact duplicate if every field matches; otherwise it is removed when its email matches an earlier row.

Possible duplicates (0 groups)

No possible duplicates were found in this sample. The check looks for the same first name, last name and company with different email addresses.

Change log

Rendered from CHANGELOG.md, the file that comes with every cleaned file.

• Input: messy_contacts.csv: 33 rows x 6 columns
• Trimmed stray spaces in 10 cells
• Removed 2 completely empty rows (row 13, 20)
• email: lower-cased/normalised 3 emails
• phone: standardised 21 phone numbers to (###) ###-####
• signup_date: converted 10 dates to YYYY-MM-DD
• name: fixed capitalisation in 3 names
• Row 4 name: reordered 'Last, First' 'Okafor, Ruth' -> First 'Ruth', Last 'Okafor'
• Row 6 name: reordered 'Last, First' 'Lindqvist, Ana' -> First 'Ana', Last 'Lindqvist'
• Row 29 name: reordered 'Last, First' 'Wong, Eleanor' -> First 'Eleanor', Last 'Wong'
• Split 'name' into First Name / Last Name (3 'Last, First' names reordered; original column kept)
• Row 2 province: 'Ontario' -> 'ON'
• Row 3 province: 'bc' -> 'BC'
• Row 4 province: 'Ont.' -> 'ON'
• Row 6 province: 'Quebec' -> 'QC'
• Row 9 province: 'Alberta' -> 'AB'
• Row 12 province: 'Ontario' -> 'ON'
• Row 15 province: 'Ont' -> 'ON'
• Row 17 province: 'On' -> 'ON'
• Row 18 province: 'Ontario' -> 'ON'
• Row 22 province: 'bc' -> 'BC'
• Row 25 province: 'Quebec' -> 'QC'
• Row 26 province: 'British Columbia' -> 'BC'
• Row 27 province: 'Ontario' -> 'ON'
• Row 28 province: 'Ont.' -> 'ON'
• Row 31 province: 'ab' -> 'AB'
• province: normalised 15 region values to 2-letter codes
• Removed 4 duplicate rows (2 exact, 2 same 'email'); each is saved in removed_duplicates.csv, with the row it duplicates
• Row 9 removed: exact, duplicate of row 8
• Row 17 removed: exact, duplicate of row 16
• Row 19 removed: same email, duplicate of row 4
• Row 25 removed: same email, duplicate of row 24
• Possible duplicates: none found
• Flagged 3 rows for review (listed with the reason in flagged.csv; they remain in cleaned.csv); none deleted
• Rows: 33 in, 27 out (2 blank and 4 duplicate rows removed)
• Output: cleaned.csv: 27 rows, no flag column
• Delivery files: cleaned.csv, flagged.csv, possible_duplicates.csv, removed_duplicates.csv, CHANGELOG.md

Download the sample files

• Before: messy_contacts.csv (input)
• After: cleaned.csv
• flagged.csv (rows to review, with the reason)
• possible_duplicates.csv
• removed_duplicates.csv
• Change log: CHANGELOG.md