Importing Donors and Donations from PayPal

<< Click to Display Table of Contents >>

Navigation:  INSTALLATION INSTRUCTIONS >

Importing Donors and Donations from PayPal

If you use PayPal to accept online donations, they have an option to export data files for importing into other programs, called the Activity Download.

 

You can import that one file with both the Database Import Donors menu option and the Database Import Donations menu option, in order to import both donors and donation that are included on the same lines of the file.

 

Please first read the Help topic Importing Donors and Donations from One File to understand the general issues about doing this type of activity. What follows are the specifics for PayPal.

 

Importing from a PayPal Activity Download does have some difficulties and some limitations, as explained in the details below.

 

Here is a table of a sample Activity Download exported from PayPal, using its default export columns.

 

We have actually turned the data sideways to make it understandable (and fit on a page!). So if this was in Excel, the rows would be columns and vice-versa. We added a first column to the table below to count the field numbers. What I have listed as Field 1 is really Column A in Excel, Field 2 is Column B, etc. The 2nd column of the table below is the row of headings from the data, and the 3rd column is a made-up sample data row.

 

 

Field #

Heading

Data

1

Date

01/05/2020

2

Time

8:45:57

3

TimeZone

EDT

4

Name

JOHN SMITH

5

Type

Subscription Payment

6

Status

Completed

7

Currency

CAD

8

Gross

10

9

Fee

-0.59

10

Net

9.41

11

From Email Address

johnsmith149375@gmail.com

12

To Email Address

yourorganization@gmail.com

13

Transaction ID

14

Shipping Address

 

15

Address Status

Non-Confirmed

16

Item Title

Your Organization Subscription

17

Item ID

 

18

Shipping and Handling Amount

0

19

Insurance Amount

 

20

Sales Tax

0

21

Option 1 Name

 

22

Option 1 Value

 

23

Option 2 Name

 

24

Option 2 Value

 

25

Reference Txn ID

26

Invoice Number

 

27

Custom Number

 

28

Quantity

1

29

Receipt ID

 

30

Balance

37.64

31

Address Line 1

 

32

Address Line 2 …

 

33

Town/City

 

34

State/Province …

 

35

Zip/Postal Code

 

36

Country

 

37

Contact Phone Number

1234567890

38

Subject

Your Organization Subscription

39

Note

 

40

Country Code

 

41

Balance Impact

Credit

 

Importing the Donors

 

Based on this, the following list of columns could be selected in the Fields to Import list of the Database Import Donors window:

 

Skip 3 Columns

Name: First Last

Skip 6 Columns

Email

Skip 19 Columns

Addr1

Addr2

Addr3 (Merge 2 Fields)
This merges the Town/City and State/Province ... fields into one field.

Postal Code

Skip 1 Column

Phone

 

The rest of the columns are either about the donation details or are irrelevant, so we will ignore them. When importing donors, if there are extra columns at the end of each row you will be warned about that, but given an option to just ignore them, so we don't need to have a big "Skip ... Columns" at the end to skip them.

 

There's a possible problem with the data, however, that you would have to fix before importing it. If you have any PayPal transactions in the downloaded file that are anything other than donations you received, they may have nothing in the Name field that we are importing, and even if they did, you would not want to import those rows! And DONATION will not import rows with no Name value(s) filled in. So you really need to open the downloaded file in Excel, delete any rows that aren't for donations (and/or that don't have a Name), and re-save the file.

 

Import that possibly edited file now, with 1 header row, using Email Address as the unique identifier for donors, selecting either Update existing donors ... or Ignore rows ... matching donors (whichever you prefer), and also selecting Ignore additional matching rows for each donor.

 

One concern you might have about choosing Update existing donors ... with the field selections above is that the exported phone number format is just 10 digits with no dashes or other punctuation. Fortunately the import routine will reformat a number like 1234567890 to (123) 456-7890, so that should not cause a problem.

 

Importing the Donations

 

Once that works, we can go on to the Donations. The first problem I encountered is that at least in Canada, the PayPal exports used the DD/MM/YYYY format for the date, while my computer was using MM/DD/YYYY. To get those dates to import properly, I had to temporarily change my Short Date format in Regional settings in Control Panel to match the PayPal format - DD/MM/YYYY.

 

Set up the Database Import Donations window with the following Files to Import list:

 

Date Received

Skip 3 Columns

Cheque # / Paid By

Skip 2 Columns

Total Amount

Skip 2 Columns

Email

Skip 4 Columns

Category

 

As with importing donors, when importing donations if there are extra columns at the end of each row you will be warned about that, but given an option to just ignore them, so we don't have to have a big "Skip ... Columns" at the end to skip them.

 

Some of the decisions of which import fields to match to which donation fields were somewhat arbitrary. For the Cheque # / Paid By, likely what you really want to have saved there is "PayPal", but that does not occur in any column in the import data. So we chose the 5th column, Type, which was showing "Subscription Payment". While you are editing the file before importing it, to remove any lines with no Name, you could replace all values in that 5th column with "PayPal", without the quotes.

 

Also, we chose the Item Title field to import into the Category. You will be prompted when values in that column don't exist as Donation Categories. You can associate them with existing categories, or create new Donation Categories based on them. For this sample, I associated the value that was always found in the Item Title field, which was "Your Organization Subscription", to the General category. This also points out a weakness of using PayPal for donations: it doesn't appear to me that there's any way for your donors to specify a donation category and have that come out in the import file. So all PayPal donations that you import will end up getting the same Donation Category, unless you can figure something else out that you can use in the data to distinguish categories.

 

Now import that, with 1 header row.

 

That's it! You can repeat these imports each time you download a new file from PayPal, using the same settings, which the program remembers for you unless you do other different imports in between, that get memorized in place of these settings.

 


This topic was last edited on Jun 17, 2020