database cleanup how-to

October 26, 2023 12:04 PM

The nonprofit I work with has been using an old desktop version of Quicken Home to track their finances (and paying their vendors by paper check!). I have permission to move their bookkeeping and invoicing over to QuickBooks, which is much more appropriate for their needs. I have seen the help page on how to do the actual export/import, but I have questions on how to approach the process.

Their Quicken database includes such categories as “PayPal fee” “Pay Pal fee” “pay pal fee” and “fee: paypal,” all of which seem to have been used at some point in the past. Same for "registrations:event," "Event registrations" and "Event: registrations." The reports are a mess.

They want to be able to see at least ten years of reports so I will have to import old data, I can’t just start from scratch. Should I clean up the old database, then export, or import everything and then clean it? Should I ask them to make me a list of categories and then recode everything? What are the best practices here?

posted by epanalepsis to Computers & Internet (4 answers total)

Personally I would keep the current database as is, and export all the data to a temporary intermediate database and perform all clean up over there. I think there is no way around having to recode everything. My approach to that for example for the categories would be to export these as a distinct list to a separate table and have someone provide the new code for every old version, and use this table to recode that category. Repeat for every column/object.
posted by IAr at 12:28 PM on October 26, 2023 [6 favorites]

I would pull the data into openrefine to do the actual data cleanup prior to importing. It will make you look (and feel!) like a wizard.
posted by rockindata at 12:53 PM on October 26, 2023

+1 to IAr. Don't clean the data in the source, move it somewhere else and clean it there.

I have two reasons for this:

1. if you clean the data in the source database and you make a mistake -- and you will make a mistake, data cleanup is like that -- you have way less recourse than if you clean it elsewhere.

2. having the original unaltered data aids greatly when you verify or troubleshoot your cleanup and migration work -- when you spot something odd in the destination database it's extremely helpful to be able to see the original inputs that resulted in the questionable outputs.

good luck!
posted by Sauce Trough at 12:54 PM on October 26, 2023 [1 favorite]

Another proposal: don't clean the data, just make sure that the Quickbooks reports you write are smart enough to account for all the myriad ways in which paypal fees and event registrations and etc can be reflected in the data.

This is a better practice because reporting code is much easier and much less risky to change than data. This is a worse practice because your reports will now be more complex, and you haven't solved the problem at the root.

(note, I am a database generalist and not a quickbooks person -- I don't know how reports are written in that software, so this might be a stupid idea)
posted by Sauce Trough at 1:02 PM on October 26, 2023