Advanced Import Data File Best Practices

Robust data collection about your supporters is preferable to limited data collection. When producing the data file in CSV file format for Advanced Import into Salsa CRM, it's helpful to know these items when formatting your data.

General Information

• As a final step of the process, you must save your data file in comma-delimited format. Save your file as a Comma Separated Values (.csv) for Mac and CSV (Comma delimited) for Windows.
  Do NOT save it on a Mac as CSV UTF-8 (Comma Delimited) (.csv) nor Macintosh Comma Separated (.csv) nor MS-DOS Comma Separated (.csv).
  
• You must label your data columns in your file with the same spelling as what the field is called in Salsa CRM, and spelling counts! For example, US address ZIP codes are stored in the Postal Code field. The Column Definitions table contains the exact spellings you'll need in the Column Name column. To help you to decipher what is required and what is optional, here are some templates for Constituent Import and Donation Import.
  
• Import ID copies your old system ID into Salsa CRM and stores this in the Constituent record.
• Vendor Name and Import ID go hand-in-hand to identify the unique nature of the record you are importing. Vendor Name should be the name of the organization that is supplying the data you are importing. Further imports using the same Vendor Name and Import ID would then tie incoming values to existing constituents.
• When you Process the source file, the system scans the file and pre-check for errors. If there are fewer than 50 errors the system will allow you to proceed, and you will just have to fix the errors in the Resolution Queue process later on. If you have more than 50 errors, you'll have to go back to your source file and fix the errors.

Constituent Data File Best Practices

• When populating the file that you are going to import into Salsa CRM, each unique constituent record may have an Import ID but is not required. If you include this Import ID, this becomes a field that is unique to the constituent and can be used on future imports to indicate exactly who the imported record refers to. Typically, this is the Source ID issued by the vendor (i.e. Jenzabar), but it is not Salsa CRM's Constituent ID. If you are importing a file that you have created manually and not from a vendor such as Jenzabar, it is not necessary to create Import IDs.
• Individual records must have a First Name and Last Name. Do not import blank last name columns as Individuals. The import will be rejected.

• Do not put both first and last names in one name field or the other. Split them into separate first and last names.

• Do not use fake/made-up first or last names like 'Unknown' or symbols like underscores or question marks in place of real names.

• Do not use fake email addresses if you don't know someone's email. It will negatively impact your delivery reputation if sent to a valid domain.

• Salsa CRM recycles some fields. The Last Name of Individuals and the full Organization Name for Organizations are stored in the same field in the database:  Last or Org Name. Check Numbers, Other Payment Type Descriptions, and Credit Card Authentication Codes are all stored in the same field.  It's very efficient. 
• We only support the ASCII subset of the UTF-8 encoding scheme. There should be no special characters or foreign letters in your data file, such as (but not limited to) $ % ^ & * ¢ £ ¥ € ∫ ½ ÷ © ® § Á Ë Î Õ Ñ Ü.
• No leading, trailing, or excessive spaces in your field values.
• If you are importing a mix of individuals and organizations, add a required column Constituent Type with values "I" for Individuals and (letter) "O" for Organizations.
• Organization Records must have the full name of the organization in the Last or Org Name field and the "O" Constituent Type.
• When you are importing constituent information only, make sure you do not have any donation or gift-related columns in the import file.
• Addresses need to be complete, with Address Line 1, City, State, and Postal Code. All must be present; none may be missing or it won't be imported. A partial address is not valuable data.
• In general, we try to follow the ISO-3166-2 Country subdivision code for the state or province codes.
  • United States is US.
  • England's country code is GB; not UK. 
  • For provinces/states, you would use OXF for Oxfordshire, Great Britain, or 401-Bundibugyo for Uganda. When in question, you can look in the City File for the code associated with the country or province in question. In most instances, N/A also works.
• Postal codes will need to be commensurate with the country the address is in.  For instance, US postal codes are five digits long. Canadian postal codes are 7 digits, including the space between the first and second sets of 3 characters: X1X 2Y2, for example.
• Excel and other spreadsheet programs may not retain a leading zero in a postal code field, for New England states for example. Make sure the file is saved so that postal codes are formatted as five digits before saving as a CSV file
• Do not include the ZIP+4 in the Postal Code field. It belongs in the Postal Code Suffix field.
• Canadian postal codes are not ZIP codes; that's a trademark of the United States Postal Service. They are 7 characters in the format A1A 1A1 — alternating letters and numbers with a space in the middle.
• Some countries don't have postal codes. Hong Kong and Ireland, for example, do not use postal codes. N/A is appropriate in those addresses.
• Phone Numbers typically contain 10 numbers, a 3-digit area code, a 3-digit prefix, and a 4-digit line number. Imported phone numbers may be in the following formats, with any digits beyond 10 numbers considered an extension:
  • XXX-XXX-XXXX
  • (XXX)XXX-XXXX
  • XXXXXXXXXX
    
    NOTE: international phone numbers are not supported in Advanced Import.

• DATE ENTRY: We highly recommend that you use on the listed formats with the complete four digit year, especially if any of the dates you are importing are prior to the year 2000. Advanced Import supports the following common date formats for all date fields:

  • • mm/dd/yyyy
    • mmddyyyy
    • yyyy/mm/dd
    • yyyy-mm-dd

Donation Data File Best Practices

Different Payment Types require different fields.  Check payments, for example, must also upload the check number and the check date. Cash payments require no other data and are the default. If you don't upload a required field along with your non-Cash payment type, it will convert to a Cash Payment Type. These are the required fields with the corresponding Payment Type:

• Check
  • Check Number
  • Check Date
• Credit card
  • Card Type (valid values listed below)
    • AMEX
    • American Express
    • Amex
    • Diners Club
    • Discover
    • JCB
    • MC
    • Master Card
    • MasterCard
    • Other
    • VISA
    • Visa
  • Last 4 digits of the account number
  • Expiration Date (MMYYYY or MMYY)
  • Authorization Code
• Other
  • Other Payment Description (Recommended if Payment Type is "Other". Default: N/A)
• Gift in Kind
  
  • Gift in Kind Description
• Payroll Deduction
  • Payroll Identifier (Optional if Payment Type is "Payroll Deduction")