Skip to main content

Posts

Showing posts from January, 2020

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…