How to Properly Format and Upload CSV Files in Action Builder

Uploading data into Action Builder using CSV files allows you to efficiently import existing information about the people and entities you want to track across your campaigns. 

What is a CSV File?

A CSV (Comma-Separated Values) file is a simple and widely-used format for storing tabular data, such as spreadsheets or databases. Each line in a CSV file represents a single record, and each record consists of fields separated by commas. CSV files are plain text files, making them easy to create, edit, and import/export between different software applications.

Key Features of CSV Files:

• Simplicity: Easy to read and write using any text editor or spreadsheet software like Microsoft Excel or Google Sheets.
• Compatibility: Supported by most data management and analysis tools, making it a versatile choice for data exchange.
• Lightweight: Minimal file size compared to other formats like Excel, facilitating quick uploads and downloads.

In the context of Action Builder, CSV files are used to import large sets of data efficiently, ensuring that your campaigns have up-to-date and accurate information.

Understanding Unique Identifiers in Action Builder

Every record in Action Builder is assigned a unique identifier, which is crucial for accurately updating and managing your data. There are two types of unique IDs you can use:

• Action Builder ID: Automatically generated when a record is added to the system.
• Custom ID: Your own unique identifier, such as Member ID, Voter ID, Employee Number, etc.

Why Unique IDs Matter

Just like in any true database, the system relies on these unique IDs to:

• Identify Existing Records: When uploading data to update information about an entity, the system uses the unique ID to match and update the correct record.
• Prevent Duplicates: Unique IDs help ensure that existing records are updated rather than duplicated, maintaining data integrity.
• Link Data Accurately: They enable accurate data linking between different datasets and uploads.

Using Action Builder ID vs. Custom ID

• Action Builder ID: Ideal for updates when you have exported data from Action Builder and plan to re-import it after making changes.
• Custom ID: Useful when integrating data from external systems or databases that already have unique identifiers.

Tip: Consistently use the same type of unique ID across your uploads to maintain data integrity.

Preparing Your CSV File

Use the Correct File Format

• CSV Format: Ensure your data file is saved with a .csv extension. Action Builder requires CSV (Comma-Separated Values) files for uploads.
• Encoding: Use UTF-8 encoding to support special characters and prevent data corruption.

Include Column Headers

• Descriptive Headers: Your CSV should have a header row where each column is labeled with a descriptive header. These headers will be used during the data mapping process.
• Consistency: Use consistent naming conventions that match or closely resemble the field names in Action Builder to facilitate automatic mapping.

Clean Your Data

• Remove Duplicates: Check for and eliminate any duplicate records to prevent redundancy.
• Standardize Formats:
  • Dates: Use a consistent date format (e.g., YYYY-MM-DD).
  • Phone Numbers: Include country codes and use a standardized format.
  • Email Addresses: Verify that all email addresses are correctly formatted.
• Handle Missing Data: Fill in any missing essential information or decide how to handle records with incomplete data.

Special Characters and Formatting

• Escape Characters: Ensure that commas, quotation marks, and other special characters within data fields are properly escaped or enclosed in quotation marks.
• No Formulas or Formatting: Remove any spreadsheet formulas or special formatting before saving as CSV.
• No Carriage Returns: Ensure the file does not contain carriage returns (i.e., line breaks within data fields), as these can disrupt the upload process and cause errors. All data for each row should be contained on a single line in the CSV.

Updating Existing Records

If you need to update existing records and add new ones simultaneously, it's crucial to prepare your CSV file carefully to avoid creating duplicates.

Steps to Create a Comprehensive CSV File

1. Export Existing Data:

   • Export your current data from Action Builder, including the unique IDs (either Action Builder IDs or your Custom IDs).

2. Prepare New Records:

   • Compile the new records you want to add, ensuring each has a unique Custom ID if you're using custom identifiers.

3. Merge Data:

   • Combine the exported data and new records into a single CSV file.

4. Use Formulas to Identify Records:

   • Utilize spreadsheet formulas like VLOOKUP or MATCH to check if a Custom ID from the new records already exists in the exported data.

   • Example Formula (Excel):

     excel

      

     =IF(ISNA(MATCH(A2, ExistingIDs!$A$2:$A$1000, 0)), "New", "Existing")
     • This formula checks if the Custom ID in cell A2 exists in the range of existing IDs. It labels the record as "New" or "Existing" accordingly.

5. Assign Action Builder IDs:

   • For existing records, ensure the corresponding Action Builder ID is included.
   • For new records, leave the Action Builder ID field blank.

6. Finalize the CSV File:

   • Double-check all data fields for accuracy.
   • Remove any helper columns or formulas before saving the CSV file.

Tips for Avoiding Duplicates

• Consistent Unique IDs: Always use the same Custom ID for an entity across all uploads.
• Data Validation: Use spreadsheet tools to highlight duplicate Custom IDs within your dataset.
• Test Upload: Before uploading the entire file, test with a small subset to ensure the process works as expected.
• **Use the Bulk Edit Feature**: Update records in bulk without using the uploader by simply using the Bulk Edit feature located on the campaign Wall Chart. Learn more about Bulk Edit.

