Check a contacts CSV for empty rows and odd columns

A contact import can fail or put information in the wrong fields when a CSV has blank records, unexpected columns, or rows that split differently from the header. Check the file before importing so you can correct real problems without deleting contacts that are merely missing an optional detail.

Make a copy of the CSV first, then inspect its rows and columns in the CSV Editor. It opens CSV files in your browser, where you can find, edit, sort, and remove duplicates.

Check the CSV before importing it

1. Keep an untouched copy. Duplicate the original file and work on the copy. If a cleanup removes useful information or changes the layout, you can start again from the original.
2. Open the copy in the CSV Editor. The editor works with the file in your browser. Check that the first row appears to be the column headings, such as name, email, and phone, and that values appear under the headings you expect.
3. Check the number and order of columns. Compare the headings with the fields your contact service accepts. Look for surprise columns, such as an empty heading at the end, and for contact details appearing under the wrong heading.
4. Look for wholly empty records. A blank row has no values in any field. Check around the top and bottom of the data, and look for gaps if the editor makes them visible. Do not confuse an empty row with a contact that has one or more blank fields.
5. Inspect records that look different. Find unusual values or sort a column to bring similar entries together. Check rows with missing email addresses, unexpectedly short or long entries, or information that seems to have shifted into a neighboring field.
6. Fix only confirmed problems. Remove fully empty rows if they are not needed, and edit or remove extra columns only after checking what they contain. Save the corrected file as a new copy, then reopen it and check the headings and a few records before importing.

Keep an empty field when a contact is missing an optional value. For example, a person without a company name can still be a valid contact. Deleting every row with a blank cell could remove many useful records.

What an empty row or odd column can mean

A row is completely blank

A fully empty row often comes from extra line breaks at the end of a file or from a blank line between groups. It usually contains no contact information, but confirm that every field is empty before removing it. A row with a name and no email is not an empty row.

Some rows have fewer values than the heading row

This can be normal when a contact has no value for one or more fields. CSV rows do not have to fill every column. Check that the existing values still line up with the right headings, and leave empty cells where the information is genuinely unknown.

A row has more values than expected

An extra value can indicate a stray delimiter, a missing quote, or a real extra field. Look closely at the affected record before deleting anything. A comma inside a field, such as Smith, Jordan, should be enclosed in quotation marks in a CSV. Without the quotes, a program may treat that comma as a column break.

The columns seem shifted for many records

The file may use a different separator than the importer expects. Some CSV files use semicolons instead of commas, often because of regional settings. If headings and values do not line up across the file, check the delimiter setting in the import process rather than moving individual values by hand.

There is an unnamed column at the far right

This may be an accidental trailing separator, but it could also contain information that was not included in the visible heading. Inspect the column from top to bottom before removing it. If it is empty in every record, it is usually safe to remove a copy of that column from the file.

Keep contact details intact while cleaning

Use the smallest change that fixes the problem. Removing a blank record is different from removing an incomplete contact, and deleting an extra column is different from deleting an extra value from a particular row. After an edit, check the same contact again to make sure its name, email, and phone number remain together.

Take special care with phone numbers and other identifiers that begin with zero. A spreadsheet program may treat them as numbers and remove the first zero. If the CSV is intended for a contact import, keep these values in the format the receiving service expects. See why leading zeros disappear from CSV data if a number changes when you open or save the file.

Do not remove duplicate-looking rows until you compare the full records. Two people can share a name, and one person can have more than one email address. The CSV Editor can remove duplicates, but decide which fields should count as a duplicate before using that feature.

Problems you might hit

The file looks like one long column

The separator may not match the way the file is being read. Check whether the source uses commas, semicolons, or tabs, and use the correct delimiter option when opening or importing it. Do not replace every separator character blindly, because some may be part of a quoted contact field.

The file looks correct but the contact service rejects it

The service may require particular headings or a specific column order. Compare the first row with its import instructions, then create a separate import copy with the required headings. Keep the original data intact until the service accepts the file.

Changes are hard to verify

After cleanup, reopen the saved copy and check the first few rows, several records you edited, and the final record. Confirm that the column headings still match the values and that the file remains a CSV rather than being saved in a different format.

The CSV Editor works on the file in your browser. The file is not uploaded, so you can inspect contact data without sending it to a separate editing service.