Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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!

share|improve this question

2 Answers 2

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.

share|improve this answer
    
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 '11 at 20:09
    
yes this does look promising, thanks for the link –  datatoo Apr 12 '11 at 17:53
up vote 3 down vote accepted

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

http://code.google.com/p/google-refine/

I got a new job right when I found out about refine, or I certainly would have tried it out on some real-world data. at the moment I haven't used it myself, so I can't comment on its quality--but that demonstration video blew me away, and it certainly appears to be the utility I was looking for when I posted this question.

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.