Cleaning Up Your Data Before Importing Into Affinity

Help center reference: Cleaning Up Your Data Before Importing Into Affinity

How-to — task-oriented recipe.

Importing data into Affinity can be tricky when everyone has their own way of formatting their CSV file data. This guide will help you format your CSV file data correctly for Affinity so that your import experience is smooth and seamless.

Note: This video contains the old navigation bar on the top, whereas the current navigation bar now lies on the left side of your screen. While the remaining content is still the same, rest assured that we will be updating our videos accordingly.

Data Points required based on the type of Affinity list

Depending on the type of list you’ll be importing into, please follow these guidelines:

Importing into a people list?

• You must include a Full Name column in your CSV file. If they’re separated by first and last names, then you can import them as two separate columns as well: First Name and Last Name.
  • If a person has a longer last name (for example Jane Doe Van Lang) you can add two names to the First Name column and/or two names to the Last Name column.
• Don’t have a full name for some email addresses?
  • Do not input the same exact placeholder for all of them (e.g. “Test Person” for 50 email addresses) - this will create one contact with all 50 email addresses combined into one profile.
  • Instead, create a unique placeholder for each email address (e.g. “Test 1,” Test 2,” “Test 3,” etc.) to ensure they get created as separate profiles.
• We highly suggest also including an Email Address column so that Affinity can match these rows to the correct people in your directory.
  • If some people do not have an email address, we will either attempt to match on name only (which has less accuracy due to common names) or create a new person.

Importing into an organization list?

• You must include an Organization Name column in your CSV file.
• We highly suggest also including a Website column so that Affinity can match these rows to the correct organizations in your directory.
  • If some organizations do not have a website, we will still create a new organization contact without a website - this is to minimize the chance of mapping to the wrong organization, which would only cause issues later on.
  • You may add the correct website on these organizations’ profile pages anytime afterwards.
  • If you find another profile that is enriched by Affinity and already has the website, please make sure to go to that profile page, then merge duplicates from there.

Importing into an opportunity list?

• You must include an Opportunity Name column (see Column A above) in your CSV file.
  • Each opportunity name must be unique. Any duplicates will not be imported.
• Please remove any organizations without websites and people without email addresses.
  • Otherwise, this may create duplicate records in Affinity, which would require manual merging later on.
  • If there is an organization associated with each opportunity, make sure there is a column in the CSV file (see Column B above) dedicated to the organization in the following format: Organization Name <website>
    • Please remove any commas (,) in this column to prevent the unwanted creation of blank organizations, since the comma (,) is being used as the delimiter.
  • If there are people associated with each opportunity, make sure there is a column in the CSV file (see Column C above) dedicated to the people in the following format: Full Name1 <email address>; Full Name2 <email address> 

Other Important Callouts

• Make sure your CSV file is saved in the CSV (UTF-8) format.
• The very first row (Row 1) of the CSV file must contain your column headers.
• Column header names must be unique - there cannot be any column headers with the exact same name.
• Column headers with line breaks will fail to properly upload to Affinity.
  • Example: In** Column D**, you’ll notice two lines due to a line break.
• Please make sure that columns with no column headers in Row 1 are also empty from Rows 2 and beyond (e.g. If there is no data in cell E1, there should not be any data in cell E2 and below).
• Make sure you remove hyperlinks (e.g. the Email Address column).
• Remove any unwanted duplicate rows in your CSV file (e.g. If “ABC Ventures” is on Row 2, Row 3, and Row 5).
  • If duplicates do exist and you either choose Append or Overwrite - the importer will only import the first entry of the duplicates (Row 2) while skipping the other duplicate entries (Row 3 and Row 5).

Understanding Dates in the Data Importer

Working with dates during import requires careful attention to formatting and your Affinity settings. Here’s what you need to know to ensure your dates import correctly.

The Critical Importance of Your Date Format Setting

The most important rule: Your CSV date format must match your Affinity Date/Time setting.

Before importing any dates, check your Date/Time format setting in Affinity:

1. Go to your Settings page in Affinity
2. Look for your Date/Time format preference
3. Note whether you have MM/DD/YYYY or DD/MM/YYYY selected

Your CSV file must use the same format that’s configured in your Affinity settings. If your Affinity is set to DD/MM/YYYY format, your CSV must contain dates in DD/MM/YYYY format. If set to MM/DD/YYYY format, your CSV must use MM/DD/YYYY format.

How Affinity Processes Date Imports

When you import dates, Affinity’s system reads each date value from your CSV and interprets it according to your Date/Time setting. Here’s what happens:

Scenario 1: Date matches your setting

• Your setting: DD/MM/YYYY
• CSV value: 05/01/2026
• Result: Imports as January 5, 2026 ✓

Scenario 2: Date doesn’t match your setting (but is valid in another format)

