how do I clean up my data?

  • 1
  • Problem
  • Updated 5 years ago
  • Not a Problem
  • (Edited)
Archived and Closed

This conversation is no longer open for comments or replies and is no longer visible to community members.

Our audience data is a huge mess. Various imports weren't correctly mapped when the Excel file was imported because we're using ETapestry donor database to export names and email addresses for use in My Emma. ETapestry has only one field for both first and last name. I tried using the wizard within Excel to separate the names into two columns, but since some data has two names plus a last name all in one field (i.e. "Sally & Rob Thomas"), the most recent import now includes things like ampersands where the "last name" is supposed to be in My Emma.

There are also instances where two different email addresses are in the same field. Another product of ETapestry and their whacked donor mgt. software.

I have no idea how to clean this up, using either Excel or MyEmma. 
Photo of Service Dogs of Virginia

Service Dogs of Virginia

  • 13 Posts
  • 3 Reply Likes
  • frustrated

Posted 5 years ago

  • 1
Photo of Brandi Leath

Brandi Leath

  • 531 Posts
  • 67 Reply Likes
Hey there!!! Maybe I can help.  Not sure how Excel savvy you are but there are several ways of going about doing this. 

Try using Text to column method.
Select the column with the names (make sure the columns next to it are empty for now)
then TOOLS>DATA>Text to Column>Delimited>next> (select delimiter such as Space>finish)

You can then name your columns First/Middle/Last so when you import you can separate them.
Photo of Service Dogs of Virginia

Service Dogs of Virginia

  • 13 Posts
  • 3 Reply Likes
I actually did that on the most recent file I imported and that's when the ampersands ended up in the last name field. :-(
Photo of Brandi Leath

Brandi Leath

  • 531 Posts
  • 67 Reply Likes
If it's just the ampersands, do a find and replace and replace with nothing.  Be sure to remove the space as well.  You can do the same for other characters/combos, etc.
Photo of Service Dogs of Virginia

Service Dogs of Virginia

  • 13 Posts
  • 3 Reply Likes
Oh, OK. Hadn't thought of that.... (not very Excel savvy, but trying to learn). I will try it. Thanks! If  I succeed, then when I import again it will overwrite the records in My Emma?
Photo of Brandi Leath

Brandi Leath

  • 531 Posts
  • 67 Reply Likes
I would think so, as long as the email addresses match your imports and click "update" when you import. :)
Photo of Service Dogs of Virginia

Service Dogs of Virginia

  • 13 Posts
  • 3 Reply Likes
I'll try and hope I don't make a bigger mess than already exists! :-) Thanks Brandi!
Photo of Brandi Leath

Brandi Leath

  • 531 Posts
  • 67 Reply Likes
You're welcome!! :)
Photo of Brandi Leath

Brandi Leath

  • 531 Posts
  • 67 Reply Likes
I also found this great tutorial on splitting first and last names using a formula (just in case you have to start over). 
(Edited)
Photo of Service Dogs of Virginia

Service Dogs of Virginia

  • 13 Posts
  • 3 Reply Likes
Thanks!! I did the find/replace on the ampersand in Excel and that worked after a couple of attempts. Now I'm working on the multiple email addresses in one field (cuting and pasting by hand, since I couldnt get the comma sep wizard to work this time). THanks for the tutorial!
Photo of Brandi Leath

Brandi Leath

  • 531 Posts
  • 67 Reply Likes
Oh no, sounds like you have a tedious task ahead of you. hmmm....

Are there any characters in between the email addresses? Like a space or semicolon? Or are they backed up to each other like: brandimykle@gmail.combrandimykle@gmail.com?

Also what version of Excel are you using and about how many rows of data do you have? It might be worth starting over if you have a LOT of data.  Now that you know what happened to mess you up you can go back and prevent it from happening again.
Photo of Service Dogs of Virginia

Service Dogs of Virginia

  • 13 Posts
  • 3 Reply Likes
Yes, exactly. It starts with the export from ETapestry. Their help desk gave me almost the same tutorial you sent... putting stuff into columns. Fortunately, I only had about 20-30 doubled up email addresses, so I just got through cutting & pasting. They were separated by commas w/o a space, so that's why I thought I could use that same Data>change to columns thing, but it didn't work/I couldn't figure it out. I'm sort of flying by the seat of my pants. Know very little about Excel.... I just learned how to get a sum for a column! That's how newbie I am! LOL 

The pressure is on with getting the email addresses into My Emma though, because we need to do an e-blast to our mailing list this week. 

You've been a huge help!! I'm so glad you saw my post on the forum! Thanks again!
Photo of Brandi Leath

Brandi Leath

  • 531 Posts
  • 67 Reply Likes
That's great, only 20-30 ...whew! Glad you got it sorted out!!! Excel is kinda my thing....if you ever need any more help with it feel free to shoot me a message!! :) brandimykle@gmail.com

You're very welcome, glad I was able to help!