by Cloud for Good Blog | Jun 23, 2015 | Blog
There are times, in the life of a database administrator, when you just have to go back to good ol’ Excel. We use it for taking data apart, for finding duplicates and flagging them, for sorting up and down to find anomalies and errors. We use it to replace whole columns of one number with another. Francis Scudellari from the Cloud for Good team started this discussion with this excellent post, Keeping it Clean: The Data Import Basics. Check that out to get started thinking about your data importing. One of the most common uses, however is for putting cells TOGETHER. Usually this happens when I have a FirstName column and a LastName column and we need a Full Name column also. Sometimes we need to put one line of the address together with the second line. Excel calls this function “concatenation,” but I always think of it as “sticking things together.” Let’s see just how easy this is. We start with a name issue. Here we have a list of names. Some are husbands and wives, but for the first example, all we want to do is get full names for everybody. Say I’m addressing envelopes for an invitation and I want to invite each individual. I’ve exported my contacts pages, and I’m looking at split names. I need to find a way to get them all into one cell. Click the formula button above your table. It looks like fx. Notice that I’ve already created the “destination” column. Your concatenation needs someplace to go! Insert function comes up and CONCATENATE is at the top of the list!...
by Cloud for Good Blog | Jun 16, 2015 | Blog
This is a cross post from the Cloud for Good blog. As Francis wrote a few weeks ago, it’s important to import clean data into your Salesforce system. Gaining & keeping users’ trust is especially critical at the beginning of a project. When we work on a QuickStart project, the client is always tasked with preparing their data in a spreadsheet file with all the columns named and set-up. Even if you’re paying a consultant to manage your data import, you can save money by making sure you’re giving them the best data possible for import. And consultants won’t be able to catch anomalies in your data the way someone who’s more familiar with it can. Whether you’re migrating from another database, or finally moving away from multiple spreadsheets to manage your data, there are a few principles you want to keep in mind, and some tools you can use in Excel or another spreadsheet program to achieve them. SEPARATING COUPLES’ NAMES Many organizations have data where both members of the couple are listed in one field. If your data has a separate Last Name column, and you have a single column in which couples’ names are together, you’ll want to separate them (unlike Trish’s post from last week about sticking them together). You’ll have a lot more flexibility going forward with Salesforce if we use the 1 Contact = 1 Person concept consistently. What you can do for data import, that will make your data preparation much easier, is to just separate the column with the two first names together into two columns: If there is a mix of “Chris &...