• Your setting: DD/MM/YYYY
• CSV value: 05/17/2026
• Result: Affinity detects this is invalid as DD/MM/YYYY format (month 17 doesn’t exist) but valid as MM/DD/YYYY format, so it flips the structure and imports as May 17, 2026

Scenario 3: Ambiguous dates

• Your setting: DD/MM/YYYY
• CSV value: 05/01/2026
• Result: This is valid in BOTH formats. Affinity will interpret it according to your setting as January 5, 2026 - even if you meant May 1, 2026

This third scenario is why matching your CSV format to your Affinity setting is so critical. Ambiguous dates like 05/01/2026 will always be read according to your setting, which can lead to incorrect data if your CSV is in a different format.

Supported Date Formats

Affinity recognizes the following date formats during import:

Single-digit formats:

• M/D/YYYY (e.g., 1/5/2026)
• D/M/YYYY (e.g., 5/1/2026)
• M/D/YY (e.g., 1/5/26)
• D/M/YY (e.g., 5/1/26)

Double-digit formats:

• MM/DD/YYYY (e.g., 01/05/2026)
• DD/MM/YYYY (e.g., 05/01/2026)
• MM/DD/YY (e.g., 01/05/26)
• DD/MM/YY (e.g., 05/01/26)

Text month formats:

• D MMM YYYY (e.g., 5 Jan 2026) - This format works regardless of your date/time setting and eliminates ambiguity

Format key:

• M = Single digit month (1, 2, 3, … 12)
• MM = Double digit month (01, 02, 03, … 12)
• MMM = Abbreviated month text (Jan, Feb, Mar, … Dec)
• D = Single digit day (1, 2, 3, … 30, 31)
• DD = Double digit day (01, 02, 03, … 30, 31)
• YY = Two digit year (01 → 2001, 26 → 2026)
• YYYY = Four digit year (1995, 2026)

Import Validation and Warnings

During the import process, Affinity validates a sample of your date data (3 rows per column) to check for potential issues. You may see a warning message that reads: “Column contains invalid dates or is missing data, some recognized dates may be corrected automatically. Please proceed with caution.”

This warning appears when the sample dates don’t match any of the recognized formats listed above. Affinity will attempt to correct the dates automatically by flipping day/month values when appropriate. You should review your data carefully before starting the import.

Best Practices for Date Imports

1. Always check your Date/Time setting first - Before preparing your CSV, verify which format your Affinity account is set to use (MM/DD/YYYY or DD/MM/YYYY)
2. Use consistent formatting - Ensure all dates in your CSV use the same format throughout
3. Prefer four-digit years - YYYY format (2026) is clearer than YY format (26)
4. Use text months for clarity - The format “D MMM YYYY” (e.g., “5 Jan 2026”) works regardless of your date/time setting and eliminates ambiguity between MM/DD/YYYY and DD/MM/YYYY
5. Test with a small sample - If unsure, try importing a few rows first to verify dates are interpreted correctly
6. Remove Excel formulas - Export to CSV format, which strips formulas and leaves only the final date values

What Happens After Import

Whatever date format exists in your CSV is what will be imported into Affinity. The system doesn’t have context about Excel formulas or cell formatting - it only sees the final exported value in the CSV file.

Need to Fix an Import?

If your dates didn’t import as expected, you can use the Undo Import feature to reverse the import and try again with corrected date formatting. This allows you to fix any date formatting issues without having to manually clean up incorrect data.

Data Types in Affinity and their recommended formats

Data types and their formatting in Affinity can vary depending on the field type and whether they support single values or multiple values per cell. See below on how different data types are formatted when exported from Affinity, and how you should format the data in your CSV file before importing it into Affinity:

