Is data conversion just a matter of clicking the ‘export’ button? If only! For companies and nonprofits, large-scale data conversion can be a complex process that takes time, planning, and key insights into how to do it right.
Here are 5 ways to set yourself up for data conversion success:
#1: Think about the format
Data conversion is useless unless you’re able to maintain integrity throughout the conversion process. That means the data you’re converting stays accurate no matter what form it’s converted to.
In order to ensure complete accuracy, it’s imperative to examine which form you’re starting with, and which form you’re converting to.
- Do I completely understand the format I’m converting to so my data stays accurate?
- Will I need any special tools in order to convert my data? If so, do I know how to use them?
- Do I understand how to convert numerical fields, especially if I’m converting to or from Excel?
Maintaining the integrity of your data throughout conversion is possible – if you plan ahead and think about the “before” and “after” forms of your data.
#2: Play nice with others
Many databases and vendors only accept data in certain forms. When beginning your data conversion – to either send data out for an append or to pull data from your database/CRM for another reason – it’s crucial to consider which formats your append vendor will accept.
For instance, many Massa & Company clients utilize National Change of Address (NCOA) to stay current on customer mailing addresses. However, NCOA is very particular about which data formats they’ll accept – and they make no exceptions!
Also, once your NCOA job is completed, you’ll need to convert the NCOA data file back into your original format and update your database/CRM.
Before choosing which format to export your data into, take a strategic look at how the data is going to be used, and by whom. Make sure the format you chose to convert to is one that is accepted by those who will be using it.
#3: Choose TSV over CSV
CSV stands for comma-separated value. This is a common data format with one fatal flaw – if your data field has a comma in it and you export to Excel, Excel sees the comma as a reason to create an entirely new field.
When this happens, Smith, John could get split into two totally separate customer fields: instead of a single name field of Smith, John you’ll get a name field for Smith and an extra field for John. That separation could potentially be a big problem for the integrity of your data.
To avoid this pitfall, opt for TSV, or tab separated value. Instead of commas, data is separated by tabs. This is particularly helpful when dealing with names – after all, hardly anyone has a tab in their name!
#4: Avoid fixed format files
Fixed format files are data files that are difficult to export and convert unless you have experience doing so. A fixed format file requires a data layout document that describes where (at which character count position) each field in the file begins and ends.
For example, the first name field would begin at position 1 and end at position 15, while the last name field would begin at position 16 and end at position 30.
Here is an example:
If you’re a smaller company or organization, you may want to steer clear of fixed format files when starting a data conversion. It isn’t hard – it’s just more work than you likely have time for.
#5: Watch out for zeros
When converting data fields that contain numbers, be particularly careful about leading zeros. If you export a zip code file into Excel, Excel will look at those numbers and create a numeric field. Once that happens, Excel will drop all leading zeros in that field. Before you know it, the accuracy of every zip code that began with a zero is completely ruined!
This problem also shows up with account numbers. A recent Massa & Company client used account numbers as unique identifiers for their customers. Every account number began with a leading zero. When the company exported their records to various affiliates using Excel, the program dropped all of the leading zeros. When the affiliates then went to import the data into their systems, every single customer record looked like a brand new customer with a brand new account number. Yikes!
To avoid this major headache, give Excel a simple command to view zip codes, account numbers, and other numeric fields as text fields. This insight is just one more way to protect the integrity of your data and save yourself a ton of time and money during and after data conversion.
Food for Thought: Cast Iron Skillet Pizza
I thought I had come up with this great idea to make cast iron skillet pizza by using a focaccia bread dough recipe – it turns out that I wasn’t the only one with this brilliant idea! Cast iron skillet pizza removes the hassle of fussing with a pizza stone trying to shape the pizza dough into a circle. However, this is NOT a last minute pizza recipe. Since the dough has to rise twice, you should start preparing it around mid-afternoon for an evening dinner.
This recipe reminds me of preparing for a data conversion. It takes time, effort, and planning, but the end result is always worth the hard work!
- Follow this recipe for the dough.
- Before the second rise, split the dough into two pieces and put each into a cast iron skillet of its own (if you want two pizzas).
- Make sure that you split the ½ C of olive oil between the two skillets.
- After the second rise, add your toppings to the dough without punching it down!!
Here is how to layer the pizza: I start with a thick layer of Italian sausage that I cook while the dough is rising. I use crumbled Italian sausage because it already has fennel and other Italian herbs in it.
Next, prepare some tomato sauce. For tomato sauce I buy San Marzano whole peeled tomatoes and cook them in a pot and add absolutely nothing to them – they are sweet and delicious. Spread the tomato sauce over the sausage layer.
Cover the tomato sauce with fresh mozzarella then drizzle with olive oil and place in a 425-degree oven for 30 minutes. Put a cookie sheet underneath the skillet to catch any cheese that bubbles over. Keep an eye on the pizzas starting at 20 minutes. Let them sit on the countertop for 5 minutes before slicing. This recipe will make your brains fall out!
Whether you’re the person performing the data conversion or the person overseeing the data conversion, the process is more complex than simply clicking the export button. If you’re not familiar with the issues that can arise during data conversion, get some help – otherwise you’ll run into problems you don’t even know are going to happen.
Are you ready to get your data conversion ducks in a row? Massa & Company can do all the heavy lifting for you. Find out how we can streamline the data conversion process so you can get back to running your business.