Preparation

Data needs to be prepared before it is imported. You need to:
  • Look at the data to import in a tool (like Excel) and adjust the data so columns are consistent.
  • Add column headers for each data item.
  • Use special names for those column headers, especially if the column can be used to match records during the import and replace data in the database.
  • Fill down columns with missing data, if you want a default for a particular column (e.g. if there is no Country listed for some records, and you want a default, then entering a country in the empty fields in that column).
  • Add some fields of your own, if appropriate, to create defaults for imported data.
  • You can import the Excel spreadsheet in a number of formats. You can
    • Import the columns you want from the Excel spreadsheet in either .XLS or .XLSX format, even with calculations in it. TM only looks at the content of the column and ignores the actual calculation.
    • Save the Excel document as a TEXT file in either TAB or COMMA delimited format. (TAB delimited is recommended - it seems easier to use).
    • Other tools that will be help are Text editors like BBedit. Opening the prepared text file with an editor can help with hidden formatting like columns, carriage returns, and more. See View, Text Display, Show Invisibles.

Sometimes MS Excel documents have unwanted extra lines and formatting that can increase the record count when imported. To prevent this from happening,
  • Excel Format: Make sure there are no empty lines or columns by using the 'clear' command in excel to ensure no additional empry rows or columns in the spreadsheet. They are hidden and not easy to see
  • TEXT Format: Open the TEXT document in a text reader program like NotePad++ or BBedit. The text reader program will expose the additional unwanted lines. Delete the blank lines. Save the TEXT file. Now it's ready for a clean import.
    Diataxis: 
    How To

    Preparing the Import File

    Patron data imported into Theatre Manager must be in either the industry standard Tab Delimited or Comma Delimited format. This format is supported by most applications. If it is not, a tool like Microsoft Excel or Word is very useful for helping massage data before importing into Theatre Manager.

    Tab delimited format means that each field in the database is separated by a tab and the last field in each record has a carriage return after it. The first record in the data may be the title of the fields for clarity, although this is not necessary.

    Patron Import Sample

    Company Address Line 1 Address Line 2 City State Postal Code gPhoneWork
    Elk Whistle 18032-C Lemon Drive F Street Yorba Linda CA 92886 (714) 695-0994
    Namah Dance and Music Ensemble 5756 Wallis Lane Woodland Hills CA 91367 626) 576-8730
    L.A. SYMPHONIC WINDS 23309 Mariano Woodland Hills CA 91367 818,348-2990

    Notes about the Import File

    • The first row in this import file contains a description of the fields, or columns of data. Examples are 'Company' and 'City'. The only benefit of using descriptive names for the column headers is to assist the column matching process. Because of the change where there can be many email accounts, phone numbers, addresses, etc. for a patron, we needed to create some special column titles that refer to columns that have special use for convenient importing or matching data.
    • There are some spaces at the beginning of some fields. Work Phone in the second row has some spaces before the (714) area code. Theatre Manager will remove all extra spaces at the start and end of each field.
    • Some columns have blank entries. In this case, a value for 'Address Line 2' is only present in the second row. The last two rows will be imported as blanks.
    • Theatre Manager will attempt to fix data problems during import. On the last line, 'L.A. SYMPHONIC WINDS' will be converted to 'L.A. Symphonic Winds' and '818,348-2990' will be corrected to '(818) 348-2990'. The conversion will occur depending on the parameters set in System Preferences for auto-capitalization.
    Diataxis: 
    Explanation

    Cleaning Up Data

    It is very important that each column of data contains the same type of data, otherwise importing the data file would provide disastrous results. The import process will simply import whatever is in that column into that field in the database. For example, the 'Company' column should only contain Company names. If it does not, the import may end up with last names in the first name or addresses in a company name. Each patron would have to be deleted and the import would have to be started again.

    When data importing, you should 'clean up the data' prior to importing. This can be done by creating separate columns for:

    • patron salutation
    • patron name-first (if the first and last are in the same field, the import will auto-split them)
    • patron name-last
    • address line 1
    • address line 2
    • city
    • province
    • postal code/Zip Code
    • country (Optional)
    Diataxis: 
    How To

    Special Import Column Names

    Most data can be imported regardless of the name on the column. However, Theatre Manager is a relational database which makes it difficult to import some types of fields, especially if there are multiple for a patron (like phone numbers).

    There are also some fields that can be used for matching fields - which allow data to be replaced in current records.

    Diataxis: 
    Explanation

    Import Fields with Special Meaning

    You can use any of the special fields below when you do your import process. They will match up with fields in the database such as lookup data or phone numbers.

    The import routines will automatically create new values into Code Tables for the special meaning fields during the import process. In most cases, there is no need to prepopulate code table values that will be used.

    The exception is gPaymentMethod - all payment methods must be created prior to using them.

    Patron Information
    • gPatronType - patron type - individual, company, staff, etc. Code Table "Patron Type".
    Patron Contact Information
    • gPhoneHome - patron home phone number
    • gPhoneWork - patron work phone number
    • gPhoneCell - patron mobile phone number
    • gPhoneFax - patron fax phone number
    • gEmailHome - patron home email address
    • gEmailWork - patron work email address
    • gWebsite - patron work website URL

    Some special fields that will "update" the field, rather then add new contact information to the patron account

    • gPrimaryPhone - Patron primary phone number
    • gPrimaryEmail - Patron primary email address
    • gPrimaryFax - Patron primary fax number
    • gPrimaryWeb - Patron primary web site URL
    Patron Marketing Information
    • MKT_C_SEQ - REFER to special field for matching patron numbers on import
    • gC_AGE_CODEresult - Marketing age code
    • gC_MKT6result - Marketing field #6
    • gC_MKT7result - Marketing field #7
    • gC_MKT8result - Marketing field #8
    Volunteer/Personnel Information
    • gC_ACTIVEresult - Marketing Volunteer Status
    • gMktVol1Result - Volunteer Popup field #1
    • gMktVol2Result - Volunteer Popup field #2
    • gMktVol3Result - Volunteer Popup field #3
    • gMktVol4Result - Volunteer Popup field #4
    • gMktVol5Result - Volunteer Popup field #5
    • gMktVol6Result - Volunteer Popup field #6
    • gMktVol7Result - Volunteer Popup field #7
    • gMktVol8Result - Volunteer Popup field #8
    • gMktVol9Result - Volunteer Popup field #9
    • gMktVol10Result - Volunteer Popup field #10
    Patron Donor Information
    • gDonorGivingCapacity - Marketing Donor Giving Capacity
    • gDonorMovesStatus - Marketing Donor Moves Management Status
    Patron Mail Lists
    • PM_SHORT_NAME and gName1 through gName15 - are up to 16 mail list columns. If you want to import any mail list, then you must have ONE column called PM_SHORT_NAME.
      • The contents of the field indicate which mail list to which the patron will be added.
      • You can comma separate the mail lists to add to a patron such as list1,list2,list3 and/or
      • You can have multiple columns in your import file named 'gName1' through 'gName15'. In those columns, you can have blanks or mail lists to which you want people added (eg list1 or list2, etc.)
      • In this way, you can use up to 16 columns to specify a lot of mail lists to add a patron to, either many in one column separated by commas, or in separate columns if that is more clear for you.
    Ticket/Donation/Member Data - Orders
    • I_ORD_REASON_BUY_RESULT - Order reason "Why did Patron Buy?", Code Table "Order Reason to Buy".
    • gSource - Delivery Method - ticket handling status.
      • 0 = Hold for Pickup/Taken
      • 1 = Mail to Patron
      • 2 = Hold for WillCall
      • 3 = Hold @ Door for Coupon
    Ticket Data - Tickets
    • gTixUsed - Patron Attended - flag to indicate if patron attended the performance.
      • attended = indicate that patron attended the performance
    • gAttendedReason - description for the Patron Attended or Not Attended Reason. Code Table "Course Attendance Reasons".
    Donation Data - Donations
    • gDonor1Field - Donor Popup field #1
    • gDonor2Field - Donor Popup field #2
    • gDonor3Field - Donor Popup field #3
    Donation Data - Campaigns
    • gAccount - Account number to assign to new donation campaigns
    Member Data - Memberships/Passes
    • gMember1Field - Member Popup field #1
    • gMember2Field - Member Popup field #2
    • gMember3Field - Member Popup field #3
    Member Data - Member/Pass Types
    • gAccount - Account number to assign to new member/pass type
    Ticket/Donation/Member Data - Payments
    • gPaymentMethod - Payment Method. Matching "Payment Code" in code table "Payment Methods". All payment methods must be created prior to using them.
    • gOrigin - Payment Source - location of where the payment originated.
      • 0 = Internet/Web Sales
      • 1 = Telephone Order
      • 2 = Mail Order
      • 3 = Patron Present
    Diataxis: 
    Reference

    Matching Fields

    The following fields could be used as unique identifiers to import and match records to data that exists in Theatre Manager, or they could just be used to import data. The meaning changes depending on how you use them for matching data during the import process.

    MKT_C_SEQ

    This is a special field that is allows you to match imported data with existing patrons based on the patron number. This should only be used if the data file originated from Theatre Manager in the first place, such as for address correction, and you are sure the Patron number matches the one in Theatre Manager (otherwise the results will be overwritten and jumbled data). If you are going to match on patron number, be sure to make a backup of the database first and verify the success of the results after the import.

    If you plan to Export patron names for address correction purposes through a Mail House or other verification service, be sure to include the Patron number on the export, so that when you reimport the corrections back in, you have the patron number to link the corrected address with the correct patron record. In addition, be sure not to merge any patron records while the list is at the Mail House, or else the patron numbers may not align when you reimport the data.
    Helpful Link: Canada Post address correction web site

    MKT_FIELD_1 through MKT_FIELD_5

    These are the marketing fields on the patron record. If they are unique within your database, and you specify them as a column header in the import file, they will also be presented as 'matching field' options. Only do this is you have used these fields to reference unique keys from another system (such as prior ticketing systems).

    Diataxis: 
    Explanation

    What to Import

    The data file may have a lot of information and many types of fields. We suggest that you make an evaluative judgment on the quality of the data. For example, first name, last name and address fields are usually of good quality. Often work and home phone numbers are reasonable. Other phone numbers, marketing information, etc. may be poor quality. Sometimes it is better not to import poor quality data. If it is missing, nobody can make a decision based on poor data.

    During the import, the program will try to match data with an existing patron based on fields you select. If a match is found, only the fields in the import file will be replaced. The remaining patron information will remain untouched. This may be useful when importing an address correction from the post office. The user may not want to update the patrons first name or last name, just their address, city and postal/zip code fields. If so, only include those columns in the import file.

    Here are some general guide lines:

    Patron Number
    • If you are using patron number to import to match to existing ones in Theatre Manager (say for address correction purposes), then you must include the patron number with each record and the field heading must be MKT_C_SEQ
    Salutations
    • Ensure all have a period where appropriate. For example, Mr. Mrs.
    • Ensure all are either 'and' or '&'. For example Mr. & Mrs.
    • Ensure all are set to the proper mixed case.
    First Name
    • Ensure all are either 'and' or '&'. For example John & Mary
    • Move all salutations out of the first name field and move them to the Salutation field
    Last Name
    • Move all company names into the company name field.
    • Move all suffixes out of the last name field and move them to the Suffix field
    Suffix
    • Ensure all have a period where appropriate. For example, Jr.
    • Ensure all are set to the proper mixed case.
    Address
    • Update address information to contain the Post Office address guidelines for mailing address. For example, using PO BOX, RR
    • Update address information to be contained on 2 address lines. If you have more then 2 address lines, they will need to be either combined into 2 lines, or have data contained within the address lines moved to different fields within the patron information record.
    State/Province
    • Ensure all are consistent with a 2 letter state/province code
    Country
    • All contries must match those that are in the codetables. For example, USA, Canada, New Zealand, Japan. Those that do not match will be ignored.
    Phone Numbers
    • Best ability to ensure that phone numbers are properly indicated into separate columns as Home, Work, Fax or Other
    • Depending on time availability, telephone number extensions to be standardized to be starting with a 'x'. For example x111
    • If area codes are missing from phone numbers and you wish to have all 'missing' area codes be set to something, please let us know prior to the data being imported.
    • If the phone number being imported is to be the primary phone number, use the correct column name in the import file.
    Diataxis: 
    Explanation