Importing your data into Google Sheets for ‘data cleaning’
Open Google Sheets and:
- File -> Import
- Select your CSV file
- Click Insert
- Make your Import Location selection and click ‘Import data’
Using ‘Find and replace’ and REGEX to clean your data
‘Find and replace’ is a user-friendly way of cleaning your data.
For example, if you want to:
- Transform lowercase Country Code to Uppercase –
- Fill empty cells with a default value – In the case shown in the image below, we wanted to fill empty Time Zone values with the default value “Australia/Sydney”
- We set find to ^$, ticked “Search using regular expressions, and used Australia/Sydney as the inserted text.
- This next one adds a zero to a phone number that starts with a 4. Note that the phone colmn must have the column as a string – then use ^4 -> 04 to replace the 4 with a 04
List of Supported Countries
| “BD”: “Bangladesh”, “BE”: “Belgium”, “BF”: “Burkina Faso”, “BG”: “Bulgaria”, “BA”: “Bosnia and Herzegovina”, “BB”: “Barbados”, “WF”: “Wallis and Futuna”, “BL”: “Saint Barthelemy”, “BM”: “Bermuda”, “BN”: “Brunei”, “BO”: “Bolivia”, “BH”: “Bahrain”, “BI”: “Burundi”, “BJ”: “Benin”, “BT”: “Bhutan”, “JM”: “Jamaica”, “BV”: “Bouvet Island”, “BW”: “Botswana”, “WS”: “Samoa”, “BQ”: “Bonaire, Saint Eustatius and Saba “, “BR”: “Brazil”, “BS”: “Bahamas”, “JE”: “Jersey”, “BY”: “Belarus”, “BZ”: “Belize”, “RU”: “Russia”, “RW”: “Rwanda”, “RS”: “Serbia”, “TL”: “East Timor”, “RE”: “Reunion”, “TM”: “Turkmenistan”, “TJ”: “Tajikistan”, “RO”: “Romania”, “TK”: “Tokelau”, “GW”: “Guinea-Bissau”, “GU”: “Guam”, “GT”: “Guatemala”, “GS”: “South Georgia and the South Sandwich Islands”, “GR”: “Greece”, “GQ”: “Equatorial Guinea”, “GP”: “Guadeloupe”, “JP”: “Japan”, “GY”: “Guyana”, “GG”: “Guernsey”, “GF”: “French Guiana”, “GE”: “Georgia”, “GD”: “Grenada”, “GB”: “United Kingdom”, “GA”: “Gabon”, “SV”: “El Salvador”, “GN”: “Guinea”, “GM”: “Gambia”, “GL”: “Greenland”, “GI”: “Gibraltar”, “GH”: “Ghana”, “OM”: “Oman”, “TN”: “Tunisia”, “JO”: “Jordan”, “HR”: “Croatia”, “HT”: “Haiti”, “HU”: “Hungary”, “HK”: “Hong Kong”, “HN”: “Honduras”, “HM”: “Heard Island and McDonald Islands”, “VE”: “Venezuela”, “PR”: “Puerto Rico”, “PS”: “Palestinian Territory”, “PW”: “Palau”, “PT”: “Portugal”, “SJ”: “Svalbard and Jan Mayen”, “PY”: “Paraguay”, “IQ”: “Iraq”,“PA”: “Panama”, “PF”: “French Polynesia”, “PG”: “Papua New Guinea”, “PE”: “Peru”, “PK”: “Pakistan”, “PH”: “Philippines”, “PN”: “Pitcairn”, “PL”: “Poland”, “PM”: “Saint Pierre and Miquelon”, “ZM”: “Zambia”, “EH”: “Western Sahara”, “EE”: “Estonia”, “EG”: “Egypt”, “ZA”: “South Africa”, “EC”: “Ecuador”, “IT”: “Italy”, “VN”: “Vietnam”, “SB”: “Solomon Islands”, “ET”: “Ethiopia”, “SO”: “Somalia”, “ZW”: “Zimbabwe”, “SA”: “Saudi Arabia”, “ES”: “Spain”, “ER”: “Eritrea”, “ME”: “Montenegro”, “MD”: “Moldova”, “MG”: “Madagascar”, “MF”: “Saint Martin”, “MA”: “Morocco”, “MC”: “Monaco”, “UZ”: “Uzbekistan”, “MM”: “Myanmar”, “ML”: “Mali”, “MO”: “Macao”, “MN”: “Mongolia”, “MH”: “Marshall Islands”, “MK”: “Macedonia”, “MU”: “Mauritius”, “MT”: “Malta”, “MW”: “Malawi”, “MV”: “Maldives”, “MQ”: “Martinique”, “MP”: “Northern Mariana Islands”, “MS”: “Montserrat”, “MR”: “Mauritania”, “IM”: “Isle of Man”, “UG”: “Uganda”, “TZ”: “Tanzania”, “MY”: “Malaysia”, “MX”: “Mexico”, “IL”: “Israel”, “FR”: “France”, “IO”: “British Indian Ocean Territory”, | “SH”: “Saint Helena”, “FI”: “Finland”, “FJ”: “Fiji”, “FK”: “Falkland Islands”, “FM”: “Micronesia”, “FO”: “Faroe Islands”, “NI”: “Nicaragua”, “NL”: “Netherlands”, “NO”: “Norway”, “NA”: “Namibia”, “VU”: “Vanuatu”, “NC”: “New Caledonia”, “NE”: “Niger”, “NF”: “Norfolk Island”, “NG”: “Nigeria”, “NZ”: “New Zealand”, “NP”: “Nepal”, “NR”: “Nauru”, “NU”: “Niue”, “CK”: “Cook Islands”, “XK”: “Kosovo”, “CI”: “Ivory Coast”, “CH”: “Switzerland”, “CO”: “Colombia”, “CN”: “China”, “CM”: “Cameroon”, “CL”: “Chile”, “CC”: “Cocos Islands”, “CA”: “Canada”, “CG”: “Republic of the Congo”, “CF”: “Central African Republic”, “CD”: “Democratic Republic of the Congo”, “CZ”: “Czech Republic”, “CY”: “Cyprus”, “CX”: “Christmas Island”, “CR”: “Costa Rica”, “CW”: “Curacao”, “CV”: “Cape Verde”, “CU”: “Cuba”, “SZ”: “Swaziland”, “SY”: “Syria”, “SX”: “Sint Maarten”, “KG”: “Kyrgyzstan”, “KE”: “Kenya”, “SS”: “South Sudan”, “SR”: “Suriname”, “KI”: “Kiribati”, “KH”: “Cambodia”, “KN”: “Saint Kitts and Nevis”, “KM”: “Comoros”, “ST”: “Sao Tome and Principe”, “SK”: “Slovakia”, “KR”: “South Korea”, “SI”: “Slovenia”, “KP”: “North Korea”, “KW”: “Kuwait”, “SN”: “Senegal”, “SM”: “San Marino”, “SL”: “Sierra Leone”, “SC”: “Seychelles”, “KZ”: “Kazakhstan”, “KY”: “Cayman Islands”, “SG”: “Singapore”, “SE”: “Sweden”, “SD”: “Sudan”, “DO”: “Dominican Republic”, “DM”: “Dominica”, “DJ”: “Djibouti”, “DK”: “Denmark”, “VG”: “British Virgin Islands”, “DE”: “Germany”, “YE”: “Yemen”, “DZ”: “Algeria”, “US”: “United States”, “UY”: “Uruguay”, “YT”: “Mayotte”, “UM”: “United States Minor Outlying Islands”, “LB”: “Lebanon”, “LC”: “Saint Lucia”, “LA”: “Laos”, “TV”: “Tuvalu”, “TW”: “Taiwan”, “TT”: “Trinidad and Tobago”, “TR”: “Turkey”, “LK”: “Sri Lanka”, “LI”: “Liechtenstein”, “LV”: “Latvia”, “TO”: “Tonga”, “LT”: “Lithuania”, “LU”: “Luxembourg”, “LR”: “Liberia”, “LS”: “Lesotho”, “TH”: “Thailand”, “TF”: “French Southern Territories”, “TG”: “Togo”, “TD”: “Chad”, “TC”: “Turks and Caicos Islands”, “LY”: “Libya”, “VA”: “Vatican”, “VC”: “Saint Vincent and the Grenadines”, “AE”: “United Arab Emirates”, “AD”: “Andorra”, “AG”: “Antigua and Barbuda”, “AF”: “Afghanistan”, “AI”: “Anguilla”, “VI”: “U.S. Virgin Islands”, “IS”: “Iceland”, “IR”: “Iran”, “AM”: “Armenia”, “AL”: “Albania”, “AO”: “Angola”, “AQ”: “Antarctica”, “AS”: “American Samoa”, “AR”: “Argentina”, “AU”: “Australia”, “AT”: “Austria”, “AW”: “Aruba”, “IN”: “India”, “AX”: “Aland Islands”, “AZ”: “Azerbaijan”, “IE”: “Ireland”, “ID”: “Indonesia”, “UA”: “Ukraine”, “QA”: “Qatar”, “MZ”: “Mozambique”Atlantic/Canary |
List of supported Timezones
| Etc/GMT+12 Pacific/Midway Pacific/Honolulu America/Juneau US/Alaska America/Dawson America/Los_Angeles America/Phoenix America/Tijuana US/Arizona America/Belize America/Boise America/Chihuahua America/Denver America/Edmonton America/Guatemala America/Managua America/Regina Canada/Saskatchewan US/Mountain America/Bahia_Banderas America/Bogota America/Cancun America/Chicago America/Mexico_City US/Central America/Caracas America/Detroit America/Indiana/Indianapolis America/Louisville America/Manaus America/New_York America/Santiago America/Santo_Domingo America/Toronto US/East-Indiana US/Eastern America/Argentina/Buenos_Aires America/Glace_Bay America/Montevideo America/Sao_Paulo Canada/Atlantic America/St_Johns Canada/Newfoundland America/Godthab America/Noronha Etc/GMT+2 Atlantic/Cape_Verde Atlantic/Azores UTC Africa/Algiers Africa/Casablanca Africa/Lagos Atlantic/Canary | Europe/London Africa/Cairo Africa/Harare Europe/Amsterdam Europe/Belgrade Europe/Brussels Europe/Madrid Europe/Oslo Europe/Sarajevo Africa/Nairobi Asia/Amman Asia/Baghdad Asia/Jerusalem Asia/Kuwait Asia/Qatar Europe/Athens Europe/Bucharest Europe/Helsinki Europe/Moscow Asia/Baku Asia/Dubai Asia/Kabul Asia/Tehran Asia/Karachi Asia/Yekaterinburg Asia/Colombo Asia/Kolkata Asia/Calcutta Asia/Kathmandu Asia/Almaty Asia/Dhaka Asia/Rangoon Asia/Bangkok Asia/Krasnoyarsk Asia/Irkutsk Asia/Kuala_Lumpur Asia/Shanghai Asia/Taipei Australia/Perth Asia/Seoul Asia/Tokyo Asia/Yakutsk Australia/Adelaide Australia/Darwin Asia/Vladivostok Australia/Brisbane Australia/Canberra Australia/Hobart Australia/Sydney Pacific/Guam Asia/Magadan Pacific/Auckland Pacific/Fiji Pacific/Tongatapu |
Accepted Custom Field Format Requirements
Depending on the records you are importing, your file could include the following columns:
Date Picker
- Acceptable formats-
- mm/dd/yyyy (e.g. 10/28/2020)
- yyyy/mm/dd (e.g. 2020/10/28)
- You can also replace ‘/’ with ‘-‘ ‘.’ and it’ll recognize the uploaded fields as a Date field (Available in our future release!)
Multiple Options, Checkbox, Textbox List
- Acceptable formats-
- Value 1, Value 2, Value 3 …… (e.g. Blue, Yellow, Green or Blue, Yellow, Green or Blue, Yellow , Green)
- You can also replace ‘ , ‘ with ‘ ; ‘ ‘ . ‘ and it’ll recognize the uploaded fields as the aforementioned field
Radio, Single Option
- Acceptable formats:
- Value 1 (e.g. Blue)
File Upload, Signature
- Not Supported
Contact Owner
- To assign an owner to your contacts during the import, include the name of the ‘User’
- Acceptable formats:
- FirstName LastName (e.g. Logan Paul or logan paul)
- LastName FirstName (e.g. Paul Logan or paul logan)
- Note #1: Unless the user has both FirstName and Last Name, the system will not recognize it during the upload
Tags
- Acceptable formats-
- Value 1, Value 2, Value 3 …… (e.g. Blue,Yellow,Green or Blue, Yellow, Green or Blue , Yellow , Green)
- You can also replace ‘ , ‘ with ‘ ; ‘ ‘ . ‘ and it’ll recognize the uploaded fields as the aforementioned field
- Value 1, Value 2, Value 3 …… (e.g. Blue,Yellow,Green or Blue, Yellow, Green or Blue , Yellow , Green)
| Tags (example formats) | Status |
| lowercase | ok |
| UPPERCASE | ok (will be lowercased) |
| ProperCase | ok (will be lowercased) |
| with a space | ok |
| With A Space | ok (will be lowercased) |
| pre-existing tag | ok (maps successfully) |
DoNotDisturb
- Acceptable formats-
- True or False ( true or false )
- Yes or No ( yes or no )
Phone Number
- For the vast majority of cases, as long as you format the phone numbers in E.164 (Ex: +1 1234567890) our system will recognize it as valid- irrespective of the default Country picked at the sub-account level
- Additionally, if you have set the Country to the US, the following would work perfectly fine as well
- 123-456-7890
- (123) 4567890
- 1234567890
- Furthermore, if you are working with international numbers (Outside the US), we’d encourage you to visit: https://libphonenumber.appspot.com/ and run your phone number format by the tool in conjunction with the codes below to ascertain its appropriateness for upload.
- Additionally, if you have set the Country to the US, the following would work perfectly fine as well
Numerical- Acceptable formats-
- 1.23.4
- 1234
- .123
- Acceptable formats-
- Monetary
- Acceptable formats-
- 1234
- 1,234,234,333
- Acceptable formats-