CSV, Excel, and Salesforce

The upside to modern PCs is that so many file associations are created for us automatically. This is offset to a degree because the default setting in Windows is to hide file extensions, so that what you see is just the name and an icon. This is especially problematic for CSV files as most people who use Windows have Excel and since the friendly green icon is fronting the file the habit to just click it prevails.

Sometimes, this association is fine. Simple text data delimited with commas or tabs can be converted to Excel format with no manual intervention and everything looks fine. But, if any of the fields have commas, or are dates or numbers, than Excel makes lots of assumptions that it doesn’t tell you about and changes the data to match the assumptions. One does not need to be a data scientist to know this is bad. Indeed, one only needs a pulse to be annoyed by it, and if you don’t know why the data is being messed up, frustration is a common reaction.

The first thing I recommend is to change your Window settings to show file extensions. There are instructions provided by Microsoft for this here.

Next, develop the habit of opening CSV files using one of the more time-consuming (and reliable) methods. Method 1 is to open the CSV file in a text editor (my personal preference is EditPad, and there is a good list of others here). Then create a new Excel workbook or sheet, copy the contents of the CSV file from the text editor and use the Paste > Use Text Import Wizard option.

Use Text Import Wizard paste option
Use Text Import Wizard paste option

The simplest approach is to accept the defaults on the first two steps and on the third step select all columns by holding the Shift key, scrolling to the right and click the last column, and choose the Text column format.

Text Import Wizard Step 3 select all columns and Text format
Text Import Wizard Step 3 select all columns and Text format

This will create a clean separation by column with no auto-formatting applied. Then Finish and you will have the data as you expected.

For larger files, I suggest reading this thread on SuperUser.com.

When creating data in Excel for CSV upload, format all the columns as Text before saving as CSV.  If you have to do the same data transforms regularly, I recommend creating a template with formulas.

Probably the hardest habit of all for most users to adopt is when opening the file in Excel just to view its contents is to select No when prompted to save the changes.

© Scott S. Nelson

Salesforce Trailhead “Challenge not yet complete” most common cause

Ah, Spring is in the air. So are arms, as people new to Salesforce throw them up during Trailhead challenges where they can’t seem to get the hands-on part to pass even though they see the result they expect.

The Trailhead modules and Superbadges are so well organized and written, it may seem like there is an instructor reviewing you submissions, but that would not be practical, profitable, or in the spirit of a cloud platform. The scoring is done by automated tests that are checking that things match exactly as the instructions provided.

The most common cause is that the learner has mis-typed a value provided, usually the API name (i.e., my_variable__c). Runner up to this is the experienced user who is new to Trailhead and uses their own naming conventions rather than following the instructions (been there, done that).

The third common cause is that the module content was updated but the test was not (doesn’t happen that often, but you can tell when there are a bunch of questions on the Trailhead Community about the same problem).


© Scott S. Nelson

How to re-assign Salesforce object ownership in bulk

Here is a question I see frequently in the Trailhead Community: How do you re-assign the ownership of objects in bulk? I’m sure there is an app to make this easier, and I suggest you look in the AppExchange if the policy for your org is to use apps first. And I know there are other solutions, this is just one that I find quick and easy because I don’t have to give it too much thought.

Accounts before Owner Change

First: Always try it in a sandbox first and test the results before doing it in production.

Second first, locate the user id of the person who’s object owners ship you want to change from. From their user page (or just copying the link), grab the object ID from the URL.

Find Object Owner

Copy Object ID
Copy Object ID

Now open Salesforce Workbench in another tab, login and navigate to Queries > SOQL queries. If you are adept at SOQL, create a query to pull all of the object you want to re-assign based on the user id. If you are not comfortable writing your own SOQL or just feeling lazy, use Workbench to help create the query:

Select Query in Workbench

The key values you want are the object ID and the owner ID, though adding a Name field to check the values can be helpful. Be sure to select View as Bulk CSV then run the query. Save the resulting file. I recommend naming it something that is meaningful to you rather than accepting the long string produced by default. Open in your favorite CSV editor to see the object and owner ids.

Next, get the user ID of the new owner(s) the same way you did for the current owner. In the CSV file, change the OwnerId value as desired.

For safety reasons, delete all columns from the csv file except Id and OwnerId, then save it with a different name as a CSV file.

Object and Owner IDs only

Back in Workbench, go to Data > Update. Select the object type and click the Choose File button and select the file you just created with the new OwnerId(s), then click Next.

On the mapping screen the values should already be mapped. Check that only the ID and OwnerId are mapped and click the Map Fields button. If there are many records, check the “Process records asynchronously via Bulk API” option on the resulting page then Confirm Update. Note that if rules were changed since the records were created you may have errors that will require fixing the records before they can be updated.

Could happen if rules changed since created

And in list view:

After Owner Change

© Scott S. Nelson

Salesforce Deployments when Other People’s Code Fails Validation

As a cloud architecture consultant I have always applauded Salesforce for requiring 75% test coverage for deployments. I just wish that it was a minimum per class rather than an average per org. Why? Because things change and over time the average in production can come down to the point where adding a new change set that is at 75% when averaged with what is in production drops the total average. Because of this, I have set the standard for my team as 95%, which always got us through this issue until recently.

Another team had run a deployment that removed methods previous tests relied on. When my change set was validated, I was surprised to see the following error:

Method does not exist or incorrect signature
Method does not exist or incorrect signature

What was most vexing about this is that none of the classes in error were touched by my change set. Usually this can be fixed with a recompile, but not this time. What to do? Well, someone else had gotten a deployment through (there are multiple teams working in the org), so I knew there had to be some solution. And there was! Sparing you the dozen things I tried that did not work, here is the solution:

Run specified tests

And the proof:

Ready for Quick Deployment
© Scott S. Nelson