278 lines
8.2 KiB
PowerShell
278 lines
8.2 KiB
PowerShell
#!/usr/bin/env pwsh
|
|
|
|
<#
|
|
.SYNOPSIS
|
|
Data export wrapper for Salesforce CLI with SOQL query support
|
|
|
|
.DESCRIPTION
|
|
A user-friendly wrapper around 'sf data export' that simplifies data export
|
|
from Salesforce orgs with SOQL query support, multiple formats, and intelligent defaults.
|
|
|
|
.PARAMETER qy
|
|
SOQL query to export data
|
|
|
|
.PARAMETER fl
|
|
File containing SOQL query
|
|
|
|
.PARAMETER so
|
|
Standard object query (exports common fields)
|
|
|
|
.PARAMETER ot
|
|
Output file path (default: export.csv)
|
|
|
|
.PARAMETER to
|
|
Target org username or alias
|
|
|
|
.PARAMETER fm
|
|
Output format: csv, json (default: csv)
|
|
|
|
.PARAMETER bk
|
|
Use bulk API for large datasets
|
|
|
|
.PARAMETER wt
|
|
Wait time in minutes (default: 10)
|
|
|
|
.PARAMETER ve
|
|
Enable verbose output
|
|
|
|
.PARAMETER hp
|
|
Show this help message
|
|
|
|
.EXAMPLE
|
|
.\sf-data-export.ps1 -qy "SELECT Id, Name FROM Account LIMIT 100"
|
|
.\sf-data-export.ps1 -so Account -fm json -ot accounts.json
|
|
.\sf-data-export.ps1 -fl queries/contacts.soql -bk -wt 15
|
|
.\sf-data-export.ps1 -qy "SELECT Id FROM User" -to production
|
|
|
|
.NOTES
|
|
This script automatically checks for Salesforce CLI installation and runs
|
|
diagnostics if the CLI is not found.
|
|
#>
|
|
|
|
param(
|
|
[string]$qy,
|
|
[string]$fl,
|
|
[string]$so,
|
|
[string]$ot = "export.csv",
|
|
[string]$to,
|
|
[ValidateSet("csv", "json")]
|
|
[string]$fm = "csv",
|
|
[switch]$bk,
|
|
[int]$wt = 10,
|
|
[switch]$ve,
|
|
[switch]$hp
|
|
)
|
|
|
|
# Show help if no parameters provided or help requested
|
|
if ($hp -or (-not $qy -and -not $fl -and -not $so)) {
|
|
Get-Help $MyInvocation.MyCommand.Path -Detailed
|
|
exit 0
|
|
}
|
|
|
|
# Function to check if Salesforce CLI is installed
|
|
function Test-SalesforceCLI {
|
|
try {
|
|
$null = Get-Command sf -ErrorAction Stop
|
|
return $true
|
|
} catch {
|
|
return $false
|
|
}
|
|
}
|
|
|
|
# Function to run sf-check diagnostics
|
|
function Invoke-SalesforceCheck {
|
|
$checkScript = if (Test-Path "sf-check.ps1") {
|
|
".\sf-check.ps1"
|
|
} elseif (Test-Path "sf-check.sh") {
|
|
"bash sf-check.sh"
|
|
} else {
|
|
$null
|
|
}
|
|
|
|
if ($checkScript) {
|
|
Write-Host "Running Salesforce CLI diagnostics..." -ForegroundColor Yellow
|
|
Invoke-Expression $checkScript
|
|
} else {
|
|
Write-Host "Salesforce CLI not found and no diagnostic script available." -ForegroundColor Red
|
|
Write-Host "Please install the Salesforce CLI: https://developer.salesforce.com/tools/salesforcecli" -ForegroundColor Red
|
|
}
|
|
}
|
|
|
|
# Function to build standard object query
|
|
function New-SObjectQuery {
|
|
param([string]$SObjectType)
|
|
|
|
switch ($SObjectType) {
|
|
"Account" {
|
|
return "SELECT Id, Name, Type, Industry, Phone, Website, BillingCity, BillingState, BillingCountry FROM Account"
|
|
}
|
|
"Contact" {
|
|
return "SELECT Id, FirstName, LastName, Email, Phone, AccountId, Account.Name FROM Contact"
|
|
}
|
|
"Lead" {
|
|
return "SELECT Id, FirstName, LastName, Email, Phone, Company, Status, Source FROM Lead"
|
|
}
|
|
"Opportunity" {
|
|
return "SELECT Id, Name, AccountId, Account.Name, Amount, CloseDate, StageName, Probability FROM Opportunity"
|
|
}
|
|
"Case" {
|
|
return "SELECT Id, CaseNumber, Subject, Status, Priority, Origin, AccountId, Account.Name, ContactId, Contact.Name FROM Case"
|
|
}
|
|
"User" {
|
|
return "SELECT Id, Name, Email, Username, Profile.Name, IsActive, LastLoginDate FROM User"
|
|
}
|
|
default {
|
|
return "SELECT Id, Name FROM $SObjectType"
|
|
}
|
|
}
|
|
}
|
|
|
|
# Function to validate SOQL query
|
|
function Test-SOQLQuery {
|
|
param([string]$QueryText)
|
|
|
|
if ($QueryText -notmatch '^\s*SELECT\s+') {
|
|
Write-Host "Error: Query must start with SELECT" -ForegroundColor Red
|
|
return $false
|
|
}
|
|
|
|
return $true
|
|
}
|
|
|
|
# Silently check for Salesforce CLI
|
|
if (-not (Test-SalesforceCLI)) {
|
|
Invoke-SalesforceCheck
|
|
exit 1
|
|
}
|
|
|
|
# Validate that exactly one query method is specified
|
|
$queryMethods = @($qy, $fl, $so | Where-Object { $_ }).Count
|
|
if ($queryMethods -eq 0) {
|
|
Write-Host "Error: Must specify one of: -qy, -fl, or -so" -ForegroundColor Red
|
|
Write-Host ""
|
|
Write-Host "Usage examples:" -ForegroundColor Yellow
|
|
Write-Host " .\sf-data-export.ps1 -qy `"SELECT Id, Name FROM Account`"" -ForegroundColor Gray
|
|
Write-Host " .\sf-data-export.ps1 -fl queries/accounts.soql" -ForegroundColor Gray
|
|
Write-Host " .\sf-data-export.ps1 -so Account" -ForegroundColor Gray
|
|
Write-Host ""
|
|
Write-Host "Use -hp for detailed usage information." -ForegroundColor Yellow
|
|
exit 1
|
|
}
|
|
|
|
if ($queryMethods -gt 1) {
|
|
Write-Host "Error: Can only specify one of: -qy, -fl, or -so" -ForegroundColor Red
|
|
exit 1
|
|
}
|
|
|
|
# Determine the final query
|
|
$finalQuery = ""
|
|
if ($qy) {
|
|
$finalQuery = $qy
|
|
Write-Host "Using inline query" -ForegroundColor Green
|
|
} elseif ($fl) {
|
|
if (-not (Test-Path $fl)) {
|
|
Write-Host "Error: Query file not found: $fl" -ForegroundColor Red
|
|
exit 1
|
|
}
|
|
$finalQuery = Get-Content $fl -Raw
|
|
Write-Host "Using query from file: $fl" -ForegroundColor Green
|
|
} elseif ($so) {
|
|
$finalQuery = New-SObjectQuery $so
|
|
Write-Host "Using standard query for $so" -ForegroundColor Green
|
|
}
|
|
|
|
# Validate the query
|
|
if (-not (Test-SOQLQuery $finalQuery)) {
|
|
exit 1
|
|
}
|
|
|
|
# Build the sf command based on bulk vs regular query
|
|
if ($bk) {
|
|
# Use Bulk API 2.0 for large datasets
|
|
$sfArgs = @("data", "export", "bulk", "--query", $finalQuery, "--output-file", $ot, "--result-format", $fm)
|
|
|
|
if ($wt -ne 10) {
|
|
$sfArgs += "--wait"
|
|
$sfArgs += $wt.ToString()
|
|
}
|
|
|
|
Write-Host "Using Bulk API 2.0" -ForegroundColor Yellow
|
|
} else {
|
|
# Use regular data query for smaller datasets
|
|
$sfArgs = @("data", "query", "--query", $finalQuery, "--output-file", $ot, "--result-format", $fm)
|
|
}
|
|
|
|
# Add optional parameters
|
|
if ($to) {
|
|
$sfArgs += "--target-org"
|
|
$sfArgs += $to
|
|
Write-Host "Target org: $to" -ForegroundColor Cyan
|
|
}
|
|
|
|
Write-Host "Output format: $fm" -ForegroundColor Cyan
|
|
Write-Host "Output file: $ot" -ForegroundColor Cyan
|
|
|
|
# Note: sf data commands don't support --verbose flag
|
|
# VerboseOutput only affects the script's own output (query preview)
|
|
|
|
# Display export information
|
|
Write-Host ""
|
|
Write-Host "📊 Starting Data Export" -ForegroundColor Blue
|
|
Write-Host "=======================" -ForegroundColor Blue
|
|
|
|
# Show query preview if verbose
|
|
if ($ve) {
|
|
Write-Host ""
|
|
Write-Host "📝 SOQL Query:" -ForegroundColor Yellow
|
|
Write-Host "----------------------------------------" -ForegroundColor Gray
|
|
Write-Host $finalQuery -ForegroundColor Gray
|
|
Write-Host "----------------------------------------" -ForegroundColor Gray
|
|
}
|
|
|
|
# Display the command being run
|
|
Write-Host ""
|
|
Write-Host "Executing: sf $($sfArgs -join ' ')" -ForegroundColor Gray
|
|
Write-Host ""
|
|
|
|
# Execute the command
|
|
try {
|
|
& sf @sfArgs
|
|
$exitCode = $LASTEXITCODE
|
|
|
|
Write-Host ""
|
|
if ($exitCode -eq 0) {
|
|
Write-Host "✅ Data export completed successfully!" -ForegroundColor Green
|
|
|
|
# Show file information if it exists
|
|
if (Test-Path $ot) {
|
|
$fileInfo = Get-Item $ot
|
|
$fileSize = if ($fileInfo.Length -gt 1MB) {
|
|
"{0:N1} MB" -f ($fileInfo.Length / 1MB)
|
|
} elseif ($fileInfo.Length -gt 1KB) {
|
|
"{0:N1} KB" -f ($fileInfo.Length / 1KB)
|
|
} else {
|
|
"$($fileInfo.Length) bytes"
|
|
}
|
|
|
|
if ($fm -eq "csv") {
|
|
# Count records (excluding header)
|
|
$recordCount = (Get-Content $ot).Count - 1
|
|
Write-Host "📁 Exported $recordCount records to $ot ($fileSize)" -ForegroundColor Cyan
|
|
} else {
|
|
Write-Host "📁 Data exported to $ot ($fileSize)" -ForegroundColor Cyan
|
|
}
|
|
}
|
|
|
|
if ($ve) {
|
|
Write-Host "💡 Use a spreadsheet application or text editor to view the exported data" -ForegroundColor Yellow
|
|
}
|
|
} else {
|
|
Write-Host "❌ Data export failed with exit code: $exitCode" -ForegroundColor Red
|
|
Write-Host "💡 Check query syntax and permissions" -ForegroundColor Yellow
|
|
exit $exitCode
|
|
}
|
|
} catch {
|
|
Write-Host "Error executing sf command: $($_.Exception.Message)" -ForegroundColor Red
|
|
exit 1
|
|
}
|