Importing Contributions

[buddy409]

I think we have found some sort of glitch. We have been trying to import all our data into a new install of rock and part of that is about 60,000 financial transactions.

For some reason, roughly 10,000 of them get assigned to “Anonymous Giver” even though the PersonID matches a person perfectly. We have tried doing the individual/Family import separate from the Contributions import, as well as at the same time.

Then we simplified the PersonID numbers to be a simple, 1, 2, 3, etc. Then, a different group of 10,000 transactions got assigned to “Anonymous Giver”.

Is this a know glitch or am I missing something dead obvious here?

[guymaness2]

There are no known issues as you are describing. Any records in the Financial csv file that have a value in the IndividualID that cannot be matched is assigned to Rock's default Anonymous Giver record.

Person records are matched on either the ForeignId (when an int is provided in IndividualID column) or ForeignKey (when a non-numeric string is provided in the IndividualID column) of existing Person records in Rock, not the actual Person.Id. This is by design to limit any matching to only previously imported Person records. This is to prevent any undesired cross assignment of contributions due to your source system IndividualID coincidentally matching a valid Rock Person.Id for a different person.

Keep in mind Bulldozer is designed as a migration tool, not a periodic importing tool. As such, its proper use expects an Individual csv to be imported before the Financial csv. As it imports the Individual csv file, it writes the PersonId field to the ForeignId and/or ForeignKey field of the imported Rock record. This is what allows the Financial import to find the appropriate Person record. You will want to make sure that every IndividualId in your Financial csv file exists as a PersonId in your Individual file.

Does that explanation reveal where the issue may be?

[buddy409]

Everything you said makes perfect sense. We have our “01 - Individual” file cleaned up and our person ID numbers are super simple. Starts at 1 and goes up to 5800. There is a person for every number in between. Our “06 - Contribution” file has 63,000 records and each of them has a personID that is between 1 and 5800. I’ve tried the import about 15 different ways and every time, a latch chunk of the contributions get assigned to the Anonymous Giver. Sometimes 20,000 of them, sometimes only 7,000. And it’s always different people that don’t work.

[guymaness2]

Interesting. There are two log files (one for activity and the other for exceptions ) in the bin/Logs folder of the Bulldozer package that record in realtime what is going on. Are there any clues in either of those?

[buddy409]

The log shows that everything imported perfectly with no errors and there is nothing on the exception log for the time period of the import

[guymaness2]

Can you try the following exercise: 1) Import your Contributions csv file into an SQL table using MSQL Management Studio 2) Tweak and run the following query:

SELECT * FROM importedCtrbTableReference
WHERE IndividualID NOT IN( SELECT ForeignId FROM Person )

You'll need to change importedCtrbTableReference to a valid reference that will grab the imported table, and it presumes you are running the query against your RockDB for the simple "Person" table reference.

This should show you all the IndividualIDs that are in your Contributions file that do not match any foreignkeyed records in Rock.

If it comes back empty, then it means the issue lies in the contribution file/import. If it comes back with a bunch of hits, then it means the issue lies with the individual file/import.

[treyhendon]

Slight tweak to that. Need to ensure that the Foreign Id and Foreign Key are on the PersonAlias table in addition to the Person table. Not with Bulldozer, but with other tools I've seen that the foreign markers get set on the Person table and are missing from Person Alias. Bulldozer builds a dictionary of the Person Ids from the Person Alias table and that's how it's all stitched together.

[buddy409]

So I checked it against both the person table and the personalias tables. It comes back empty on both of them.

[treyhendon]

Just to also make sure, have you run the CSV through a validation utility?

I wonder if the parsing is getting off anywhere? SQL Management Studio's import can be more forgiving, but carriage returns (/r/n) can cause issues with the CSV parser in Bulldozer. Or possibly non-escaped (") text containing a comma (like in a Fund Name) could have shifted column parsing.

I used Flat File Checker when first developing the CSV standards for the extended maps of Bulldozer if that helps.

[buddy409]

That sounds like it could be the issue. I’ll give that a try!

[buddy409]

So I checked all the files I’m importing using the Flat File Checker. I’ve removed all the commas from the text fields in some of the documents. Still, the same 7,365 contributions get assigned to Anonymous Giver...

[ElimAdmin]

I recently used Bulldozer to import batches and transactions only. Nowhere near as many as you. I only had the individual Id from the Contribution.csv set in the ForeignId and ForeignKey fields in the Person alias record where AliasPersonId=PersonId. I didnt have ForeignId or ForeignKey set in the Person table.

In our case we were matching on the Person's Envelope Number.

We were not importing Individuals at the same time, so I cleared ForeignId and ForeignKey in Person and in PersonAlias, then reset ForeignId and ForeignKey in PersonAlias (from the Person's EnvelopeNumber attribute) where AliasPersonId=PersonId.

I think

SELECT * FROM importedCtrbTableReference WHERE IndividualID NOT IN( SELECT ForeignId FROM Person )

should read

SELECT * FROM importedCtrbTableReference WHERE IndividualID NOT IN( SELECT ForeignId FROM PersonAlias WHERE AliasPersonId=PersonId )

This:

SELECT * FROM importedCtrbTableReference WHERE IndividualID NOT IN( SELECT ForeignId FROM Person )

will only work if you have just imported the Individuals so that ForeignId on Person AND PersonAlias are the same.

So the link that must exist is: Contribution.csv.IndividualId to PersonAlias.ForeignId (if int) or ForeignKey (if not int).

Then PersonAlias.AliasPersonId to PersonAlias.PersonId.

Then this PersonId is used to match to Person.Id.

If you have records being linked to AnonymousGiver then there is something in that linkage that is not finding an actual Person record.

You don't have a Perso Alias record with a Foreig Id that matches the IndividualId in the Contributions.csv, Or then row i AliasPerson that does match (there should only be one) is returning a Person Id that can't be found.

Correct me if I've described this wrong Trey.

(I also had commas in the memo field that tripped me up.)

I"ll post some sql tomorrow if that helps.

On Sat, 30 May 2020, 3:50 pm buddy409, notifications@github.com wrote:

So I checked all the files I’m importing using the Flat File Checker. I’ve removed all the commas from the text fields in some of the documents. Still, the same 7,365 contributions get assigned to Anonymous Giver...

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/KingdomFirst/Bulldozer/issues/30#issuecomment-636270762, or unsubscribe https://github.com/notifications/unsubscribe-auth/ABDRIAP2JMFQV2ONVNOV3Q3RUB7AJANCNFSM4NN5NZXQ .