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.