If you already pre-flight CSVs before putting them on the wire to the Bulk Import API (/bulk/v1/leads.json), you won’t notice this li’l bug. We recommend at least these two pre-flight checks:
- ensure the lookup field —
email,id,sfdcContactId, etc. — is non-empty in every row - ensure field values are consistent with back-end data types, e.g. an Integer field can’t store a decimal or alphanumeric value
There are tons of other checks you should do on CSVs, but I like to confine the term “pre-flight” to stuff Marketo will throw an error on. (Other checks are business-level and won’t stop the row from importing, for better or worse!)
If you pre-flight, you usually get the all-clear from the Import Status endpoint (/bulk/v1/leads/batch/:importid.json) so you don’t need to fetch Import Failures (/bulk/v1/leads/batch/:importid/failures.json).
But say you don’t pre-flight and just upload CSVs as-is. Then it’s possible for a CSV to lookup on email yet have rows with empty email:
------WebKitFormBoundaryBQACkJZyaiIAXogC
Content-Disposition: form-data; name="file"; filename="leads.csv"
Content-Type: text/csv
firstName,lastName,phone,email
Joe,Hixie,212-555-1212,teknkl+test-import-01@figureone.com
Benji,Nordstrom,212-555-2323,
Dusty,Brix,212-555-3434,teknkl+test-import-03@figureone.com
Hank,Lovemore,212-555-4545,teknkl+test-import-04@figureone.com
------WebKitFormBoundaryBQACkJZyaiIAXogC--The exact payload above won’t cause any confusion. Import Status will return "numOfRowsFailed": 1 and Import Failures returns a parseable CSV to feed into your error handling code:
firstName,lastName,phone,Import Failure Reason
Benji,Nordstrom,212-555-2323,Value for lookup field 'email' not foundIf you reverse email and phone and leave that same value empty, you still get usable Failures:
------WebKitFormBoundaryBQACkJZyaiIAXogC
Content-Disposition: form-data; name="file"; filename="leads.csv"
Content-Type: text/csv
firstName,lastName,email,phone
Joe,Hixie,teknkl+test-import-01@figureone.com,212-555-1212
Benji,Nordstrom,,212-555-2323
Dusty,Brix,teknkl+test-import-03@figureone.com,212-555-3434
Hank,Lovemore,teknkl+test-import-04@figureone.com,212-555-4545
------WebKitFormBoundaryBQACkJZyaiIAXogC--firstName,lastName,phone,Import Failure Reason
Benji,Nordstrom,212-555-2323,Value for lookup field 'email' not foundThings get wacky, though, when you not only have a missing lookup value or other data error, you also have empty fields in the same row:
------WebKitFormBoundaryBQACkJZyaiIAXogC
Content-Disposition: form-data; name="file"; filename="leads.csv"
Content-Type: text/csv
firstName,lastName,email,phone
Joe,Hixie,teknkl+test-import-02@figureone.com,212-555-1212
,Nordstrom,,
Dusty,Brix,teknkl+test-import-02@figureone.com,
Hank,Lovemore,,
------WebKitFormBoundaryBQACkJZyaiIAXogC--Then, the response gets mangled:
lastName,Import Failure Reason
Nordstrom,Value for lookup field 'email' not found
Hank,Lovemore,Value for lookup field 'email' not found
Check the number of headers vs. the number of fields in data row 1 vs. row 2. Nordstrom was row 1’s lastName, yes, but Hank wasn’t row 2’s lastName, it was its firstName. That mangled response can’t be parsed to find the original offending row! Unfortunately, the API gets confused when error rows have empty fields.😦
To be clear: empty fields are totally valid in themselves and won’t cause a problem in the absence of fatal import errors. Also, the bug appears whether the empty field is double-quoted ,"", or simply empty ,, — as you should expect because those have exactly the same meaning in a CSV.
Truth is, even the non-mangled response CSV is suboptimal, IMO. It lacks a row number, so to seek the row in the original file you need to match every single field. That’s really inefficient if you already parsed a source CSV into an indexed collection (e.g. JS array) even if you didn’t pre-flight.[1]
Luckily, it’s easy to add a row number yourself, which patches over the bug while improving the Import Failures response in all cases. Add a Last Import Row Number (Integer) field in Marketo, and make that the first field in your CSV:
------WebKitFormBoundaryBQACkJZyaiIAXogC
Content-Disposition: form-data; name="file"; filename="leads.csv"
Content-Type: text/csv
lastImportRowNumber,firstName,lastName,email,phone
1,Joe,Hixie,teknkl+test-import-02@figureone.com,212-555-1212
2,,Nordstrom,,
3,Dusty,Brix,teknkl+test-import-02@figureone.com,
4,Hank,Lovemore,,
------WebKitFormBoundaryBQACkJZyaiIAXogC--Then the response reflects that field for easy seekin’:
lastImportRowNumber,lastName,Import Failure Reason
2,Nordstrom,Value for lookup field 'email' not found
4,Hank,Lovemore,Value for lookup field 'email' not foundThe response still has an irregular number of fields per line, but you only need the first field so your code won't break.
Notes
[1] To prepend lastImportRowNumber you must parse the CSV before creating the request payload. So in the unlikely-but-not-impossible case where you have a guaranteed pre-pre-flighted CSV from an outside source and want to just stream it into the payload (admittedly, the most efficient way to send a static file, as long as you skip the BOM) you can't do that anymore.