Fixing date, phone and number formats in a CSV
A CSV file stores everything as text. Strong data typing isn't supported by the format.1 The file never says "this column is a date". The new system has to guess, and it guesses from the pattern of the characters.
When the guess and the pattern disagree, you get swapped days and months, lost digits and amounts that are off by a factor of ten.
Dates
The main danger is order. Some places write day, month, year. Others write month, day, year. A value with a slash between two small numbers can mean two different days, depending on the reader. One CRM's guide lists day month year, month day year and year month day as valid date formats.2 Your file has to match the one the importer expects.
Here's how to fix a date column:
- Find out which order the new system reads. Check its import help, or its date setting.
- Find out which order your old file used. Look for a date whose first number is larger than twelve. That can only be a day, which settles it.
- Convert the whole column to one pattern.
- Check the first and last rows.
A year-first pattern removes the doubt. ISO 8601 writes dates in year-month-day order with hyphens, as YYYY-MM-DD.3 Check your importer's help to see whether it accepts that pattern.
Phone numbers
Phone numbers are labels, not quantities. Don't let anything treat them as numbers to add up.
- Keep the column as text.
- Remove stray characters such as dots and brackets if the importer is strict.
- Add the country code if the system wants one.
Some systems read international format. One CRM's guide says to format phone numbers as +[country code][number].2 The international numbering plan limits numbers to digits and a maximum of fifteen of them.4 A longer value is a sign something went wrong, such as two numbers pasted into one cell.
A phone number without a country code is ambiguous if your contacts live in several countries. Adding the code during cleaning is easier than guessing later.
Money and other numbers
Amounts go wrong in two ways: the decimal mark and the symbol.
The decimal mark. Some countries use a comma as the decimal point and a dot to group thousands. In those places, the field separator in a CSV is often a semicolon.1 If you switch an amount to the dot style, check that no stray commas are left inside the cell, or they can split the field.
The symbol. As general advice, keep currency signs and thousands separators out of a numeric cell unless your importer says otherwise. One CRM's guide says USD currency data should be formatted using decimals.2 Check your importer's own example before you keep or remove symbols.
A tidy money column has:
- One currency per column, or a separate column naming the currency.
- A plain number with the decimal mark the importer expects.
- No words such as "approx".
- A minus sign for credits, if the system allows it.
Percentages need the same care. One CRM's guide says to import 25%, the cell should contain either 25% or .25.2 Check which form your system takes.
Rows that fail on a rule
Some systems reject a row if it breaks a rule you set up, such as a required format. The CRM guide says that where validation rules are set, imported values must follow them.2 If rows go missing after an import, look at the rules on those fields first.
After conversion
Spot-check by eye. Pick a record you know well and compare its date, phone and amount in the old system and the file. Do it again after a trial load. The page on a trial load explains how.
If values still look wrong, go back to cleaning the file and check encoding and separators.
Notes and sources
- CSV, Comma Separated Values (RFC 4180), Library of Congress
- Format import files, HubSpot Knowledge Base
- ISO 8601, Wikipedia
- E.164, Wikipedia