Skip to main content

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 cut out the middleman and get the output of an SQL query directly into Salesforce. Don't forget the max length variables of Invoke-SqlCmd!

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

# upsert version, won't work if you're including audit fields (CreatedDate etc)
force bulk -c upsert -o objectName -e External_ID__c 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.

Comments

Popular posts from this blog

'A section using configSource may contain no other attributes or elements' error after installing Application Insights

After installing the Application Insights nuget package to an Umbraco solution, you'll get this error:

A section using 'configSource' may contain no other attributes or elements

<ExamineLuceneIndexSets configSource="config\ExamineIndex.config" />
     <log4net configSource="config\log4net.config">
         <root>
             <level value="ALL" />
             <appender-ref ref="aiAppender" />
Source File: \project\web.config

This happens because part of the Application Insights installation process adds a <log4net> section to web.config.  Which would make sense, except Umbraco already has a <log4net> section in /config/log4net.config.  So as you can imagine, the solution is to manually move everything its added into that file. Unfortunately you can't just copy/paste the whole lot, but it's not particularly complicated:


Move <appender-ref ref="aiAppender" /> into the lo…

Handling double-quoted CSVs in Azure Data Factory Pipelines

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"

The ADF pipeline failed because the double-quotes don't get escaped correctly:
ErrorCode=UserErrorSourceDataContainsMoreColumnsThanDefined, found more columns than expected column count.

The solution was to change the "Escape character" property on the dataset, by clicking the "Edit" button beneath it and manually entering "", since "" isn't in the list of escape characters.  I didn't think this would work at fir…

Using WiX to create an event source during install of a .NET framework project

Edit: so I guess I wasn't the only one confused with this stuff, as it's been my most popular post by far!  If I've helped you out or saved you some time, please let me know in the comments :)

In order for this to work, you have to add references to WixUtilExtension and WixNetFxExtension to your WiX project.  Once that's done, add this inside a <Component> element:

<Util:EventSourcexmlns:Util="http://schemas.microsoft.com/wix/UtilExtension"Name="EVENTSOURCEGOESHERE"Log="Application"EventMessageFile="[NETFRAMEWORK40FULLINSTALLROOTDIR]EventLogMessages.dll" />
Obviously replace EVENTSOURCEGOESHERE with your event source name.  NETFRAMEWORK40FULLINSTALLROOTDIR is a property set by the WixNetFxExtension which stores the path to the .NET framework v4 directory, but you can replace this with the corresponding property for the directory containing the relevant EventLogMessages.dll file.  So if you're using the .NET framewo…