Introducing Pardot’s Import API

Ever wanted to insert hundreds, thousands, or even millions of prospects into Pardot, only to find the process difficult due to the limitations of the upsert functionality? In Winter ‘20, the Pardot API gets new functionality with the release of the Import API. The Import API provides a programmatic and asynchronous way to insert or update large numbers of prospects with minimal API calls.

What’s an “import”?

The Import API uses the term “import” as a basic unit of work for all records handled in one group. An import allows you to choose at what point to stop gathering record updates or creates and persist those changes to Pardot’s datastore.

An import is made up of batches of records. Each batch is sent as a CSV file. Imports can have up to ten batches, and batch size is limited to 10 MB. The number of records per batch is dependent on the number of fields in each record; the more fields per record, the larger the size of each record therefore the smaller number of records per batch.

When you finish adding batches to an import, you send a command to Import API to start importing records. The Import API reviews the batches and then scales the system to use available resources to efficiently process the insert and update operations. Batch order doesn’t guarantee processing order — the system processes the import in the most efficient way.

CSV format

Before we explain how to use the Import API, let’s discuss the CSV data format used by an import. The first line of the CSV file must include the API name of the fields that you’re importing. Each CSV file in an import must contain the same fields, in the same order. If a CSV file in a batch is different, the Import API rejects the file with an error.

To follow along with the examples in this post, create two CSV files using these following examples.

file1.csv

email,first_name,campaign_id
codey@salesforce.com,Codey,2341
astro@salesforce.com,Astro,2341

file2.csv

email,first_name,campaign_id
einstein@salesforce.com,Einstein,2341

To include a comma in a field value in the CSV file, wrap the value in quotes.

CSV Comma in Field example

email,first_name,campaign_id
einstein@salesforce.com,"Einstein, Genius",2341

The campaign_id field is the ID of a Campaign within the Pardot account.

Using the basic features of the Import API

Now that we have some prospects to import, let’s use the Import API!

First, log in to the Pardot API using your email, password and user_key to get your api_key. To find your user key, log in to Pardot through a browser and navigate to {your email address} > Settings. Copy the value next to API User Key.

We’ll be using Curl to access the API, which is a simple command-tool to execute HTTP requests and see responses. Most distributions of macOS and Linux have Curl preinstalled. However, on Windows, the installer can be downloaded from the Curl website.

curl -X POST \
  'http://pi.pardot.com/api/login/version/3?format=json' \
  -F email=email@email.com \
  -F 'password=secret' \
  -F user_key=0e7170d437c2e716528dad1eb66b6bec

Executing this Curl command returns a JSON response that contains the api_key for the current session. This api_key is used in the Authorization headers when calling the Pardot API.

Sample output from the previous Curl command

{
  "@attributes":{"stat":"ok","version":1},
  "api_key":"bb0ffb179a949ec143aa63961bb15791"
}

Now that you’re logged in and have your api_key, create an import by calling the create endpoint. This endpoint accepts a JSON body that modifies the import. The example below shows the minimum required parameters: operation and object. In Winter ‘20, the only supported operation is Upsert and the only supported object is Prospect.

curl -X POST \
  'http://pi.pardot.com/api/import/version/3/do/create/?format=json' \
  -H 'Authorization: Pardot user_key=0e7170d437c2e716528dad1eb66b6bec,api_key=bb0ffb179a949ec143aa63961bb15791' \
  -H 'Content-Type: application/json' \
  -d '{"operation":"Upsert", "object":"Prospect"}'

After execution, a JSON response is returned. The response contains the import’s unique ID, the URL to send batches to, and other properties that tell you the state of the import. For now, you only need the value of the batchesRef, which is where you’ll create batches by uploading CSV files.

{
    "@attributes":{"stat":"ok","version":1},
    "id":20,
    "state":"Open",
    "isExpired":false,
    "batchesRef":"http://pi.pardot.com/api/import/version/3/do/batch/id/20"
}

To add data to the import, use the CSV files shown earlier and call the batch endpoint with the ID of the import.

curl -X POST \
  'http://pi.pardot.com/api/import/version/3/do/batch/id/20' \
  -H 'Authorization: Pardot user_key=0e7170d437c2e716528dad1eb66b6bec,api_key=bb0ffb179a949ec143aa63961bb15791' \
  -F importFile=@file1.csv

When successful, the batch endpoint returns a status code of 204 with no body.

Add the second file by calling the same endpoint again.

curl -X POST \
  'http://pi.pardot.com/api/import/version/3/do/batch/id/20' \
  -H 'Authorization: Pardot user_key=0e7170d437c2e716528dad1eb66b6bec,api_key=bb0ffb179a949ec143aa63961bb15791' \
  -F importFile=@file.csv

In this example, you added two batches, but you can add up to ten batches to an import. When you’re done adding batches, update the import with the Ready state.

curl -X PATCH \
  'http://pi.pardot.com/api/import/version/3/do/update/id/20?format=json' \
  -H 'Authorization: Pardot user_key=0e7170d437c2e716528dad1eb66b6bec,api_key=bb0ffb179a949ec143aa63961bb15791' \
  -H 'Content-Type: application/json' \
  -d '{"state":"Ready"}'

After sending the request, the Import API returns a response, indicating that the import is processing, or that the import is waiting. In the following example, the response returned from the previous request shows the import with state equal to Waiting.

{
    "@attributes":{"stat":"ok","version":1},
    "id":20,
    "state":"Waiting",
    "isExpired":false
}

