Examples of how you can import data to, or export data from Pigment using the Pigment API and Windows PowerShell.
1. Generate Pigment Import/Export API key
https://community.pigment.com/security-permissions-82/manage-api-keys-226
2. For Imports into Pigment (to understand the process)
https://community.pigment.com/importing-and-exporting-data-95/how-to-trigger-an-import-with-apis-230
3. For Exports from Pigment (to understand the process)
4. To import data using PowerShell
# Define the API endpoint and credentials
$API_IMPORT_KEY = 'YOUR_PIGMENT_IMPORT_API_KEY'
$API_IMPORT_URL = 'https://pigment.app/api/import/push/csv?configurationId='
$PIGMENT_IMPORT_CONFIG = 'YOUR_IMPORT_ID'
$PIGMENT_IMPORT_PAYLOAD = 'test.csv'
# Set up the headers for the API request
$headers = @{
"Authorization" = "Bearer $API_IMPORT_KEY"
"Content-Type" = "text/csv"
}
Write-Output "Now trying the import into $PIGMENT_IMPORT_CONFIG....."
try {
# Read the file content
$fileContent = Get-Content -Path $PIGMENT_IMPORT_PAYLOAD -Raw
# Make the API request and import the data
$response = Invoke-RestMethod -Uri "$API_IMPORT_URL$PIGMENT_IMPORT_CONFIG" -Method Post -Headers $headers -Body $fileContent
Write-Output "Import successful!"
} catch {
Write-Output "An error occurred: $_"
}
Explanation:
-
$API_IMPORT_KEY: This variable stores your API key, which is used to authenticate your request.
-
$API_IMPORT_URL: This variable stores the base URL for the Pigment API endpoint.
-
$PIGMENT_IMPORT_CONFIG: This variable stores the configuration ID for the import.
-
$PIGMENT_IMPORT_PAYLOAD: This variable stores the path to the CSV file you want to import.
-
$headers: This hash table sets up the headers for the API request. The
Authorization
header includes the API key, and theContent-Type
header specifies that the content is a CSV file. -
Write-Output: This command prints a message to the console, indicating that the script is attempting to import data into the specified configuration.
-
try: This block starts a try-catch block for error handling.
-
$fileContent: This variable stores the content of the CSV file, read as a single string using the
Get-Content
cmdlet with the-Raw
parameter. -
$response: This variable stores the response from the API request. The
Invoke-RestMethod
cmdlet sends a POST request to the API endpoint with the specified headers and file content. -
Write-Output "Import successful!": This command prints a success message to the console if the import is successful.
-
catch: This block catches any errors that occur during the try block.
-
Write-Output "An error occurred: $_": This command prints an error message to the console if an error occurs, displaying the error message stored in
$_
.
5. To export data using PowerShell
# Define the API endpoint and credentials
$API_EXPORT_KEY = 'YOUR_PIGMENT_EXPORT_API_KEY'
$API_EXPORT_URL = 'https://pigment.app/api/export/view/'
$OUTPUT_FILE = 'view.csv' # CSV file name
$PIGMENT_EXPORT_VIEW = 'YOUR_VIEW_ID'
# Set up the headers for the API request
$headers = @{
"Authorization" = "Bearer $API_EXPORT_KEY"
}
Write-Output "Now trying the export into $PIGMENT_EXPORT_VIEW....."
try {
# Make the API request and export the data
$response = Invoke-WebRequest -Uri "$API_EXPORT_URL$PIGMENT_EXPORT_VIEW" -Headers $headers -Method Get -OutFile $OUTPUT_FILE -UseBasicParsing
Write-Output "Export successful!"
} catch {
Write-Output "An error occurred: $_"
}
Explanation:
-
$API_EXPORT_KEY: This variable stores your API key, which is used to authenticate your request.
-
$API_EXPORT_URL: This variable stores the base URL for the Pigment API endpoint.
-
$OUTPUT_FILE: This variable specifies the name of the CSV file where the exported data will be saved.
-
$PIGMENT_EXPORT_VIEW: This variable stores the view ID for the export.
-
$headers: This hash table sets up the headers for the API request. The
Authorization
header includes the API key. -
try: This block starts a try-catch block for error handling.
-
$response: This variable stores the response from the API request. The
Invoke-WebRequest
cmdlet sends a GET request to the API endpoint with the specified headers and saves the response to the output file. -
Write-Output "Export successful!": This command prints a success message to the console if the export is successful.
-
catch: This block catches any errors that occur during the try block.
-
Write-Output "An error occurred: $_": This command prints an error message to the console if an error occurs, displaying the error message stored in
$_
.
6. To export raw data (table) using PowerShell:
There are occasions when the volume of data contained within a view is too large export, in this situation you can this method.
This Community article explains the different types of ‘raw’ exports. Please read this, this is very important in understanding the raw export types.
- If Table then API_EXPORT_URL=https://pigment.app/api/export/table/
- If Metric then API_EXPORT_URL=https://pigment.app/api/export/metric/
- If List then API_EXPORT_URL=https://pigment.app/api/export/List/
Note: This is a far more complex example than the previously shown.
# Define the API endpoint and credentials
$API_EXPORT_KEY = 'YOUR_PIGMENT_EXPORT_API_KEY'
$API_EXPORT_URL = 'https://pigment.app/api/export/table/'
$OUTPUT_FILE = 'raw.csv' # CSV file name
$PIGMENT_METRIC_ID = 'YOUR_PIGMENT_TABLE_ID'
$CHUNK_SIZE = 10*1024*1024 # 10MB for streaming
# Set up the headers for the API request
$headers = @{
"Authorization" = "Bearer $API_EXPORT_KEY"
"Content-Type" = "application/json;charset=utf-8"
}
# Define the request data
$datareq = @{
"friendlyHeaders" = $true # use the Pigment metric names
}
Write-Output "Now trying the export of $PIGMENT_METRIC_ID....."
try {
# Make the API request and export the data
$response = Invoke-WebRequest -Uri "$API_EXPORT_URL$PIGMENT_METRIC_ID" -Headers $headers -Method Post -Body ($datareq | ConvertTo-Json) -UseBasicParsing
# Stream the response content in chunks
$stream = sSystem.IO.StreamReader]::new($response.RawContentStream)
$outputStream = SSystem.IO.StreamWriter]::new($OUTPUT_FILE, $false, System.Text.Encoding]::UTF8)
while ($null -ne ($line = $stream.ReadLine())) {
$outputStream.WriteLine($line)
}
$outputStream.Close()
$stream.Close()
Write-Output "Export successful!"
} catch {
Write-Output "An error occurred: $_"
}
Explanation:
-
$API_EXPORT_KEY: This variable stores your API key, which is used to authenticate your request.
-
$API_EXPORT_URL: This variable stores the base URL for the Pigment API endpoint.
-
$OUTPUT_FILE: This variable specifies the name of the CSV file where the exported data will be saved.
-
$PIGMENT_METRIC_ID: This variable stores the metric ID for the export.
-
$CHUNK_SIZE: This variable defines the size of each chunk of data to be read from the response (in bytes).
-
$headers: This hash table sets up the headers for the API request. The
Authorization
header includes the API key, and theContent-Type
header specifies that the content is JSON. -
$datareq: This hash table contains the request data, specifying that friendly headers (Pigment metric names) should be used.
-
Write-Output: This command prints a message to the console, indicating that the script is attempting to export data from the specified metric.
-
Write-Output: This command prints a message to the console, indicating that the script is attempting to export data from the specified metric.
This script sends a POST request to the Pigment API to export data from a specified metric, streams the response content in chunks, and writes the data to a CSV file.