Consider the following scenarios:
1. You want a backup of all of your customer data
2. You want to change the software you are using to track sales
Two very reasonable requests, wouldn't you say? Well, try to do either and you will see how easy it truly is!
CSV, Tab and Comma-Delimited Data Files
The common data exchange of most software is the CSV file. This is
the file type that a software program will import or export. Data
fields are separated by tabs, commas or double-quote characters.
Usually, you get to choose which character it is.
Just for fun, try to perform a backup of your precious customer
data. In most applications, this is as easy as pressing the "export"
or "backup" button.
Now try to open the file. Chances are, your system will come back
with some arcane message about not recognizing the data format. If
not, Excel may open the file and display gobble-de-gook.
That, my friends, is the state of your business lifeline!
Why Its a Mess
I hope it's not a mess. Unfortunately, most of the scenarios I come across are a dog's breakfast. Here's why...
Select the tab character as the character that separates fields in
your export. What do you think will happen if you have tabs somewhere
in your data? Exactly! The backup will be confused. It will separate
fields wherever it sees a tab, not where you feel it
should separate fields. Your backup becomes useless until you
painstakingly eliminate every single tab in your entire database.
OK, let's try using another character like a comma to separate the
data fields. What do you figure happens when the export comes to a
job title such as "Manager, Customer Service"? You're right, it
assumes the "job title" field is "Manager" and the next field, say the
"total annual sales", becomes "Customer Service" rather than a number.
Worse, all subsequent fields are shifted accordingly so that usually
means the rest of the entire database is corrupt.
Unfortunately, the user manual doesn't often make this clear.
Worse, you don't find out until disaster strikes. Then, disaster
strikes twice because businesses get this bad news when they want to
recover from a fire, server crash or theft.
Changing Software Applications
If you decide to move from one software application to another, you
face the same challenge. You need to export the data from the program
you are using then import it into the new application. The CSV file is
your vehicle.
There's an old saying regarding databases, "Garbage in, garbage
out." In this case, the data in your current application may be
accessible and useful, but it becomes corrupted when you export. This
makes it "Garbage in" to your spiffy new software. You are stuck with
what you have until you clean up your data.
How to Spare Yourself the Grief?
- Identify if you have a problem. Try
exporting your data. Make sure your current application exports a
clean CSV file. If it does, congratulations, you have no problem. Make
a backup now! Make sure you backup regularly by establishing a daily
and weekly backup schedule and be sure to keep a current copy somewhere
other than your office in case it is hit by a meteor.
- Oh, oh. Now what?
If your application doesn't export clean data, then best to roll up
your sleeves and clean it up, as the problem will not heal itself. You
and your business are exposed, both because you don't have an easy way
to recover after a disaster and because you are captive to the software
you are now using.
If you have a problem, feel free to contact me. I've found a few
resources that will do the dirty work for you. Similarly, if you've
found a solution that works, please write your comment here so others
might benefit.
