How to setup an excel file to import into DM?

Here are the most frequently asked questions about the Donor Manager. You might even find a few answers !

Andrew
Posts: 35
Joined: Wed Sep 26, 2012 1:46 pm
Type the middle number, please (202): 202

How to setup an excel file to import into DM?

Post by Andrew »

Happy New Year! How do I go about setting up an excel file so that I can enter my monthly donor giving in there, then import that into DM? I'd like to have a separate (electronic) record of donor giving history, but still use DM to keep track of things. Thanks!
jmuehleisen
Site Admin
Posts: 737
Joined: Sat May 29, 2004 1:28 pm
Type the middle number, please (202): 202
Location: Kampala, Uganda
Contact:

Re: How to setup an excel file to import into DM?

Post by jmuehleisen »

The DM can import a number of giving file formats, generally defined by the organization that sends the info to the missionary.

If you are creating these manually, I can give you a somewhat "generic" format the corresponds to one of the existing organizational file formats.

Or, tell me more what you are working from and I can give some more specific guidance.
John Muehleisen
Mentoring Emerging Christian Leadership in Africa for Excellence / Integrity / Innovation
Andrew
Posts: 35
Joined: Wed Sep 26, 2012 1:46 pm
Type the middle number, please (202): 202

Re: How to setup an excel file to import into DM?

Post by Andrew »

A generic format would be great. Just something I can enter the data into (probably Excel '03 or '07) and then import it into DM.
wbatson
Posts: 8
Joined: Fri Feb 20, 2009 4:18 pm

Re: How to setup an excel file to import into DM?

Post by wbatson »

Hi John,

This generic Excel format would be great for me, too. Can you send it to me?

Thanks,
Willie Batson
wbatson@familybuilders.net
jmuehleisen
Site Admin
Posts: 737
Joined: Sat May 29, 2004 1:28 pm
Type the middle number, please (202): 202
Location: Kampala, Uganda
Contact:

Re: How to setup an excel file to import into DM?

Post by jmuehleisen »

Here would be a fairly simple and generic xls (or csv) gift import file format:

donor id
full mailing name
first name
last name
address 1
address 2
city
state
zip
country
gift date
gift amount
comments
receipt number
phone
email address

One thing: DM 5 requires this to be in an XLS 95 (yeah, I know) format, not Excel 97 or 2003 or 2007.

So, actually, a CSV is easier and eimpler and more generic.

If you are interested, work something up like this and I'll add a 'generic" gift file import to the DM for you.

John
John Muehleisen
Mentoring Emerging Christian Leadership in Africa for Excellence / Integrity / Innovation
Andrew
Posts: 35
Joined: Wed Sep 26, 2012 1:46 pm
Type the middle number, please (202): 202

Re: How to setup an excel file to import into DM?

Post by Andrew »

Hi John, thanks again for your program! I had a baby last year and she has health challenges so tracking donations has taken a backseat!

I did create a CSV like you suggested, but not sure what format to do it. Also wondering how to import it into DM - do you need to release a new version with that ability? Thanks!
jmuehleisen
Site Admin
Posts: 737
Joined: Sat May 29, 2004 1:28 pm
Type the middle number, please (202): 202
Location: Kampala, Uganda
Contact:

Re: How to setup an excel file to import into DM?

Post by jmuehleisen »

Is this a CSV that has basic names and addresses for building the master donor list?

Or does this CSV have information about individual donations?

And is it "one donation per line" in the CSV, or is it "one line per donor, with different donations in different columns on the same line"?

Tell me a bit more and I can help with the next step.
John Muehleisen
Mentoring Emerging Christian Leadership in Africa for Excellence / Integrity / Innovation
Andrew
Posts: 35
Joined: Wed Sep 26, 2012 1:46 pm
Type the middle number, please (202): 202

Re: How to setup an excel file to import into DM?

Post by Andrew »

I am thinking that it would be a CSV with information about individual donations, with "one line per donor, with different donations in different columns on the same line".

Since I'm just setting it up I am open to suggestions, but I was thinking something like:
ALPHASORT__2014/05/31__2014/04/30__2014/03/31__etc (where "__" equals a new column)
Smith, John__100.00__75.00__150.00
Siebert, Andrew__40.00__40.00__40.00

I don't think it would even need all of the other mailing/phone info you had originally suggested. I can manually enter that in DM.
jmuehleisen
Site Admin
Posts: 737
Joined: Sat May 29, 2004 1:28 pm
Type the middle number, please (202): 202
Location: Kampala, Uganda
Contact:

Re: How to setup an excel file to import into DM?

Post by jmuehleisen »

OK.

What's the source of this data? Is it coming from a different program? Or what?

And is this a "we'll process a file like this every month" type of thing, or is it "I have all this data and I just want to do an initial load of my giving history" type of thing?
John Muehleisen
Mentoring Emerging Christian Leadership in Africa for Excellence / Integrity / Innovation
Andrew
Posts: 35
Joined: Wed Sep 26, 2012 1:46 pm
Type the middle number, please (202): 202

Re: How to setup an excel file to import into DM?

Post by Andrew »

