2

I'm trying to use an SSJS script activity to upload a CSV file. Specifically, I'm using the Pardot Import API to create new Prospects.

I'm stuck on the add batch part. This requires the Content-Type to be multipart/form-data. I can use this route with Postman to upload a CSV file, but as you can't upload a CSV file using SSJS, I thought I would try hand-rolling my payload with boundary markers, like this example which uses the client-side XMLHttpRequest to manually create the multipart/form-data payload.

As there's an SSJS equivalent for XMLHttpRequest (Script.Util.HttpRequest), I followed the method, based on the payload that Postman makes (which I viewed in the console to see the raw payload).

Here's my SSJS code:

op = 'batch';

route = apiRoute(object, ver, op);
route += '/id/' + id;

url = pardotBaseUrl + route;

Write('\n\nbatch url: ' + url);

var batch = [{
 name: 'importFile',
 value: 'email,first_name\r\neliot.harper@sample.com,Eliot'
}]

var guid = Platform.Function.GUID();
guid = guid.replace('-', '');

var boundary = '--------------------------' + guid.substring(0,12);

var payload = multiPost(boundary,batch);
//payload = Stringify(payload); // yes, I tried this too

Write('\n\npayload:\n' + payload);

try {

var req = new Script.Util.HttpRequest(url);
req.emptyContentHandling = 0;
req.retries = 2;
req.continueOnError = false;
req.contentType = 'multipart/form-data; charset=utf-8; boundary=' + boundary;
req.setHeader('Content-Length', + payload.length);
req.setHeader('Authorization','Bearer ' + accessToken); 
req.setHeader('Pardot-Business-Unit-Id', pardotBUId);
req.setHeader('Accept', '*/*');
req.method = 'POST';
req.postData = payload;

var resp = req.send();

   Write('\n\nrespContent: ' + resp.content);
   Write('\n\nrespReturnStatus: ' + resp.returnStatus);
   Write('\n\nrespStatusCode: ' + resp.statusCode);   

} catch(e) {
Write('\n\rerror: ' + e );
}

function multiPost(boundary, formHash){
    var body = ''
    for(var key in formHash){
        body += boundary
             + '\r\nContent-Disposition: form-data; '
             + 'name="' + formHash[key].name + '"; '
             + 'filename="' + formHash[key].name + '.csv"'
             //+ '\r\nContent-Type: text/csv'
             + '\r\n\r\n' + formHash[key].value + '\r\n'
    }
    body += boundary + '--\r\n'

    return body;
}

this request returns the following response:

batch url: https://pi.demo.pardot.com/api/import/version/4/do/batch/id/798

payload:
--------------------------58eb929d90eb
Content-Disposition: form-data; name="importFile"; filename="importFile.csv"

email,first_name
eliot.harper@sample.com,Eliot
--------------------------58eb929d90eb--

error: An error occurred when attempting to evaluate a Util.HttpRequest call.  See inner exception for details.
  URL: https://pi.demo.pardot.com/api/import/version/4/do/batch/id/798
  Method: POST
  ClientID: 10958519
  JobID: 0
  ListID: 0
  BatchID: 0
  SubscriberID: 0

I've pretty much exhausted every permutation to get this to work. It appears that the CLR used by the intepreter doesn't like it when you try to craft a file upload. I appreciate that if you're reading this question, you probably won't be in a position to try this for yourself, but perhaps you could answer/comment if I've missed the obvious, or what you would try next?

4

After spending 2 more hours working with Macca on every single possible permutation, we didn't get this working, but did come up with a solution. Here's the lowdown:

  1. You need to ensure that the opening boundary is prefixed with a CRLF character when hand-rolling the payload
  2. Additional -- boundary prefix markers are required
  3. You need to set a Content-Length header (which I was doing). If you don't you will get a 'missing file' error.
  4. However, when you do set the Content-Length header, to the correct value, HttpRequest() still throws an error. We're still not sure why. Perhaps the CLR doesn't like you setting a Content-Length header value? At least, that's what it appears to be.

We exhausted our options with Script.Util.HttpRequest() and were not able to get this to work.

However, as we were able to correct the homemade payload, we could then use the HTTPPost Platform function to upload our 'file'.

Here's an example (obviously the payload is hard coded for example reasons, but I'm building string from DE records, so you would just push in additional comma separated rows when you do this programatically).

While Pardot does require a Content-Length header as I mentioned earlier, the HTTPPost Platform function drops it in there for you.

Here's some working code:

var object = 'import';
var ver = 4;
var op = 'batch';

var route = apiRoute(object, ver, op);

route += '/id/' + id; // get id from previous request

url = pardotBaseUrl + route;

var guid = Platform.Function.GUID();
guid = guid.replace('-', '');

var boundary = guid.substring(0,12);

var headerNames = ['Authorization', 'Pardot-Business-Unit-Id'];

var headerValues = ['Bearer ' + accessToken, pardotBUId];

var contentType = 'multipart/form-data; charset=utf-8; boundary=' + boundary

var result = [0];

var payload = '';

payload += '\r\n--' + boundary
payload += '\r\nContent-Disposition: form-data; name="importFile"; filename="importFile.csv"'
payload += '\r\n\r\nemail,first_name'
payload += '\r\neliot.harper@sample.com,Eliot'
payload += '\r\n--' + boundary + '--'

var statusCode = Platform.Function.HTTPPost(url, contentType, payload, headerNames, headerValues, result);

Write('\n\nstatusCode: ' + statusCode);


function apiRoute(object, version, operation) {
   return '/api/' + object + '/version/' + version + '/do/' + operation;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.