Welcome, guest | Sign In | My Account | Store | Cart

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.

Python, 380 lines
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
#!/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')