Cleaning up your data before importing into Affinity
Importing data into Affinity can be tricky, especially when everyone has their own way of formatting data in their CSV files. This guide will help you format your CSV file data correctly 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 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 for 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 (in MM/DD/YY format) ----------------------------- 4/25/21 |
MM/DD/YYYY format required. If your CSV file has DD/MM/YYYY format, please convert it to MM/DD/YYYY before starting the import |
DD/MM/YYYY format is not allowed ------------------------ If your CSV file is set to DD/MM/YYYY, the date 05/12/2021 will not be treated as December 5, 2021, but as May 12, 2021 |
|
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 |
2 columns: Full Name and Email Address ----------------------------- “Brian Lee” will be exported under the Full Name column, while “brian.lee@test.com” will be exported under the Email Address column |
Option 1 - Map these 2 columns: Full Name column and Email Address column OR Option 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 “janedoe@gmail.com” in the Email Address column OR Option 2 - “Jane” in the First Name column and “Doe” in the Last Name column, and “janedoe@gmail.com” in the Email Address column |
“Brian Lee <brian@affinity.co>” 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 |
1 column: Full Name <Email Address> separated by semicolons per value ----------------------------- “Brian Lee <brian.lee@test.com>; Alan Kuang <alan.kuang@test.com>” within one cell |
1 column: --------------------------------- Jane Doe <janedoe@gmail.com>; John Doe <johndoe@gmail.com> OR |
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 ---------------------------------
|
uber.com/ca/en |
|
Organization (Multiple Values) |
1 column: ----------------------------- Uber <uber.com>; Airbnb <airbnb.com> |
1 column: |
Uber.com; bnb.com
Uber.com, bnb.com uber.com/ca/en Uber uber.com ; Bnb bnb.com |
|
Location |
4 columns: ----------------------------- 170 Columbus Ave, San Francisco, CA, USA |
At least one of the following: --------------------------------- “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 |
1 column with all attributes provided in the cell: ----------------------------- San Francisco, California, United States; New York, New York, United States |
1 column with all attributes provided in the cell: --------------------------------- San Francisco, California, United States; New York, New York, United States |
Zipcode, Apt #, or Room # are not accepted |