How to setup an excel file to import into DM?

[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]

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.

[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]

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]

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

[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]

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.

[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]

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?

[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]

>>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 ... ?

[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]

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.

[Andrew]

Hi John, thanks for the update, works great! I'm zipping away inputting our data!

[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).