Skip to content

CSV Export Guide

This guide shows you how to export data from the Affinity CLI to CSV format for use in spreadsheets, data analysis tools, or other applications.

Quick Start

Export data to CSV using the --csv flag:

# Export all people to CSV
xaffinity person ls --all --csv people.csv

# Export all companies to CSV
xaffinity company ls --all --csv companies.csv

# Export all opportunities to CSV
xaffinity opportunity ls --all --csv opportunities.csv

# Export list entries with custom fields
xaffinity list export 12345 --all --csv entries.csv

Excel Compatibility

If you're opening CSV files in Microsoft Excel, use the --csv-bom flag to ensure proper character encoding:

xaffinity person ls --all --csv people.csv --csv-bom

This adds a UTF-8 Byte Order Mark (BOM) to the file, which helps Excel correctly display special characters, accents, and non-English text.

Commands with Built-in CSV Support

Command CSV Flag Example
person ls ✅ --csv xaffinity person ls --all --csv people.csv
company ls ✅ --csv xaffinity company ls --all --csv companies.csv
opportunity ls ✅ --csv xaffinity opportunity ls --all --csv opps.csv
list export ✅ --csv xaffinity list export 12345 --all --csv entries.csv

Note: The --csv flag requires --all to fetch all pages of data. Single-page exports are not supported for CSV output.

CSV Column Reference

person ls

The person ls command exports these columns:

  • id - Person ID
  • name - Full name (first + last)
  • primaryEmail - Primary email address
  • emails - All email addresses (semicolon-separated)

Example output: 

id,name,primaryEmail,emails
123,Alice Smith,alice@example.com,alice@example.com
456,Bob Jones,bob@company.com,bob@company.com; bjones@company.com

company ls

The company ls command exports these columns:

  • id - Company ID
  • name - Company name
  • domain - Primary domain
  • domains - All domains (semicolon-separated)

Example output: 

id,name,domain,domains
100,Acme Corp,acme.com,acme.com
101,Beta Inc,beta.com,beta.com; beta.co

opportunity ls

The opportunity ls command exports these columns:

  • id - Opportunity ID
  • name - Opportunity name
  • listId - List ID the opportunity belongs to

Example output: 

id,name,listId
10,Series A,41780
11,Seed Round,41780

list export

The list export command is the most powerful CSV export option. It includes:

  • Entity ID and name
  • All custom field values
  • List entry metadata

See xaffinity list export --help for details.

Advanced: Using jq for Custom CSV Exports

For commands without built-in --csv support, you can use jq to convert JSON output to CSV format.

Basic Pattern

xaffinity <command> --json --all | \
  jq -r '.data.<entity>[] | [.field1, .field2, .field3] | @csv' > output.csv

The -r flag is crucial - it outputs raw strings instead of JSON-quoted values.

Examples

Export field values: 

xaffinity field-value ls --field-id field-12345 --json | \
  jq -r '.data.fieldValues[] | [.fieldId, .value, .entityId] | @csv'

Export notes: 

xaffinity note ls --person-id 123 --json --all | \
  jq -r '.data.notes[] | [.id, .content, .createdAt] | @csv'

Export interactions: 

xaffinity interaction ls --person-id 123 --json --all | \
  jq -r '.data.interactions[] | [.id, .date, .type] | @csv'

Add headers manually: 

xaffinity person ls --json --all | \
  jq -r '["ID","Name","Email"],
         (.data.persons[] | [.id, .name, .primaryEmail]) | @csv'

Handle arrays (join with semicolons): 

xaffinity person ls --json --all | \
  jq -r '.data.persons[] | [.id, .name, (.emails | join("; "))] | @csv'

Extract from nested structures: 

xaffinity person get 123 --json | \
  jq -r '.data.person.fields[] | [.fieldId, .value, .listEntryId] | @csv'

JSON Data Structure Reference

All CLI commands return data in a consistent structure:

{
  "data": {
    "<entity-plural>": [ ... ]
  }
}

Entity paths for jq: - persons: .data.persons[] - companies: .data.companies[] - opportunities: .data.opportunities[] - fieldValues: .data.fieldValues[] - fieldValueChanges: .data.fieldValueChanges[] - notes: .data.notes[] - interactions: .data.interactions[] - tasks: .data.tasks[]

Troubleshooting

Empty Output

Make sure you're accessing the correct JSON path:

# ❌ Wrong - missing .data
xaffinity person ls --json | jq '.persons'

# ✅ Correct
xaffinity person ls --json | jq '.data.persons'

CSV shows JSON strings

Use the -r flag with jq:

# ❌ Wrong - produces "[1,\"Alice\"]"
xaffinity person ls --json | jq '.data.persons[] | [.id, .name] | @csv'

# ✅ Correct - produces "1,Alice"
xaffinity person ls --json | jq -r '.data.persons[] | [.id, .name] | @csv'

Special characters broken in Excel

Use the --csv-bom flag:

xaffinity person ls --all --csv people.csv --csv-bom

Empty CSV file has no headers

This is expected behavior when there are no results. The CLI cannot determine column names without data. If you need headers even for empty results, use list export which has a known schema.

Tips and Best Practices

1. Use --all for complete exports

The --csv flag requires --all to fetch all pages:

# ✅ Correct
xaffinity person ls --all --csv people.csv

# ❌ Won't work
xaffinity person ls --csv people.csv

2. Combine with filters

Filtering on custom fields (recommended):

When filtering on custom fields, use --filter for server-side filtering. This is more efficient as Affinity filters the data before sending it:

# ✅ Efficient: Server-side filtering on custom field
xaffinity person ls --filter 'Department = "Sales"' --all --csv sales-people.csv

You can also combine --filter with jq for additional client-side processing:

# Filter server-side, then process with jq
xaffinity person ls --filter 'Department = "Sales"' --json --all | \
  jq -r '.data.persons[] | [.id, .name, .primaryEmail] | @csv'

Filtering on built-in properties:

Built-in properties like type, firstName, primaryEmail, etc. cannot be filtered using --filter (which only works with custom fields). Use jq for client-side filtering:

# ⚠️ Less efficient: Client-side filtering on built-in 'type' property
# (downloads all data, then filters locally)
xaffinity person ls --json --all | \
  jq -r '.data.persons[] | select(.type == "internal") | [.id, .name] | @csv'

Combining both approaches:

For complex scenarios, combine server-side custom field filtering with client-side built-in property filtering:

# Filter on custom field server-side, then filter on type client-side
xaffinity person ls --filter 'Department = "Sales"' --json --all | \
  jq -r '.data.persons[] | select(.type == "internal") | [.id, .name] | @csv'

3. Save queries as scripts

Create reusable export scripts:

#!/bin/bash
# export-pipeline.sh

xaffinity person ls --all --csv people.csv --csv-bom
xaffinity company ls --all --csv companies.csv --csv-bom
xaffinity opportunity ls --all --csv opportunities.csv --csv-bom

echo "Export complete!"

4. Schedule regular exports

Use cron or task scheduler for automated exports:

# Daily export at 2 AM
0 2 * * * /path/to/export-pipeline.sh

5. Handle large datasets

For very large exports, monitor progress:

# The CLI will show API call counts for large exports
xaffinity list export 12345 --all --csv large-export.csv

Getting Help

  • Run xaffinity <command> --help to see all available options
  • Check xaffinity --version to ensure you have the latest version
  • Report issues at https://github.com/anthropics/affinity-api-x/issues