Clean your CSV file

Why a CSV import fails

A CSV file is a table written as text: one row per contact, columns separated by a character. Nothing in it is standardized, not the delimiter, not the encoding of accents, not the way phone numbers are written. Every export tool has its habits, every spreadsheet has its own, and the file that reaches your calling software often stacks several problems.

Here are the eight causes found in almost every prospecting file, all fixed by the tool:

1. 1

   The wrong delimiter: comma, semicolon or tab: a file read with the wrong one fits in a single column.

2. 2

   Wrapped rows: a CSV opened and saved again in Excel with a semicolon locale, each row ending up in quotes.

3. 3

   Broken accents: “é” instead of “é”, the sign of a file read in the wrong encoding.

4. 4

   Badly written numbers: spaces, dots, missing country code, leading zero dropped by Excel or 6.12E+08 notation.

5. 5

   Too many columns: photo, dates, messages: dozens of columns to skip one by one.

6. 6

   Duplicates: the same contact twice, written two different ways.

7. 7

   Damaged names: ALL CAPS, emojis, titles, the full name in one cell.

8. 8

   Empty or shifted rows: ghost contacts, or values stored in the wrong column.

The Excel trap: comma or semicolon

In many European locales, Excel uses the semicolon as the CSV delimiter because the comma is the decimal mark. A comma-separated file, from a LinkedIn tool or a US CRM, then opens in a single column. Save it as is and Excel wraps every row in quotes and appends semicolons: the file becomes unreadable for every other program.

It is the most common case of a file that “won’t import”. The tool recognizes it, unwraps each row and restores the original columns. To avoid it at the source, open a CSV with Data > From Text/CSV instead of double-clicking it.

International phone number format

Calling software dials exactly what it is given. The safest format is the international E.164 format: a plus sign, the country code, then the number without its trunk prefix and without spaces. (201) 555-0123 in the US becomes +12015550123, 020 7946 0958 in the UK becomes +442079460958.

For numbers written without a country code, the tool infers the country from your file, based on the numbers already written internationally, and lets you change it. To check a list of numbers on its own, use the phone number validator.

Preparing a LinkedIn, Sales Navigator or enrichment export

LinkedIn scrapers and enrichment tools produce very wide files: sometimes more than 200 columns, of which only about ten are needed to call. The tool recognizes their usual columns (first_name, current_company, profile_url, location_name…) and keeps only what matters.

It also puts links in the right place: a public profile link (linkedin.com/in/…) goes to the LinkedIn field, a Sales Navigator link (linkedin.com/sales/…) to the LinkedIn Sales URL field. When the job title or company is missing, it finds them in the profile headline, such as “Head of HR at Acme”.

Cleaning a CSV by hand in Excel or Google Sheets

Without a tool, expect about ten minutes for a small file. To switch between semicolons and commas, open the file with Data > From Text/CSV, pick the right delimiter, then save as “CSV UTF-8”. To remove duplicates, select the table and use Data > Remove Duplicates (Excel) or Data > Data cleanup > Remove duplicates (Google Sheets); note that these commands cannot tell that (201) 555-0123 and +12015550123 are the same number.

For phone numbers a formula is not enough: every country has its own length and prefixes. For capitalization, =PROPER(A2) capitalizes every word, but also turns “de La Fontaine” into “De La Fontaine”. That is exactly what the tool above does for you, in one go, without uploading anything.

Go further

• Import contacts from a CSV file Skipcall’s import screen, step by step.
• Phone number validator Check and format a list of numbers on its own.
• Enrich your contacts Find numbers for the contacts set aside.