#!/usr/bin/env python
# coding: utf-8
"""
Assumption: Input Excel Spreadsheet with these columns:
"Date","Company","Symbol","First Name","Last Name",
"Title","Email","Notes", "Owner First Name", "Owner Last Name"
Normally you should try to use the Salesforce import Wizard or
Apex Data Loader for importing. This script demonstrates that
programmatic import can handle complex conditional logic that
the Import Wizard or Data Loader cannot anticipate.
* Apex Data Loader requires CSV file, does not directly deal with Excel
* Apex Data Loader only loads one object type per/run.
* The mapping of input columns to object fields is static/declarative (normally this is good,
but not flexible)
* You cannot map an Account owner by name
Other issues raised that this script (scripting, in general) can handle:
http://success.salesforce.com/ideaView?c=09a30000000D9xt&id=08730000000Bq30AAC
http://success.salesforce.com/ideaView?c=09a30000000D9xt&id=08730000000BrdUAAS
This script uses the Salesforce (web services) API, which is only available to Unlimited,
Enterprise and Developer users, although I understand the API can purchased
separately.
Module Dependencies:
sforce, salesforce-python-toolkit, http://code.google.com/p/salesforce-python-toolkit/
suds, versions 0.3.6 to 0.3.9 ONLY (i.e. 0.4.0 WON'T WORK) Python SOAP API, https://fedorahosted.org/suds
xlrd, Python MS-Excel reader, http://www.python-excel.org
xlwt, Python MS-Excel writer, http://www.python-excel.org
Copyright (c) 2011 Chris Wolf cw10025 gmail
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in
all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
THE SOFTWARE.
"""
#-------------------- user configuration section, edit as needed ----
# WSDL generated from setup->Develop->API->Generate Enterprise WSDL
wsdlFile=r'enterprise.wsdl'
username='sforceuser'
password='sforcepw'
sectoken='xxxxxxxxxxxxxxxxx' #security token
# when these are False, an entry is created in the exception report
createMissingAccounts = True
createMissingContacts = True
#--------------------------------------------------------------------
import sys, xlrd, datetime
from xlwt import Workbook, easyxf, XFStyle
from sforce.enterprise import SforceEnterpriseClient
# missing ternary expression in Python < 2.5
tern=lambda a,b,c: (b,c)[not a]
class ImportException(Exception):
def __init__(self, msg, sheetName, errRow):
self.msg = msg
self.sheetName = sheetName
self.errRow = errRow
def __str__(self):
return repr("%s: '%s', row %d" % self.msg, self.sheetName, self.errRow)
#----- globals ----
# Errors XLS
errorsWorkbook = None
erroutSheet = None
erroutRow = 0
# Import XLS
importSheet = None
importRow = 0
invokingUser = None
headers = []
# Salesforce connection handle, initialized by login
sf = None
# set username, password and sectoken near top of this file
def loginToSalesforce(username, password, sectoken):
"""
Initializes global Salesforce connection handle, "sf"
"""
global sf
sf = SforceEnterpriseClient(wsdlFile)
# API login seems to always need security token
result = sf.login(username, password, sectoken)
print 'Connected to "%s"' % result.serverUrl
print ' as user "%s" Org Company Name: "%s" Org Id: "%s"' % \
(result.userInfo.userFullName,
result.userInfo.organizationName,
result.userInfo.organizationId)
print " org type is %s sandbox" % (tern(result.sandbox,"","NOT"))
global invokingUser
invokingUser = result.userInfo.userFullName
def configErrorReporting(headers):
"""
Configure import exception log, which is an Excel spreadsheet in the same
format as the input format, but with an extra column added - "Error",
which contains the error message.
Can only be called after first row of input Excel spreadsheet is read
to initialize the global, "headers"
"""
dateFmt = easyxf(
'font: name Arial, bold True, height 200;',
#'borders: left thick, right thick, top thick, bottom thick;',
num_format_str='MM-DD-YYYY'
)
headerFmt = easyxf(
'font: name Arial, bold True, height 200;',
)
global errorsWorkbook, erroutSheet, erroutRow
errorsWorkbook = Workbook()
erroutSheet = errorsWorkbook.add_sheet('Import Errors')
for colnum in range(0, len(headers)):
erroutSheet.write(0, colnum, headers[colnum][0],
tern(headers[colnum][0]==xlrd.XL_CELL_DATE, dateFmt, headerFmt))
# Add extra column for error message
erroutSheet.write(0, len(headers), "Error", headerFmt)
erroutSheet.flush_row_data()
erroutRow = 1
errorsWorkbook.save('errors.xls')
def writeErrorRec(record):
"""
Exception report spreadsheet output format same as input format and
assumes first column is of type Date - adds an extra, last column
for error message.
"""
dateFmt = XFStyle()
dateFmt.num_format_str='MM-DD-YYYY'
defaultFmt = XFStyle()
global erroutRow
for colnum in range(0, len(headers)):
erroutSheet.write(erroutRow, colnum, record[headers[colnum][0]],
tern(headers[colnum][0]==xlrd.XL_CELL_DATE, dateFmt, defaultFmt))
# add extra column for error message
erroutSheet.row(erroutRow).write(len(headers), record['Error'])
erroutRow += 1
# flush every 200 rows...
#if ((erroutRow % 200) + 1 == 200):
# since, xlwt uses aggressive caching, we must
# write each row manually, or the data may be lost upon exception
erroutSheet.flush_row_data()
errorsWorkbook.save('errors.xls')
def createContact(record):
"""
Create a partially populated Contact record, associated with the
Account ("Company") specified in the Excel record.
"""
global importSheet, importRow
contact = sf.generateObject('Contact')
contact.AccountId = record['AccountId']
contact.FirstName = record['First Name']
contact.LastName = record['Last Name']
contact.Title = record['Title']
contact.Email = record['Email']
contact.Description = record['Notes']
result = sf.create(contact)
if result.success != True:
msg = "Error: Can't create Contact record for %s, result was %s" % (str(record), result)
raise ImportError(msg, importSheet, importRow)
print "New Contact record for %s, %s, Id = %s" \
% (contact.LastName, contact.FirstName, result.id)
return result
def createAccount(record):
"""
Create a partially populated Account record, corresponding to the
Company specified in the Excel record.
"""
global importSheet, importRow
ownerId = None
setOwner = False
if record['Owner First Name'] != None and len(record['Owner First Name']) > 0 and \
record['Owner Last Name'] != None and len(record['Owner Last Name']) > 0:
setOwner = True
if setOwner:
result = sf.query(
u"select Id from User where FirstName = '%s' and LastName = '%s'"
% (record['Owner First Name'].replace(u"'", u"\\'") , record['Owner Last Name'].replace(u"'", u"\\'"))
)
if result.size > 1:
print "Warning: Multiple users with name \"%s %s\" - using first found." \
% (record['Owner First Name'], record['Owner Last Name'])
elif result.size == 1:
ownerId = result.records[0].Id
else:
global invokingUser
print "Warning: no owning User found for \"%s %s\" - owner will be \"%s\" " \
% (record['Owner First Name'], record['Owner Last Name'], invokingUser)
account = sf.generateObject('Account')
account.Name = record['Company']
account.TickerSymbol = record['Symbol'] # just used to mark records for testing
if (setOwner and ownerId != None):
account.OwnerId = ownerId
result = sf.create(account)
if result.success != True:
global importSheet, importRow
msg = "Error: Can't create Account record for %s, result was %s" % (str(record), result)
raise ImportError(msg, importSheet, importRow)
print "New Account record for %s, Id = %s" % (account.Name, result.id)
return result
def handleAccountContact(record):
"""
Input to this function is a single record from the import data
Excel file. Here, we perform two queries, one to get the AccountId
and one to get the ContactId, then we add these as new fields to
the record and pass it back to the caller.
If the Account does not exit - create it, if the contact does not
exist, then create that too.
"""
global createMissingAccounts
accountId = None
result = sf.query(u"select Id, Name from Account where Name = '%s'" % (record['Company']).replace(u"'", u"\\'"))
if result.size > 1:
#r['Error'] = 'dup accounts'
print "Warning: more then one Account record for %s - using first one" % record['Company']
#writeErrorRec(record)
#return None
accountId = result.records[0].Id #.encode('ascii', 'ignore')
if result.size < 1:
if createMissingAccounts == True:
accountId = createAccount(record).id
else:
record['Error'] = 'no account'
print "Error: no Account record for %s" % record['Company']
writeErrorRec(record)
return None
else:
accountId = result.records[0].Id
record['AccountId'] = accountId
# try contact lookup via email...
if record['Email'] != None and len(record['Email']) > 0:
result = sf.query(
u"""
select Id, FirstName, LastName, Title, Email from Contact
where Email = '%s'
""" % (record['Email'])
)
else:
result = None
# ...if none, try contact lookup via first & last name
if result == None or result.size < 1:
result = sf.query(
u"""
select Id, FirstName, LastName, Title, Email from Contact
where FirstName = '%s' and LastName = '%s'
""" % (record['First Name'].replace(u"'", u"\\'") , record['Last Name'].replace(u"'", u"\\'") )
)
if result.size == 1:
record['ContactId'] = result.records[0].Id
elif result.size > 1:
print result
print "Warning: more then one Contact record for %s, %s %s - using first found." \
% (record['Last Name'], record['First Name'], record['Email'])
record['ContactId'] = result.records[0].Id
else:
if createMissingAccounts == True:
newContactResult = createContact(record)
record['ContactId'] = newContactResult.id
else:
record['Error'] = 'no contact'
print "Error: no Contact record for %s, %s %s" \
% (record['Last Name'], record['First Name'], record['Email'])
writeErrorRec(record)
return None
return record
def importData(fileName):
"""
Main entry point - opens import data Excel sheet (assumed to be first
sheet) and processes each record, one at a time.
"""
global importSheet, importRow, headers
book = xlrd.open_workbook(fileName)
importSheet = book.sheet_by_index(0)
print "importing from %s" % importSheet.name
# get cell value types from first-non header data row (2nd), since headers are all of type text
for colnum in range(0, importSheet.ncols):
headers.append((importSheet.cell(0, colnum).value, importSheet.cell(1, colnum).ctype))
configErrorReporting(headers)
for importRow in range(1, importSheet.nrows):
record = {}
for colnum in range(0, importSheet.ncols):
if headers[colnum][1] == xlrd.XL_CELL_DATE:
dateTuple = xlrd.xldate_as_tuple(importSheet.cell(rowx=importRow, colx=colnum).value, book.datemode)
date = datetime.date(dateTuple[0], dateTuple[1], dateTuple[2])
# required format for xsd:Date type, for web service call
record[headers[colnum][0]] = date.isoformat()
else:
value = importSheet.cell_value(rowx=importRow, colx=colnum)
if isinstance(value, basestring):
record[headers[colnum][0]] = value.strip()
else:
record[headers[colnum][0]] = value
#print "%s: %s" % (type(record[headers[colnum]]), record[headers[colnum]])
record = handleAccountContact(record)
book.unload_sheet(importSheet.name)
if len(sys.argv) != 2:
print "%s: Error: must pass Excel file name argument on command line" % sys.argv[0]
print "e.g.: %s importdata.xls" % sys.argv[0]
sys.exit(1)
#configErrorReporting()
# old-style exception handling because MacOSX only has Python-2.4
try:
try:
loginToSalesforce(username, password, sectoken)
importData(sys.argv[1])
erroutSheet.flush_row_data()
errorsWorkbook.save('errors.xls')
except KeyboardInterrupt:
print "Abort requested by user..."
except ImportException, e:
print e
raise # re-raise for stacktrace
except:
print "Error, caught %s: %s " % (sys.exc_info()[0], sys.exc_info()[1])
raise # re-raise for stacktrace
finally:
if errorsWorkbook != None and erroutSheet != None:
erroutSheet.flush_row_data()
errorsWorkbook.save('errors.xls')
Diff to Previous Revision
--- revision 1 2011-01-21 17:39:28
+++ revision 2 2011-01-21 17:43:35
@@ -58,9 +58,9 @@
# WSDL generated from setup->Develop->API->Generate Enterprise WSDL
wsdlFile=r'enterprise.wsdl'
-username='cwolf@sforce.net'
-password='T0uchd0wn'
-sectoken='nvF7y35YSDO9YAQTF9X4ixkRa'
+username='sforceuser'
+password='sforcepw'
+sectoken='xxxxxxxxxxxxxxxxx' #security token
# when these are False, an entry is created in the exception report
createMissingAccounts = True