Example Record Fields

To provide a practical understanding, here's an example of how these fields might be populated for a single record:

• Action Builder ID: 681c0d43-23d8-432f-8de1-508208796279
• Custom ID: custom_id_123
• First Name: Janet
• Last Name: Doe
• Email: [e-mail]
• Phone Number: 555-555-5555
• Street Address: 1600 Pennsylvania Ave.
• City: Washington
• State: DC
• Postal Code: 20500
• Pronouns: she/her
• Assessment: 2
• Age: 30
• DOB: 1993-06-01
• Preferred Language: en
• Note: Here is a note about Jane.
• Note Date: 2023-09-01
• Follow Up Note: Here is a follow-up note about Jane.
• Email Type: home
• Email Subscribed: TRUE
• Email Status: do_not_contact
• Phone Number Type: work
• Phone Number Subscribed: FALSE
• Street Address: 1600 Pennsylvania Ave.
• City: Washington
• State: DC
• Postal Code: 20500
• Geocode Bad: FALSE
• Address Status: bad
• Discord Handle: janeDiscord
• Facebook Handle: FacebookJane
• Instagram Handle: InstaJane
• LinkedIn Handle: LinkedInJane
• TikTok Handle: TikTokJane
• Twitter Handle: TwitterJane
• Website: https://jane.com
• WeChat Handle: WeChatJane
• WhatsApp Handle: WhatsAppJane
• Other Social Profile URL: https://othernetwork.org/jane
• Other Social Profile Label: Other Network
• Standard Field Response: volunteer
• Notes Field Response: house party
• Notes Field Note Content: Willing to host a house party.
• Date Field Response: Join Date
• Date Fields Date Content: 2023-09-01
• Number Field Response: Children
• Number Field Number Content: 3
• Address Field Response: DC Headquarters
• Address Field Street: 1905 L Street NW
• Address Field City: Washington
• Address Field State: DC
• Address Field Postal Code: 20036
• Connected Entity ID: b8614b6f-eae0-4a10-bc83-b6d2fb1e0dc6
• Shift Field Response: day shift
• Shift Field Timezone: America/New_York
• Shift Field Monday Shift to Shift Field Friday Shift: 09:00-17:00 (for each day)

Tips:

• Ensure that each column you intend to upload corresponds to a field in Action Builder.
• Use unique identifiers like Membership ID to prevent duplicate entries when updating records.

Uploading Your CSV File

Accessing the Uploader (Admin Users Only)

1. Navigate to the Uploads section within your Admin Menu.
2. Click on New Upload to begin the process.

Upload Settings

• Select Campaign: Choose the campaign to which you want to upload the data.
• Upload Mode:
  • Standard Mode: Adds new records or updates existing ones.
  • Delete Mode: Used for mass deletion (more on this below).

Read More: How to Upload to Action Builder

Data Mapping

Automatic vs. Manual Mapping

• Automatic Mapping: If your CSV headers match Action Builder fields, the system may automatically map them.
• Manual Mapping: Review each field to ensure correct mapping. Adjust as necessary.

Mapping Fields

• Standard Fields: Map your CSV columns to standard fields like Name, Email, Phone Number, etc.
• Custom Fields: If you've created custom fields in Action Builder, ensure these are included and correctly mapped.

Tips for Effective Mapping

• One-to-One Mapping: Each CSV column should map to only one field in Action Builder.
• Unmapped Columns: Any columns not mapped will not be imported.

Response Mapping for Info Fields

Understanding Info Fields

Most of your fields will only accept specific predefined responses. This standardizes data collection and allows you to build lists based upon common responses.

Ensuring Data Integrity

• Exact Matches: The values in your CSV must match exactly the acceptable responses in Action Builder.
• Response Mapping Settings: Use these settings to align any discrepancies between your CSV data and the accepted responses.
  • Example: If Action Builder accepts "Yes" or "No" and your CSV has "Y" or "N," map "Y" to "Yes" and "N" to "No."

Mass Deletion with Delete Mode

When to Use Delete Mode

Use Delete Mode to permanently delete multiple records across all campaigns.

Preparing for Deletion

• Create a Deletion CSV: Your CSV should only contain the ID numbers (Action Builder IDs) of the entities you wish to delete.
• Single Column: Place all IDs in a single column, labeled appropriately.

Executing the Deletion

1. Select Delete Mode in the uploader before uploading your CSV.
2. Follow the prompts to confirm the permanent deletion of the records.

After Uploading

Review Upload Summary

• Success and Errors: After the upload, Action Builder will provide a summary of successfully imported records and any errors.
• Error Handling: If there are errors:
  • Download Error Report: This report will detail why certain records failed to upload.
  • Make Corrections: Fix the issues in your CSV and re-upload if necessary.

Verify Data in Action Builder

• Spot Check Records: Randomly check some records to ensure data has been imported correctly.
• Check Mapped Fields: Ensure all fields, especially custom and info fields, display the correct information.