The User's Edge : extensions

Import Insight: The Art of Importing

Kevin Brunson — Wed, 11 Mar 2009 15:07:00 GMT

“Importing is like a box of chocolates, you never know what you’re going to get”. That might not be the exact quote, but there are many types of imports, each with its’ own flavor. Some imports are complex and require more information such as linked fields, while others are simple. Some imports allow importing the same field multiple times, such as phone numbers which use extensions. Sometimes you might not have the unique ID (Import ID, Constituent ID, or SSN) of records in your database. Other times you might not be importing new records, but instead making updates to existing records in your database.

My next few posts will focus on importing, and will touch on some of the above scenarios.

Let’s start with a simple import. This is a long post, but stick with it; you'll see importing is much easier than it sounds. In this example we'll import new constituent records with an addressee and salutation, address, phone number, and email address.

You can create a new file in Excel, or you can follow along using this pre-configurated import file (save the file to your desktop). In the first row of the file we need to add our header fields. These fields will map the fields from the import file to the appropriate location in the database.
ImportID, KeyInd, Titl1, FirstName, MidName, LastName, OrgName, PrimAddID, PrimSalID, AddrImpID, PrefAddr, AddrType, AddrLines, AddrCity, AddrState, AddrZIP, PhoneAddrImpID, PhoneImpID, PhoneNum, PhoneType, PhoneAddrImpID, PhoneImpID, PhoneNum, PhoneType

Note: you can type whatever text you like as your import headers; however, the advantage of using the Raiser's Edge headers is they will automatically map to the correct fields later. If you choose to use your own headers, you will have to map your headers to the appropriate fields later in the process.

Brief information on some of the header fields above.

ImportID - you don't have to use the Import ID, you can also use Constituent ID or Social Security Number. For new records you can leave this blank if you want, or you can assign your own value.
KeyInd - this tells Raiser's Edge whether the record is an individual or organization (use 'I' for individuals and 'O' for organizations).
PrimAddID and PrimSalID - this is the format to use for addressee and salutation. To get the desired value go to Configuration, Addressee/Salutations. Select File, Preview, Addressee/Salutations Report. Note the ID number on the report next to the sample addressee/salutation.
AddrImpID - this is the import ID for the address record. If you are going to also import phone numbers, we need to assign this value now, so we can link the phone number to this address. We will talk about linking phone numbers to addresses in the next bullet.
PhoneAddrImpID - use this field to link phone numbers to addresses. The field value must match the import ID of the address (AddrImpID) field.

After adding the header fields on the first row of the import file we can begin adding information for the new records on the 2nd line of our file, for example:
C897,I,Mr.,James,C.,Maple,,6,16,A345,Yes,Home,347 Campbell St/nApt 2,Atlanta,GA,30096,A345,P246,4048975623,Home,A345,P247,4048795621,Cell Phone

The commas above represent a new cell in your excel file. So 'C897' should go in the first cell with the header 'ImportID'. 'I' should go in the 2nd cell with the header 'KeyInd'. So on and so forth. If there are two commas together with nothing in them, that represents a blank data cell for that field. Your file should appear like this:

Continue adding records by going to the next line in the file.
When finished, save your file in CSV (comma separated values) format and close the file.

To import these records go to Import, select Constituent from the Constituent category and click New.

On the General Tab select:

What do you want to do? Update existing records
Options: Import records not found as new records and Use duplicate criteria for new records
What file do you wish to import? Browse to the import file
How do you want the system to identify existing constituents? Use the Import ID
What is the format of this import file? Delimited

Note about the above selections. I selected to 'Update existing records' because it's possible records in our import file may already exist in the database. By selecting to update existing records, and then 'Import records not found as new records and Use duplicate criteria for new records', we are using the tools in Raiser's Edge to try to prevent duplicate records.

On the File Layout Tab select:

Field Separators and Text Qualifiers: Comma, Quotation
Import Field Names: Field names are on the first line of the import file
Sample Import Format: Verify the first few rows of the import file

On the Fields Tab:

Ensure all rows in the Field to Import column are mapped to the correct field in the Raiser's Edge column.
Check the Extensions column. Groupings of phone numbers in the file must share the same extension.

The fields tab should appear like this after the fields are mapped:

One item to point out on this screen is the Extension column. This column is used when the same field is being imported multiple times. In this example we are importing multiple phone numbers. The first set of phone fields will have a value of '00' in the extension column, the second set will have value '01'.

On the Summary Tab:

Check the appropriate checkboxes. At the least, I suggest marking the Create control report option. The other options can also be helpful depending on what you are importing.

