Integrating multiple systems is a problem for any business.
In the past few months, it’s been a good problem to have for the Digital Workshop. As our volume of students has increased dramatically, we’ve been trying to figure out how to better handle our multiple systems internally.
One of the most common issues that come with having multiple systems is they don’t talk nicely to each other. By “talk,” I mean data does not transfer easily between them.
For example, a system for human resources data may export a text file of employee info that a payment tracking system doesn’t understand. We may be able to import data into the payment tracking system, but only if it’s set up properly. We are then forced to figure out ways to manipulate the data to prepare it for import.
Microsoft Excel is the industry standard program for data manipulation. When dealing with this type of scenario, Excel has built-in tools to manipulate text, dates, numerical data and more.
One of the best examples of Excel’s power is in dealing with names that are improperly formatted and need to be moved from one system to another.
Let’s say one system generates names in the order of “last name, first name.” However, your other system needs the name data as two normalized fields, such as “last name” and “first name.” At this point, you need to write a formula to split the original data into two values.
The only constant we know about this scenario is that the first and last name values are separated by a comma. When we can recognize a constant of any kind, it helps us shape how to build our formula.
Using Excel’s “Find” function, we can find the starting position of one text string located inside of another. In this case, we use Find to determine where the comma is located. We’ll call that value X.
Next, we can take the value of X and use it nested in the “Left” function, which returns a value that counts the number of X characters from the Left side of the value and returns a result.
After combining Find and Left, all that will remain is the “last name” value including the comma. Subtract one from the value of X and you’ll get a field with the last name by itself, ready for import.
If you give it a try, Excel can help simplify data transfer in a number of ways like this. If you have a tech question you need answered — including more about Excel — visit Facebook.com/fcdigitalworkshop and ask. I’ll answer some of your questions in upcoming columns.
[Orginally published in The Coloradoan on 3/22/13]