Skip to main content

Posts

Exporting from an SQL database (e.g. Dynamics) into Salesforce, without Excel

Whenever you paste content into it, Excel likes to be helpful and reformat it according to what it thinks you're doing (e.g. format dates, interpret formulae), but often with CSVs it gets this wrong and messes up your carefully-formatted raw data. This was getting pretty frustrating, so rather than figuring out workarounds for Excel's weirdness, here's how to go from an SQL query straight into Salesforce with zero copypasta.

# first, write your query and save in query.sql
Invoke-SqlCmd -ServerInstance serverName -Database dbName -InputFile 'query.sql' | Export-Csv -Path 'outputFile.csv' -Encoding ascii -NoTypeInformation
force login
force bulk -c insert -o objectName outputFile.csv

Invoke-SqlCmd is in the SqlServer module, so you'll need that first, and you'll also need the Force.com CLI installed. As a bonus, this method also removes the need to replace NULLs in the output, since they get rendered as an empty field in the CSV.
Recent posts

Count all Salesforce records of all object types owned by a specific user

It's not the fastest or most elegant script, but using the Force.com CLI it's pretty easy to get a count of objects (of all types) owned by a specific user. It assumes that the field to be checked is ownerid, though, so might not work if you have other fields. A slightly better way might be to check which objects have an ownerid field first.

$ErrorActionPreference = 'SilentlyContinue' $objects = force sobject list $ownerId = '00590000001Abcd' # obviously replace this foreach($object in $objects) { $resp = (force query --format json "select count(id) from $object where ownerid='$ownerId'" | convertfrom-json | select -ExpandProperty expr0) $count = 0 if([Int32]::TryParse($resp, [ref]$count) -and $count -gt 0) { "$object : $count" } }

OTF fonts (e.g. Fira Code) look nasty on gVim in Windows

I really like the "Fira Code" font but it always looked nasty in gVim on Windows (my main text editor besides VS), and I really don't want to use a different font for each editor. After spending literally hours fiddling with stuff like the guifont setting, different font/size combos, custom font renderers (e.g. MacType), I finally realised the fonts themselves aren't the problem, nor the rendering settings.  The problem was that the chocolatey installer I had used installed OTF versions of the files, which gVim always used by default even if the TTF versions of them were installed. Uninstalling OTFs and installing TTFs fixed the problem immediately, and now once again I have consistent font rendering across all my text editors. Hooray.

Bonus font opinions:
Best: Fira Code, Cascadia Code, Source Code Pro (in that order)
Nope: Envy Code R, Inconsolata, Hack, Oxygen Mono, Anonymous Pro

Powershell error: "Retrieving COM class factory failed: class not registered (REGDB_E_CLASSNOTREG)"

The problematic command and full error message was:

>get-webbinding
"get-webconfigurationproperty : Retrieving the COM class factory for component with CLSID {688EEEE5-6A7E-422F-B2E1-6AF00DC944A6} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))."

I was pretty stuck on the above error while trying to run a script for setting up sites in IIS which involved some calls to get-webbinding (in the WebAdministration module). After much digging around I finally ran into this post. Turns out, genius that I am, I had created a shortcut to the x86 version of Powershell when I first created the VM, so I'd been using the wrong bitness the entire time. I'm surprised I didn't have more issues!  Anyway, running the 64-bit (i.e. regular) version of Powershell fixed the problem immediately. Thanks jasonint32 ;)


Set default program for opening files without extensions in Windows

For some reason, downloading the request / result CSV files for a Salesforce "Bulk Data Load" job returns CSV files with the file extension missing, so you can't just click to open them. This became super annoying during a troublesome data load so I decided to fix it once and for all.  Open cmd as admin and enter the following:

assoc .="No Extension" ftype "No Extension"="C:\ProgramData\chocolatey\lib\csvfileview\tools\CSVFileView.exe" "%1"

Obviously substitute in your favourite CSV editor (unless by some bizarre coincidence, you also like CSVFileView and install everything with chocolatey).  Credit for the solution goes to this post on superuser.

Powershell - normalizing line endings for all files in a directory

If you're working with a git repository, a much better way to solve this is by changing the core.autocrlf setting.  In this case I didn't have that option, and needed a quick way to update thousands of files so the EOL chars would match in a binary diff tool.  Here's my slightly modified version of this SO answer:

$items = get-childitem -Path "C:\path\to\dir\" -Recurse -File foreach($original_file in $items) { $text = [IO.File]::ReadAllText($original_file.FullName) -replace "`r`n", "`n" [IO.File]::WriteAllText($original_file.FullName, $text) }