Data Type	Exported formats from Affinity	Recommended import formats into Affinity	Formats not accepted
Text(Single value only)	1 column with free-flowing text per cell-----------------------------Affinity was founded in 2014.	1 column with free-flowing text per cell------------------------------Affinity was founded in 2014.	HTML formatting will not convert, the tags will remain as is.------------------------<p> Affinity was founded in 2014. </p>
Number(Single Value)	1 column with 1 numeric value per cell (no commas included within the value)-----------------------------100000	1 column with 1 numeric value per cell (no commas allowed within the value)---------------------------------10000	Commas within values with 4 or more digits are not allowed------------------------10,000
Number(Multiple Values)	1 column with multiple numeric values per cell (no commas within each value, separated by semicolons)-----------------------------_2321.0; 321313.0; 1121125.0 _	1 column with multiple numeric values per cell (no commas allowed within each value and must be separated by semicolons)---------------------------------2321; 321313; 1121125	Commas within values with 4 or more digits are not allowed------------------------10,000; 25,000
Date(Single Value only)	1 column with 1 date per cell (format matches your Date/Time setting) ----------------------------- 4/25/21 or 25/4/21	Your CSV dates must match your Affinity Date/Time setting (MM/DD/YYYY or DD/MM/YYYY format). See the “Understanding Dates in the Data Importer” section above for complete details on date formatting. --------------------------------- Supported formats include: M/D/YYYY, D/M/YYYY, MM/DD/YYYY, DD/MM/YYYY, D MMM YYYY, and several others listed in the dates section. Tip: Use D MMM YYYY format (e.g., 5 Jan 2026) to eliminate ambiguity regardless of your setting	Dates in a format that doesn’t match your Affinity Date/Time setting may be imported incorrectly. ------------------------ If your setting is MM/DD/YYYY and your CSV contains 05/12/2021 in DD/MM/YYYY format, it will be imported as May 12, 2021 instead of December 5, 2021. Important: Ambiguous dates like 05/01/2026 will always be interpreted according to your setting.
Dropdown(Single Value)	1 column with 1 value per cell-----------------------------Tag1	1 column with 1 value per cell---------------------------------Tag1_OR_Tag1/Tag2 (as one value)OR_Tag1; Tag2_ (as one value)OR_Tag1, Tag2_ (as one value)	All formats are accepted
Dropdown(Multiple Values)	1 column with 2 or more values per cell, separated by semicolons-----------------------------Tag1; Tag2; Tag3	1 column with 2 or more values per cell, separated by semicolons---------------------------------Tag1; Tag2; Tag3	Any value with a forward slash, semicolon, or comma will be separated into multiple values------------------------TagAA/TagAB will be treated as two separate values: TagAA and TagAB
Ranked Dropdown(Single Value only)	1 column with 1 value per cell. Different from Dropdown (Single Value) because their ranking/hierarchy can be manually changed in Affinity.-----------------------------Top Tier	1 column with 1 value per cell. Their ranking/hierarchy can be manually changed in Affinity---------------------------------Top Tier in one cell, then Medium Tier in another cell	All formats are accepted
People (Single Value)	2 columns: Full Name and Email Address-----------------------------“Brian Lee” will be exported under the Full Name column, while “[e-mail]” will be exported under the Email Address column	Option 1 - Map these 2 columns: _Full Name column and Email Address column_OROption 2 - Map these 3 columns: First Name column, Last Name column, and Email Address column---------------------------------Option 1 - “Jane Doe” in the _Full Name _column and “[e-mail]” in the _Email Address _columnOROption 2 - “Jane” in the _First Name column and “Doe” in the Last Name _column, and “[e-mail]” in the Email Address column	“Brian Lee <[e-mail]>” as a value within one cell will not be accepted. The full name and email address must be separated into two columns. Full names that have more than 2 parts (e.g. a middle name) will not be accepted.------------------------“Brian J Lee” will not be accepted.
People (Multiple Values)	1 column: Full Name <Email Address> separated by semicolons per value-----------------------------“Brian Lee <[e-mail]>; Alan Kuang <[e-mail]>” within one cell	1 column: Full Name <Email Address> separated by semicolons.---------------------------------Jane Doe <[e-mail]>; John Doe <[e-mail]>OR Jane Doe; John Doe	Neither first names only nor middle names are accepted. ------------------------ Jane; John; Alex
**Organization **(Single Value)	2 columns: Company Name and Website-----------------------------“Uber” for the _Company Name _and “uber.com” for the Website	2 columns: Company Name and Website--------------------------------- 2 columns: Company Name and Website--------------------------------- “Uber” for the Company Name “uber.com” or “https://www.uber.com” for the Website	uber.com/ca/en
**Organization **(Multiple Values)	1 column: Company Name <website> separated by semicolons per value-----------------------------Uber <uber.com>; Airbnb <airbnb.com>	1 column: Company Name <website> separated by semicolons per cell. --------------------------------- Uber <uber.com>; Airbnb <airbnb.com>;	Uber.com; bnb.comUber.com, bnb.com uber.com/ca/en Uber uber.com ; Bnb bnb.com
Location (Single Value)	4 columns: Address, City, State, Country-----------------------------170 Columbus Ave, San Francisco, CA, USA	At least one of the following: Address, City, State, Country---------------------------------“170 Columbus Ave” for Address, “San Francisco” for City, “California” for State, and “United States” for Country	Zipcode, Apt #, or Room # are not accepted
Location (Multiple Values)	1 column with all attributes provided in the cell: Address, City, State, Country separated by semicolons for each value-----------------------------San Francisco, California, United States; New York, New York, United States	1 column with all attributes provided in the cell: Address, City, State, Country separated by semicolons for each value---------------------------------San Francisco, California, United States; New York, New York, United States	Zipcode, Apt #, or Room # are not accepted