Ok, what I want is a separate copy of donation info that is not tied to DM, but that can be imported so I don't have to manually enter the info twice. I have encountered some bugs which I can't seem to re-create but which left me wanting to have an outside copy of the info. Also, if you ever stop supporting DM (no offense intended) then I want to have another format to see my donation history. I also want this format to be spreadsheet style so I can do totals and move stuff around, as opposed to just exporting the info from DM in a pdf style.

I am using Excel to create a CSV like you said, and would like to import this file into DM once a month. I don't have a computer file with history yet, so it is still very customizable; hopefully I will eventually enter past donations into the file and import them, if that works...

I am open to suggestions!
jmuehleisen
Site Admin
Posts: 737
Joined: Sat May 29, 2004 1:28 pm
Type the middle number, please (202): 202
Location: Kampala, Uganda
Contact:

Re: How to setup an excel file to import into DM?

Post by jmuehleisen »

>>Ok, what I want is a separate copy of donation info that is not tied to DM, but that can be imported so I don't have to manually enter the info twice.

OK. Very good.
>>Also, if you ever stop supporting DM (no offense intended) then I want to have another format to see my donation history.
No offense taken
>>I also want this format to be spreadsheet style so I can do totals and move stuff around, as opposed to just exporting the info from DM in a pdf style.
Sure. That makes sense
>>I am open to suggestions!
OK. What about this idea? Let's turn this upside down.

Instead of you creating the Excel worksheet and then importing it into the DM, how about if you enter the gifts into the DM and then let it create the Excel worksheet for you.

Currently, the DM creates an Excel Worksheet of your Partner Names in your "My Documents" folder every time it quits.

I could also add the code to have it create an Excel worksheet of your giving data at the same time.

I think that would satisfy your desire to have a copy of your data outside of of the DM that you could use and manipulate and run additional analysis on.

But here is the challenge: There are 2 possible formats for the giving data that is to be exported.

One is a "one excel line per donation."

The other is "one excel line per donor per year with a column for January, another for February, another for March, etc.."

The first option gives you more options for doing pivot tables and cross tablulations, but the second is more useful to see giving trends.

The second one would look basically like the giving history "grid" on each donors page.

The first one would look like the giving history details list.

But, it really only makes sense to do this one year at a time.

While it would be possible to do "one line per donor with each month's giving in one column" ... it would be a bit big. For example, 5 years would be 60+ columns wide.

Back to you. Your thoughts ... ?
John Muehleisen
Mentoring Emerging Christian Leadership in Africa for Excellence / Integrity / Innovation
Andrew
Posts: 35
Joined: Wed Sep 26, 2012 1:46 pm
Type the middle number, please (202): 202

Re: How to setup an excel file to import into DM?

Post by Andrew »

I would prefer the "one line per donor with each month's giving in one column". However, will the Excel Worksheet then include all giving history? I wonder if it could just include the last 24 months so it doesn't get too bulky? I can make a copy once a year so I have my own record.

Just to clarify, I wasn't asking for you to change the DM code. I thought it would be simple for you to just give me a generic import format similar to one already used by DM (even a simplified one from another organization format), and I could use that. One benefit would be the ability to copy/paste in the spreadsheet/CSV with monthly donors their info doesn't have to be entered every month in DM (I guess that is also an improvement suggestion, to auto-add monthly donations).

Am I correct that if you change the code then I would have to wait for an updated version to begin using the changes? If you can just provide an import format, then I could begin using it already?
jmuehleisen
Site Admin
Posts: 737
Joined: Sat May 29, 2004 1:28 pm
Type the middle number, please (202): 202
Location: Kampala, Uganda
Contact:

Re: How to setup an excel file to import into DM?

Post by jmuehleisen »

Actually, I've already included the code in the latest update, which is available now.

The current approach to 'importing giving data' is some scheme where there is one line per contribution, not something with multiple columns for different months.

And creating something like that would be a lot of coding to handle all of the various scenarios.

The current export I made actually was very simple to do (as the DM already keeps annual giving data in that format).

Like 5 lines of code. Super simple.

Combining multiple years into a single line is a little trickier, but not really too much.

But really the most flexible is the "one line per donation" approach, and then set your spreadsheet to run a cross-tab on that.

But take a look at what's in the latest update (the file will be called "PartnerNamesGiftHistory.xls" in your "My Documents" folder.
John Muehleisen
Mentoring Emerging Christian Leadership in Africa for Excellence / Integrity / Innovation
Andrew
Posts: 35
Joined: Wed Sep 26, 2012 1:46 pm
Type the middle number, please (202): 202

Re: How to setup an excel file to import into DM?

Post by Andrew »

Hi John, thanks for the update, works great! I'm zipping away inputting our data!
jmuehleisen
Site Admin
Posts: 737
Joined: Sat May 29, 2004 1:28 pm
Type the middle number, please (202): 202
Location: Kampala, Uganda
Contact:

Re: How to setup an excel file to import into DM?

Post by jmuehleisen »

Very good.

Let me know if you run into any problems (I saw your other message about new partners not showing up and replied there).
John Muehleisen
Mentoring Emerging Christian Leadership in Africa for Excellence / Integrity / Innovation
Post Reply