Note about the options on the Summary Tab.
The 'Create exception file of records not updated/imported' option is good to check in the event that you have exceptions during the import process, this option will create a new file with any exceptions. For example, maybe a required field is missing on some of the records in your import file, selecting this option will kick the record into a new exception file. You can then open the file of exceptions, make any required changes, and then import the exception file.
'Create an output query of the records imported'. Use this option if you need to do a mailing for the newly added records. You can use the output query in the Mail or Export module to create your mailing.
'Create an output query of the records updated'. Similiar to the above option, but this is for records that have updates made to them.
'Create control report'. As a general rule I suggest always keeping this checked, it is great tool to confirm the results of the import.

For more information on a simple import like this one, please check out Knowledgebase solution BB5765, which provides more details and a link to the sample file from above.

Stay tuned for the next post in the importing series.

If there is a specific type of import you would like to see discussed, please leave a comment and if there is a majority, I’ll try to focus on that next time.

Import Insight: Understanding Extensions

Douglas Clinton — Wed, 17 Sep 2008 21:02:00 GMT

Like calligraphy, making a soufflé, or playing the theremin, importing records into The Raiser's Edge requires practice and precision. In one of the first posts on The User's Edge Blog, Grace Strother shared her bag of import tricks. Among the tips Grace provided was to check import extensions when receiving exceptions in your import. Import extensions let The Raiser's Edge know how to handle the data when your import file contains the same field multiple times.

New constituent imports often include two or more phone numbers (including email addresses) for each record in the import. Each phone number uses up to four columns of data in the import file with these headers: PhoneAddrImpID (Phone Address Import ID), PhoneImpID (Phone Import ID), PhoneNum (Phone Number), and PhoneType (Phone Type). In the import file, grouping the columns for each phone number together will make the process easier for you, and the same headers will appear more than once. On the import's Fields tab, use the Extension column to group the import fields together.

In the screenshot below, the first group has an 00 extension and the second has an 01 extension. If you were importing both home phone number and email address, each home phone number field would be given the 00 extension, and each email field would be given the 01 extension.

Prior to version 7.82.1514, it was not possible to maximize the Import screen. If you are running an earlier version, you may have to use the scroll bar to make the Extension column visible.

Importing multiple phone numbers is just one example of when you would need to use the Extension column. Any import that uses a one-to-many field such as constituent code or attribute may also require the use of extensions.

To find this information in Knowledgebase, enter How to import the same field multiple times, select The Raiser's Edge 7, and click Search. It's BB135063.

Import Insight: Grace's Bag of Import Tricks

Grace Strother — Wed, 19 Mar 2008 19:17:00 GMT

Well, it wasn't a large bag, but I consistently used the tricks in it to help customers with puzzling import questions when I supported The Raiser's Edge. The "tricks" are pretty simple, but they may come in handy the next time you tackle an import.

Use the Fields tab or create an import file to find out what your options are. If I want to see what fields are available for a particular type of import, I select Create an import file on the General tab of the import, enter any characters in the Import file field, and go to the Fields tab. Here, you can browse through the available fields and select them to view the header. You can also click Click here on the bottom left to view the relevant page of the Import Guide for details on which fields are required and what kind of data go in those fields. If I'm updating existing records, I just go ahead and create a real import file, update the data in the fields, and import it back in, knowing it's already formatted correctly.
Note: You can only include one-to-many fields in the import file created through the Import facility. For example, if you're creating a constituent import file, you can't export phone numbers or anything that can occur more than once per record.
Go ahead and shove in the data. OK, so this isn't always practical in your live data, but give it a try in your sample data with a few of the records from your actual data file. Or if you are able to set up a standalone workstation with a copy of your live data attached, give it a try there to see how the import works with your real data. If you're not able to test in sample or on a standalone machine, back up your live data and test with a few "dummy records" or one or two records you know very well and don't mind playing with (maybe your own). I often find it easier to troubleshoot problems if I can see exactly what happens when I click Import Now.
Use the sample data files in Knowledgebase. I have no problem with plagiarizing when it comes to data files. I love copying the sample data files onto my machine and updating them with my own data. Hey, if someone else has tested the files for you, why not use them? (And for quite a few of them, that someone would be yours truly. ) Search Knowledgebase with "How to import [whatever it is you're importing]." The solutions with "(includes sample data file)" in the title have a file available to view and download. You can import them into your sample database to see what they do, or you can replace the data with your own.
When in doubt, check extensions. Sometimes I just can't figure out why I'm getting an exception. My data file looks correct, and I've tried everything in Knowledgebase I can find on the exception error. More often than not, a messed up extension is the culprit. If you're importing certain fields (like phone numbers or addresses) more than once, make sure each group of fields has the same extension. Sometimes, just one field will have the wrong extension, and that will throw everything else off.
Don't import after 4:00 in the afternoon. A customer told me this one, and I think she got it from a Blackbaud consultant. We may not always have a choice, but whenever possible try not to import or do anything else "global" in nature when your mind's not fresh. I know the cells in my data file all start looking the same late in the afternoon, so I wouldn't want to take a risk if I were working in an organization's live data.

If you have any tips for making imports easier, feel free to share them here!