Best Practices: Country Code Standardization

Zane Magnuson 2 · November 2015

We're a higher ed institution and we currently house data in Education Edge (students/faculty) and Raiser's Edge (donors/alumni). I ran into a somewhat strange and minor issue and am looking for suggestions on how best to handle it.

We accept applications via the Common Application, which is a 3rd-party company that allows students to fill out one application form and send it to any number of colleges and universities. In the universal part of the application (i.e. the part that you can't edit), the Address-Country field can be exported as either "United States of America" or the export code "USA". Our EE/RE database tables have "United States" as well as the unused/inactive option "USA". My goal is to get this export/import process to a point where there's no manual fudging; you just export from Common App and import into the database with a few button clicks.

To handle the inconsistent country code issue, should I:

Change the Country Code in RE, EE, and NetCommunity ("United States") to match the Common App coding ("United States of America")
Export Common App with the "USA" code and periodically run a post-import Global Change to make it "United States"
Suck it up and just export Common App with "United States of America" and change it in the .CSV file before importing.

(posted in RE since it's a more active community and this is more of a general database question)

Alan French · November 2015

We use Importomatic to import a lot of our data, I find it very useful as it will allow you to set up a "data dictionary" to automatically convert values from one to the other during the import process, which would take care of your United States of America issue for you.

There is a cost attached to Importomatic, however Zeidman Development make a similar product called Importacular which will allow you to import constituent and gift data for free (not tried it myself but have read good things on the forums).

Ryan Hyde · November 2015

So my first question is, are you creating a process for yourself or a process for others.

If you're creating a process for yourself, I'd do option #3. Ultimately, a global change in a .csv file is super quick, and so long as you trust yourself not to completely screw something up, then you're good.

But if you don't necessarily know who is going to be doing this import, or you don't trust them not to make a big keystroke error, do option #2. I would just write a query to dynamically look for someone with the wrong country code, and then pipe that query into a global change that would switch that country code to your standard code.

This process would take a bit longer in total RE processing time (though this only really matters if you have many hundreds or thousands of records your'e doing this for), but it would be fool proof.

Zane Magnuson 2 · November 2015

Ryan Hyde:

So my first question is, are you creating a process for yourself or a process for others.

If you're creating a process for yourself, I'd do option #3. Ultimately, a global change in a .csv file is super quick, and so long as you trust yourself not to completely screw something up, then you're good.

But if you don't necessarily know who is going to be doing this import, or you don't trust them not to make a big keystroke error, do option #2. I would just write a query to dynamically look for someone with the wrong country code, and then pipe that query into a global change that would switch that country code to your standard code.

This process would take a bit longer in total RE processing time (though this only really matters if you have many hundreds or thousands of records your'e doing this for), but it would be fool proof.

This is mostly for myself, so it does seem like that'd be the quickest solution. It just isn't the well-oiled machine I always want.

Ryan Hyde · November 2015

Yes, RE seldom is. Sigh.

Zane Magnuson 2 · November 2015

Ryan Hyde:

Yes, RE seldom is. Sigh.

Lol I'm tempted to mark this as the best answer.

Best Practices: Country Code Standardization

Comments

Categories