3

I routinely receive files, usually Excel spreadsheets, which contain manually-entered data that have outgrown the utility of spreadsheets. I then clean up these files for import into a database.

this is sometimes easy, sometimes painstaking, depending on how "clean" the data are.

for example, something like:

Name            Age     Phone #
J Smith         31 yo   8019219210
Smith, Mary Alice  43     (203) 456-7788

I translate into:

First_Name  Middle_Name Last_Name      Age  Phone_Number
J            NULL        Smith          31   801-921-9210
Mary          Alice       Smith          43   203-456-7788

I'd really love some software that helps me set parameters for what each column "should look like", automatically flag values that are suspect, and permit me to cycle through these to make edits as necessary.

Does anyone know if software like this exists? I have to imagine it does, but I have no idea how to begin finding it.

I'm also open to standard operating procedures that help achieve the same sort of task efficiently.

Thanks!

2 Answers 2

4

Update: Google Refine has been transitioned to a community-driven project, OpenRefine


After I posted this, Google released Google Refine, which appears to have all of the features I was imagining, and then some.

I got a new job right when I found out about Refine, did not have the chance to test it, so I can't comment on its quality. If you've used it and it's useful, please vote this answer up. If it gets a couple votes, I'll select this answer.

0

If your column layout in files you receive is semi-consistent, you could probably write a macro to do most everything. Split a name (with rules for identifying Last followed by a comma etc), make Age a number, format the phone.

You could even have it cycle through and when in doubt conditionally format the row for further attention.

1
  • thanks for these ideas, @datatoo. I think you're right, but I was looking for an existing utility to handle this sort of thing for me--I spent some time with VB Script once and hated it. I have used a combination of Python, text editors and Excel tricks (like flagging cells with substrings matching "Mr" or "Mrs" or "," in name columns, among many others) in my adventures cleaning up messy data--but I was doing the same things over and over again (inefficiently), and thought there might be an existing utility. I did find a candidate--see my answer.
    – Stew
    Apr 8, 2011 at 20:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .