Introduction
Automating access and retrieval can streamline processes and enhance efficiency when working with Dropbox data. This article will demonstrate how to connect to the Dropbox API in PowerShell. With the Dropbox Connector, which is part of the ZappySys ODBC PowerPack, you can seamlessly connect, read, and write data from Dropbox using PowerShell scripts.
Prerequisites
- A Dropbox account.
- Download and install the ZappySys ODBC PowerPack.
Steps
Step 1: Create/Select an App from the Dropbox Developers Page
-
Log into your Dropbox account.
-
Go to Dropbox Developers and click the Create app button to create a new app.
-
Select the Scoped access option when prompted.
-
Choose Full Dropbox to access all files and folders, or App folder to access files in specific folders.
-
Provide a name for your App and click Create App.
-
Under the Permission type section, click Scoped App to select application scopes.
-
Choose all Individual Scopes and Team Scopes if you need to manage team data, then click Submit.
-
Navigate to the Settings tab and copy the App key and App secret to Notepad.
Step 2: Create a new driver
-
Open the ODBC Data Source by typing “ODBC” in the search box and launching the ODBC Data Source.
-
Go to the User DSN or System DSN tab to access the data source for yourself or other users. Add a new System DSN for SQL Server Integration by clicking the “Add” button.
-
Select ZappySys API Driverfrom the driver list, then choose Dropbox in the connector list and press Continue.
Step 3: Dropbox connector configuration
- Fill in the connector fields, including Client ID, Client Secret, and Scopes (separated by spaces). Example scopes:
account_info.read account_info.write files.metadata.write files.metadata.read files.content.write files.content.read - Generate the token and test the connection.
- Go to the Preview Tab, select any table, and preview the result. Press OK to save the configuration.
Step 4: Read Dropbox data using PowerShell
Once the ODBC driver is configured, use PowerShell to interact with Dropbox data.
Basic PowerShell Script to Retrieve Data
# Configure the connection string and query
$connectionString = "DSN=API Driver - DropBox"
$query = @"
SELECT
Id, Name, PathDisplay, Size, ServerModified
FROM list_folder
"@
# Create the ODBC connection
$conn = New-Object System.Data.Odbc.OdbcConnection($connectionString)
try {
# Open the connection
$conn.Open()
# Create the ODBC command
$cmd = $conn.CreateCommand()
$cmd.CommandText = $query
# Create the adapter and data table
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter($cmd)
$table = New-Object System.Data.DataTable
# Fill the table with data
$adapter.Fill($table) | Out-Null
# Check if data is available
if ($table.Rows.Count -eq 0) {
Write-Host "No files found in Google Drive."
} else {
Write-Host "Files found: $($table.Rows.Count)"
Write-Host "-----------------------------"
# Print the retrieved data with null value handling
foreach ($row in $table.Rows) {
$id = if ($row["Id"]) { $row["Id"] } else { "N/A" }
$name = if ($row["Name"]) { $row["Name"] } else { "Unnamed" }
$mimeType = if ($row["PathDisplay"]) { $row["PathDisplay"] } else { "Unknown" }
$size = if ($row["Size"]) { $row["Size"] } else { "0" }
$ServerModified = if ($row["ServerModified"]) { $row["ServerModified"] } else { "N/A" }
Write-Host "$id - $name ($mimeType) - $size bytes - Server Modified: $ServerModified"
}
}
}
catch {
Write-Host "Query error: $_"
}
finally {
# Close the connection
$conn.Close()
$conn.Dispose()
}
Read values in a console, save the script to a file and then execute this command inside the PowerShell terminal, You can also use even a simpler command inside the terminal, e.g. powershell .\Dropboxdsn.ps1:
Retrieving all fields
- If you want to retrieve all columns you can use this variant of the code:
# Configure the connection string and query
$connectionString = "DSN=API Driver - DropBox"
$query = "SELECT * FROM list_folder"
# Create the ODBC connection
$conn = New-Object System.Data.Odbc.OdbcConnection($connectionString)
try {
# Open the connection
$conn.Open()
# Create the ODBC command
$cmd = $conn.CreateCommand()
$cmd.CommandText = $query
# Create the adapter and data table
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter($cmd)
$table = New-Object System.Data.DataTable
# Fill the table with data
$adapter.Fill($table) | Out-Null
# Check if data is available
if ($table.Rows.Count -eq 0) {
Write-Host "No files found in Dropbox."
} else {
Write-Host "Files found: $($table.Rows.Count)"
Write-Host "-----------------------------------"
# Get all column names dynamically
$columns = $table.Columns.ColumnName
# Print the column headers
Write-Host ($columns -join " | ")
# Print each row with all column values
foreach ($row in $table.Rows) {
$values = @()
foreach ($col in $columns) {
$values += if ($row[$col]) { $row[$col] } else { "N/A" }
}
Write-Host ($values -join " | ")
}
}
}
catch {
Write-Host "Query error: $_"
}
finally {
# Close the connection
$conn.Close()
$conn.Dispose()
}
- Run, and it will be successful; all column values are listed in the console
Using a full ODBC connection string (Without DSN)
Instead of relying on a DSN, use a full ODBC connection string:
$connectionString = "DRIVER={ZappySys API Driver};ServiceUrl=https://api.dropboxapi.com/2/;Provider=DropBox;AuthName=OAuth;ClientId=XXXXXXX;ClientSecret=XXXXXXXX;AccessToken=sl.B5IK2w5vXXXXXX;..."
Note: The ODBC connection string length is limited. If it’s too long, save it in a file and reference it using
SettingsFile=C:\path\to\config.txt
e.g,DRIVER={ZappySys API Driver};SettingsFile=C:\temp\odbc-connection-string.txt.
Step 5: Writing Dropbox Data to a File in PowerShell
You can export Dropbox data to different file formats, like JSON and HTML files, using PowerShell with this code:
# Ensure the output directory exists
$OutputDir = "C:\Temp"
if (!(Test-Path -Path $OutputDir)) {
New-Item -ItemType Directory -Path $OutputDir | Out-Null
}
# Configure the connection string and query
$connectionString = "DSN=API Driver - DropBox"
$query = "SELECT * FROM list_folder"
# Define output file paths
$jsonFile = "$OutputDir\DropBoxFiles.json"
$htmlFile = "$OutputDir\DropBoxFiles.html"
# Create the ODBC connection
$conn = New-Object System.Data.Odbc.OdbcConnection($connectionString)
try {
# Open the connection
$conn.Open()
# Create the ODBC command
$cmd = $conn.CreateCommand()
$cmd.CommandText = $query
# Create the adapter and data table
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter($cmd)
$table = New-Object System.Data.DataTable
# Fill the table with data
$adapter.Fill($table) | Out-Null
# Check if data is available
if ($table.Rows.Count -eq 0) {
Write-Host "No files found in DropBox."
} else {
Write-Host "Files found: $($table.Rows.Count)"
Write-Host "Saving data..."
# Convert DataTable to an array of objects for easy export
$dataArray = @()
foreach ($row in $table.Rows) {
$rowObject = @{}
foreach ($col in $table.Columns.ColumnName) {
$rowObject[$col] = $row[$col] -as [string] # Convert values to strings to avoid issues
}
$dataArray += $rowObject
}
# Save as JSON
$dataArray | ConvertTo-Json -Depth 3 | Set-Content -Path $jsonFile
Write-Host "JSON file saved: $jsonFile"
# Save as HTML
$htmlContent = "<html><head><title>DropBox Files</title></head><body>"
$htmlContent += "<h2>DropBox Files</h2><table border='1'><tr>"
# Add column headers
foreach ($col in $table.Columns.ColumnName) {
$htmlContent += "<th>$col</th>"
}
$htmlContent += "</tr>"
# Add row data
foreach ($row in $dataArray) {
$htmlContent += "<tr>"
foreach ($col in $table.Columns.ColumnName) {
$htmlContent += "<td>$($row[$col])</td>"
}
$htmlContent += "</tr>"
}
$htmlContent += "</table></body></html>"
# Save HTML file
$htmlContent | Set-Content -Path $htmlFile -Encoding UTF8
Write-Host "HTML file saved: $htmlFile"
}
}
catch {
Write-Host "Query error: $_"
}
finally {
# Close the connection
$conn.Close()
$conn.Dispose()
}
Export to CSV
# Convert the hashtable to an array of PSCustomObject
$dataArray = @()
foreach ($row in $table.Rows) {
$rowObject = [PSCustomObject]@{}
foreach ($col in $table.Columns.ColumnName) {
# Convert collections to a comma-separated string
$value = $row[$col]
if ($value -is [System.Collections.IEnumerable] -and $value -notmatch "String") {
$value = ($value -join ", ") # Convert collection to a readable format
}
$rowObject | Add-Member -MemberType NoteProperty -Name $col -Value $value
}
$dataArray += $rowObject
}
# Save as CSV
$dataArray | Export-Csv -Path "C:\Temp\GoogleDriveFiles.csv" -NoTypeInformation -Encoding UTF8
Conclusion
Integrating Dropbox with PowerShell using the ZappySys ODBC PowerPack allows you to automate file management, extract metadata, and manipulate data seamlessly. This method provides a flexible and efficient approach to retrieving file details, updating records, or exporting data to various formats.
For more advanced SSIS file operations, explore the ZappySys ODBC PowerPack.
References
Contact us
If you encounter any challenges or have specific use cases, please contact our support team via chat or ticket.