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.

 

In their current website (as of September 2020), after logging into your account, you get to that by clicking on Activity at the top, then the Download link at the top right above the displayed activity. Then you can select your desired options there, including the Date Range and the Format. (Choose CSV or Tab for the format.) After then clicking Create Report, PayPal will begin creating it, and show it when it is ready. (They will also email you when it is ready - sometimes it can take a little while.)

 

You can import that one file with the Database Import Donors and Donations menu option, in order to import both donors and donations 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. This page will not repeat the details from that overall instruction page.

 

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 Donors and Donations

 

Based on the fields above, the following list of columns could be selected in the Fields to Import list of the Database Import Donors and Donations window:

 

Date Received

Skip 2 Columns

Name: First Last

Skip 3 Columns

Total Amount

Skip 2 Columns

Email

Skip 4 Columns

Donation Category

Skip 14 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 irrelevant, so we will ignore them. When importing donors and 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 need to have a big "Skip ... Columns" at the end to skip them.

 

Problems with the PayPal Activity Download for Importing

 

There are several possible problem with the data, however, that you may have to fix before importing it.

 

First, 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.

 

The next problem we encountered is that at least in Canada, the PayPal exports used the DD/MM/YYYY format for the date, while our test computer was using MM/DD/YYYY. To get those dates to import properly, we had to temporarily change the Short Date format in Regional settings in Control Panel to match the PayPal format - DD/MM/YYYY. Otherwise a date in the PayPal file like 01/05/2020 (for May 1) would be imported into DONATION as January 5!

 

We chose PayPal's Item Title field to import into the Donation 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, we 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 seem 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 may end up getting the same Donation Category, unless you can figure something else out that you can use in the data to distinguish categories.

 

Doing the Import

 

Import that possibly edited file now, with the following other settings:

 

1 header row

Email Address as the unique identifier for donors

either Update existing donors from data in matching rows from the file or Ignore rows from the file matching existing donors (whichever you prefer)

In Section 5, choose Specified Value and enter the value "PayPal", without the quotes.

 

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.

 

Saving the Settings

 

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 this same window in between, that get memorized in place of these settings. To be sure you can get back to these settings, we recommend using the Save Settings button and saving them with the name "PayPal", without the quotes. Then if you have used this same window for some different importing, you can get back to these PayPal settings with the Load Setting button.

 


This topic was last edited on Feb 17, 2023