Short answer
What you need to know
Open the file in a tool that reads it as rows and columns and shows invisible characters, such as Data Check. Look for spaces at the start or end of values, tabs and line breaks inside values, no-break and zero-width spaces, control characters, a byte-order mark, and rows with the wrong number of columns. Fix the characters inside the values, keep the separators and quotes in place, and import the cleaned copy.
Why hidden characters matter in a database
A database compares values exactly. "Smith" and "Smith " with a trailing space are two different values, so a unique key lets both in, a join misses one of them, and a count by customer comes out wrong.
Most of these characters do not cause an error. The import succeeds, and the damage appears later as duplicate records, searches that find nothing, and reports that do not add up. That is why it pays to look before importing rather than after.
The characters to look for
Spaces at the start or end of a value, often left by copying from a spreadsheet or a form. Double spaces inside a value. Tabs inside a value, which some tools also treat as a column break.
Line breaks inside a value. A CSV allows them inside quotes, but many importers and scripts read each line as a new row and split the record in two.
Invisible characters: the no-break space (U+00A0), which looks exactly like a space; the zero-width space (U+200B), which takes up no room at all; and direction marks copied from web pages and chat apps.
Control characters such as NUL (U+0000), which some databases refuse outright, and a byte-order mark (U+FEFF) at the very start of the file, which an import can read as part of the first column's name.
Check the file's structure too
Every row should have the same number of columns as the header row. A row with one too many usually means a comma inside a value that was not wrapped in quotes; a row with one too few often means a value split by a line break.
Quotes must be balanced. A value that opens with a quote and never closes it swallows the rest of the file into one value. Column names should be filled in and different from each other, or the import cannot map them.
These problems need a person, because the right fix depends on what the data should say. Data Check reports them and leaves those rows unchanged.
A worked example
A contact list has the header name,email and a row reading "Ann Lee ",ann@example.com. Data Check reports one trailing space in the name column and shows it as Ann·Lee· in the preview, where each · is a space.
Choosing Remove for spaces at the start or end turns the value into Ann Lee. The quotes around it are kept, the comma between the columns is untouched, and every other row is copied exactly as it was. Importing the cleaned file stores Ann Lee, which now matches the same person typed anywhere else.
A checklist before every import
Keep the original file and work on a copy. Confirm the separator (comma, semicolon, or tab) and whether the first row holds column names.
Check for spaces at the edges, tabs, line breaks, and invisible characters inside values; check for a byte-order mark and mixed line endings in the file; and check that every row has the same number of columns.
Review every change in a before-and-after preview, save the cleaned copy as UTF-8, and import that copy into a test table before the real one.
Important limitation
Verify important findings
A character that looks wrong is not always wrong: names in many languages need accented letters, and some fields legitimately contain line breaks or tabs. Decide what the column should allow before removing anything, and keep the original file.
Further reading
Authoritative references
These primary sources support the concepts and reference information in this guide. Their inclusion does not imply endorsement of Hidden Word Scanner.