The state property of an import gives you the imports current status.

  • Open: The import has been created and is accepting batches.
  • Waiting: The import is waiting for processing resources to become available.
  • Processing: The batches are being processed.
  • Complete: All batches are processed.

To get the latest state of the import, use the read endpoint.

curl -X GET \
  'http://pi.pardot.com/api/import/version/3/do/read/id/20?format=json' \
  -H 'Authorization: Pardot user_key=0e7170d437c2e716528dad1eb66b6bec,api_key=bb0ffb179a949ec143aa63961bb15791'

Note that calling this API counts against API limits, so we recommend making calls only once per minute. After the import is complete, the response looks something like the following example:

{
    "@attributes":{"stat":"ok","version":1},
    "id":20,
    "state":"Complete",
    "isExpired":false,
    "createdCount": 3,
    "updatedCount": 0,
    "errorCount": 0
}

This response shows that the import was successful and that it created three records. If there were errors importing records, the response contains other properties and a URL to download a CSV containing records that failed and the failure reason.

{
    "@attributes":{"stat":"ok","version":1},
    "id":21,
    "state":"Complete",
    "isExpired":false,
    "createdCount": 1,
    "updatedCount": 0,
    "errorCount": 4,
    "errorsRef": "https://pi.pardot.com/api/import/version/3/do/downloadErrors/id/21"
}

Including a batch with Import Create

In the example, you executed four requests: create the import, add batch 1, add batch 2, and update the import state. Having each step of an import correspond to a request is simple to understand, but it’s not very efficient. The good news is that the create endpoint can also accept batches and set the state to Ready.

First, let’s take a look at creating an import with a batch using a single create call. This method is useful when the import has a single file, or to reduce the number of API calls by one.

curl -X POST \
  'http://pi.pardot.com/api/import/version/3/do/create/?format=json' \
  -H 'Authorization: Pardot user_key=0e7170d437c2e716528dad1eb66b6bec,api_key=bb0ffb179a949ec143aa63961bb15791' \
  -F 'importInput={"operation":"Upsert","object":"Prospect"}' \
  -F importFile=@file1.csv

The importInput field specifies the JSON body (just like the create shown in the previous example) and a second form field, importFile, which is the CSV file associated to the batch.

The result of executing the above request is exactly the same as calling the create and batch endpoints individually. This saves a request since the batch endpoint doesn’t need to be called the first batch. From here, use the API as before — add more batches if necessary and then change the import state to Ready.

If the import contains a single batch, you can further reduce the number of requests by setting the state property while creating the import.

curl -X POST \
  'http://pi.pardot.com/api/import/version/3/do/create/?format=json' \
  -H 'Authorization: Pardot user_key=0e7170d437c2e716528dad1eb66b6bec,api_key=bb0ffb179a949ec143aa63961bb15791' \
  -F 'importInput={"operation":"Upsert","object":"Prospect","state":"Ready"}' \
  -F importFile=@file1.csv

This request creates the import, adds the batch, and then sets the import to Ready within a single request. After an import is set to Ready, you can’t add more batches. If you need to add more than one file, don’t set the state until after you’ve added all the batches.

Conflict resolution

An advanced feature of the Import API changes how the API handles updates to existing records and how it handles empty, also known as null, values in CSV files.

When a row in the CSV file has a value and matches a row in Pardot that also has a value, the value in CSV file overwrites the value in Pardot. In some cases, you might want to ignore the value in the CSV and keep the Pardot value. To do this, use the columns property in the JSON body of the create endpoint. This optional property is an array of column options and all columns found in the first line of the CSV must be present in the array.

For example, an import includes three fields (email, first_name, last_name), and you don’t want the first and last names to overwrite the values in Pardot. To keep the Pardot values, specify column options as shown in this example:

{
    "operation":"Upsert",
    "object":"Prospect",
    "columns": [
        {"field": "email"},
        {"field": "first_name", "overwrite": false},
        {"field": "last_name", "overwrite": false}
    ]
}

This JSON body is specified during the request to the create endpoint. This example uses the columns array to specify that the first_name and last_name fields should not be overwritten. By setting “overwrite” to false, the First Name and Last Name fields on the prospect record won’t be overwritten in Pardot, unless they are blank.

But what if you want to overwrite most values but you just don’t want to overwrite values with blanks? By default, any blanks in the row of the CSV will update the Pardot value to a blank. Because every record in a CSV you’re importing must contain every field specified in the first line of the CSV, it can be a challenge to import optional fields, which may be blank.

Don’t worry though, you can use the nullOverwrite property change the default behavior. If nullOverwrite is specified as false, a blank value in a record doesn’t overwrite the Pardot value with a blank.

In this example, the import includes the email, first_name, and last_name fields. You only want to update last_name on prospect records if the import file has a value for the field. Here, you’d add the nullOverwrite property to the last_name column.

{
    "operation":"Upsert",
    "object":"Prospect",
    "columns": [
        {"field": "email"},
        {"field": "first_name"},
        {"field": "last_name", "nullOverwrite": false}
    ]
}

With this configuration, blanks in the last_name field of a row in the CSV won’t change the value of the matching prospect in Pardot.

You can combine overwrite and nullOverwrite on a field, and each field can have different settings depending on your use case. When neither conflict option is specified, the values in the CSV file will always overwrite Pardot values. To keep the Pardot values and ignore the values in the CSV when a conflict occurs, set both overwrite and nullOverwrite to false.

What’s next

There’s a lot more to talk about with the new Import API for Pardot releasing in Winter ’20, so head over to the technical documentation to read more.

Leave your comments...

Introducing Pardot’s Import API