Get the maximum length of every field in a CSV, Excel etc
Jun 30, 2019
While setting up tables in our data warehouse for use with some external APIs, it was getting a little tedious to figure out the max length of some long fields to get the write size for CREATE TABLE statements, so I wrote this little script to calculate it for me. Yeah, it’s kinda messy, so sue me (or don’t). You can swap out the import-csv part to also import from Excel or whatever else
...
➦
Handling double-quoted CSVs in Azure Data Factory Pipelines
Jun 12, 2019
Azure Data Factory by default uses a backslash as the escape character for CSVs, but we ran into an issue with this today processing one of the CSV files from data.gov.au. As with most CSVs they use quotes around values as normal and with double-quotes for empty values, but they also use double-quotes to escape quotes within non-empty values. This probably sounds confusing, so here’s an example:
"column 1”,“column 2”,"“,“column 4 value is "“sort of"“ like this"
...
➦
Handy SOQL query snippets
Jun 04, 2019
To count how many people logged in for the last time each year, for only inactive users:
select calendar_year(lastlogindate) LoginYear,count(id) Totalfrom userwhere isactive = falseand lastlogindate <> nullgroup by calendar_year(lastlogindate)order by count(id) desc
Formats for dealing with dates and datetimes in Salesforce
Apr 23, 2019
The correct way to format dates (not datetimes!) for bulk importing CSVs into Salesforce looks like year-month-day, so today would look like 2019-06-17. These default to the timezone set for the user importing them, so there’s no need to convert to UTC. Set this in Excel by
Select the column/s containing datesRight click on them and select “Format Cells” from the menuSelect the format “2012-03-14"Save as CSV (not the UTF-8 type of CSV)
...
➦
DIY Salesforce partial copy sandbox using SandboxBerry
Apr 17, 2019
Salesforce partial copy sandboxes are expensive, but it’s possible to approximate one if you’re willing to spend a little time moving the data around yourself with a fantastic little tool called SandboxBerry, which you can get from here. Big thanks to Ian Finch for all his work on it!
Below are the steps I had to go through to get all my data imported. Please let me know if you have any suggestions that could make this easier or more effective!
...
➦
Useful Azure AD Powershell snippets
Apr 14, 2019
I’ve been doing a lot of Azure AD stuff lately, so here are some powershell snippets that have been coming in handy.
I always forget to Connect-AzureAD first, so don’t do that
Get extension properties of a user (e.g. created date)
Get-AzureADUser -SearchString ‘username or email addy’ | select -ExpandProperty ExtensionProperty
Get guest users that are not members of a specified group
$allGuests = Get-AzureADUser -Filter “usertype eq ‘guest’” -All $true
...
➦
Get details and creators of all Salesforce reports that reference a specified field
Apr 09, 2019
If you’re ever modifying a Salesforce field and you want to contact everyone who has created reports using the field, here’s how you do that using Powershell and the Force.com CLI (which you can also install via chocolatey):
set-location C:\wherever
force export
$reportFiles = get-childitem -Path .\src\reports\ -Recurse | sls “field_to_search_for"
# I’ve included some extra fields here, they might come in useful
$reportData = force query –format=json “select id, createdby.
...
➦
Salesforce bulk data load job fails with message 'InvalidBatch : Field name not found: Id'
Apr 08, 2019
After creating a bulk / batch job, I’ll sometimes see that it failed before even starting (failed batches:1, but with 0 records failed), with the following error:
InvalidBatch : Field name not found: Id
Annoyingly, this is caused by the inability of the Salesforce bulk import process to handle anything but ASCII. Try saving your CSV again, making sure it’s in ASCII (called “CSV (Comma delimited)” in Excel) and not using UTF-8 or some other encoding.
...
➦
Azure AD error - 'IDX10311: RequireNonce is true but validationContext.Nonce is null'
Mar 13, 2019
I’ve experienced this one enough times now that I’d like to preserve the solution/s for future occurrences. The exact message is:
IDX10311: RequireNonce is ‘true’ (default) but validationContext.Nonce is null. A nonce cannot be validated. If you don’t need to check the nonce, set OpenIdConnectProtocolValidator.RequireNonce to ‘false’.An easy way to reproduce this one is to hit the “back” button after having signed in to an ASP.NET web app that auths with Azure AD using the OWIN library.
...
➦
Testing Sendgrid SMTP without sending emails using sandbox mode
Mar 03, 2019
Here’s how to test out a Sendgrid integration using SMTP without actually sending emails. Obviously make sure you have Sendgrid SMTP set up in your config first, and then just add the X-SMTPAPI header (docs link) with some JSON like the below.
MailMessage msg = new MailMessage();
var json = @"{
““mail_settings”": {
““sandbox_mode”": {
““enable”": true
}
}
}";
msg.Headers.Add(“X-SMTPAPI”, json);
// all the usual email stuff
SmtpClient smtpclient = new SmtpClient();
...
➦
