Imports fail in a small number of predictable ways. A row rejected on a unique constraint. A column that arrived one field to the left. Names displaying as José. Duplicate contacts that were supposed to be one person.
Every one of those is visible before you upload. The trick is doing the checks in the right order, because some of them mask others.
Step 1: look at the raw file, not the spreadsheet
Open the file in a plain text editor before you open it in Excel. Spreadsheets are helpful and that is the problem — they silently reinterpret what they find.
Excel converts anything resembling a date. A product code like 3-15 becomes 15 March. A long numeric ID gets rendered in scientific notation and the last digits are lost. Leading zeros in postcodes and phone numbers disappear. None of this is announced, and once saved it is not reversible.
In a text editor you see what the file actually contains. If you must use a spreadsheet, use the import dialogue and set every column to Text rather than double-clicking the file.
Step 2: fix the line endings
Windows ends lines with carriage return plus line feed; Unix uses line feed alone. Mixing them is the single most common cause of two identical-looking lists failing to match, because every line in one of them carries an invisible extra character.
Do this first, because a stray carriage return defeats de-duplication, trimming and comparison. Everything downstream depends on it.
Most editors show and convert line endings in the status bar. If you are not sure whether you have a problem, convert one line to character codes — a 13 before every 10 tells you immediately.
Step 3: trim whitespace
Copy-pasted data is full of leading and trailing spaces. They are invisible, they survive into the database, and they break every exact-match lookup afterwards.
Trim before de-duplicating, not after. john@example.com and john@example.com are different strings, so an untrimmed list de-duplicates to nothing useful.
Watch for non-breaking spaces here too. They look like spaces and most trim functions leave them alone — they arrive from Word documents, from web pages and from PDF extraction.
Step 4: normalise case where it does not matter
For email addresses, lowercase everything. Domains are case-insensitive and every mail provider in ordinary use treats the local part that way too. Without this, John@Example.com and john@example.com both survive de-duplication as separate contacts, and that person receives everything twice.
Do not do this blindly for other fields. Names, product codes and file paths often carry meaning in their capitalisation.
Step 5: remove duplicates and read the count
Now that the data is consistent, de-duplication actually works. Our Duplicate Line Remover keeps the first occurrence and preserves original order, which matters when the list is ranked or chronological.
The important part is checking the counts afterwards. Lines in, unique out, duplicates removed. If that last number is wildly larger than expected, something upstream is wrong — usually the case setting, occasionally a column that got duplicated during a merge. Investigate before you overwrite the source file.
If you want to see what is being removed rather than trusting it, the same tool has a duplicates-only mode. Paste two merged exports and it shows exactly which entries appear in both.
Step 6: check the format the destination expects
Now convert. One item per line, comma-separated, tab-separated, semicolons — every system wants something different, and reformatting hundreds of entries by hand invites errors in exactly the values you were preserving.
Our Text Separator converts between formats in one step, and can strip empty entries and wrap values in quotes at the same time.
Two format details that cause most CSV failures: values containing a comma must be quoted, or the row shifts by a column; and values containing a line break must be quoted too, or one record becomes two.
Step 7: check the encoding
If names with accents display as José or café, the file is UTF-8 being read as something else, or the reverse.
Save as UTF-8 and confirm the destination expects UTF-8. Watch for the byte order mark — some tools write invisible marker bytes at the start of a UTF-8 file, and a naive reader turns your first column header into something that matches nothing. That is why an import sometimes fails on the first field only.
Step 8: import ten rows first
Take the first ten lines, import those, and look at the result in the destination system.
Ten rows takes a minute and catches column misalignment, encoding problems and format mismatches while they are trivial to fix. The same problems found after importing forty thousand rows mean a cleanup that takes considerably longer than the eight steps above.
The order, condensed
- Inspect the raw file in a text editor
- Normalise line endings
- Trim whitespace, including non-breaking spaces
- Lowercase email addresses
- De-duplicate and check the counts
- Convert to the format the destination wants
- Confirm UTF-8 encoding
- Test with ten rows before importing everything
Every one of these runs in your browser, which matters when the list is a customer export. Contact data should not be pasted into a service you have not vetted.
