The User's Edge : import

7/22 Forum Roundup

Douglas Clinton — Wed, 22 Jul 2009 20:01:00 GMT

Here are last week's most popular forum discussions as well as unanswered posts:

Most popular topics:

Unanswered Posts:

Join the discussion over at the forums.

Import Insight: Excelsior!

Douglas Clinton — Tue, 30 Jun 2009 18:20:00 GMT

Sometimes you have to be smarter than the software.

Knowledgebase solution BB16868 is a skeleton key to group records by comparing two different fields. The solution walks you through the process of finding constituent/spouse pairs that have the same (or different) last names and popping the records into a query for you. The process involves the quiet and unthreatening duo query and export, as well as the great boogeyman of The Raiser’s Edge – Import. Don’t let that discourage you from trying this solution – there is only one field to import and you don’t even change anything on the records.

Depending on what fields you need to compare, you can either create a query of your records or export every record in your database. When you select your fields to export, you will only need to include three – the record’s Import ID, and the two fields to compare. The KB solution compares constituent and spouse last names, but you could compare constituent phone number, or Gift Amount and Soft Credit Amount on a gift or anything else you can capture in export.

Excel expertise is not required to use this solution. Assuming that you have a header row, and that the Import ID is in Column A, the solution you would enter in Column D would look like this:

=IF(B2=C2,"Yes","No")

Once you have entered this in Cell D2, copy it all the way down the rest of the column. For every record where the values in columns B and C match, you will have a “Yes” in Column D. Contra wise, if the records don’t match, the formula will return a “No” value. Once Column D has been populated, your goal will be to winnow down the spreadsheet until only the records you want remain. Your first step will be to sort the spreadsheet by Column D, so that you have all the “Yes” records in one group, and all the “No” records in another.

Using the example from the KB solution, if you want all the constituents whose last names do not match their spouses’, you would delete all the rows containing the “Yes” records. When only the “No” records are left, delete columns B, C, and D – you won’t need them where this process is going. When the Import ID column is the only one left standing, save the spreadsheet as a .csv file and go to Admin, Import in The Raiser’s Edge. Create an Import and select the .csv file you’ve edited and saved as the file to Import.

At this point, the process pretty much comes down to marking two options. The first of these is on the General tab, where you will mark Update existing records. Remember, since the Import ID is the only field you are importing, you won’t actually be making any changes to the record – you’ll just be tricking the software into thinking you are.

The other option to mark is on the Summary tab – Create an output query of records updated. When you click Update Now, you will be prompted to save that Query – once the Import is completed, you will have the query of the records you need. This process does take you a few paces off the beaten path, but it’s a handy way to put together a query when a more straightforward approach isn’t an option.

The answer to the Brain Buster in this month's edition of The User's Edge is the computer keyboard!

Import Ingisht: Soft Credits (Part II)

Kevin Brunson — Wed, 06 May 2009 12:47:00 GMT

In my last post, I discussed how to add new soft credits to existing gifts. Now, let’s look at using import to update the soft credit gift amount on gifts that already have soft credits.

In addition to the fields necessary to add a new soft credit (Recipient Import ID, Gift Link, Soft Credit Amount), the Soft Credit Import ID is required. This tells The Raiser’s Edge which soft credit amount to update. The column header for the Soft Credit Import ID is SoftCreditImpID.

In Admin, Import, select Soft Credit from the Gift node. Click New and select the following parameters.
On the General Tab select:
• What do you want to do? Update existing records
• What file to 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

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.
On the Summary Tab select:

• Create control report. This report lists the criteria fields and operators selected for the import. The report also shows the import name and the directory path where you saved the import.

For more information on importing soft credits see Knowledgebase solution BB53726. This solution also contains a sample soft credit import file.

If there is a specific type of import you would like to see discussed next, please leave a comment and I’ll add it to my list of topics.

4/20 Forums Roundup

Douglas Clinton — Mon, 20 Apr 2009 17:36:00 GMT

Here are the discussions that generated the most chatter in The Raiser's Edge forums last week:

If you have advice or insight to share, join the discussion today!

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!

Sarah's Shortcuts: Never Underestimate the Value of a Guinea Pig

Sarah McBride — Mon, 10 Mar 2008 10:10:00 GMT

After working for days to set up an import file, you may be tempted to take your chances with The Raiser's Edge gods and import the entire file at once. But wait! Don't get too trigger-happy with that Import button, my friends. If there happens to be a problem with the way the data was entered in your spreadsheet, you could very well face a lot of manual cleanup.

You'll commonly hear us advise you to have a recent backup of your database (should you need to restore to pre-import orderliness), and we often recommend validating the import so you can check for any potential exceptions before starting the actual import process. (See BB58904 for more info.)

Kudos to you if you've made these regular habits in your import process! But have you also developed a healthy respect for guinea pigs?

Though I do like those furry rodents, I'm actually referring to a test import file that just includes the first one or two records from your original spreadsheet. Using an import guinea pig will give you a chance to confirm the import produces the right results and can make it easier for you to identify any changes that need to be made within your original file. Just remember to remove the guinea pig records from the larger file once they've imported successfully in your test run.

This may take away some of the adrenaline rush from importing all of the records on the first go, but it will also help you avoid that horrible lump in your stomach when you realize something went askew.