How to pipe Basecamp Data to CSV in PowerShell

The CData Cmdlets Module for Basecamp is a standard PowerShell module offering straightforward integration with Basecamp. Below, you will find examples of using our Basecamp Cmdlets with native PowerShell cmdlets.

Creating a Connection to Your Basecamp Data

Basecamp uses basic or OAuth 2.0 authentication. To use basic authentication you will need the user and password that you use for logging in to Basecamp. To authenticate to Basecamp via OAuth 2.0, you will need to obtain the OAuthClientId, OAuthClientSecret, and CallbackURL connection properties by registering an app with Basecamp.

See the Getting Started section in the help documentation for a connection guide.

Additionally, you will need to specify the AccountId connection property. This can be copied from the URL after you log in.

$conn = Connect-Basecamp  -User "$User" -Password "$Password"

Selecting Data

Follow the steps below to retrieve data from the Projects table and pipe the result into to a CSV file:

Select-Basecamp -Connection $conn -Table Projects | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\myProjectsData.csv -NoTypeInformation

You will notice that we piped the results from Select-Basecamp into a Select-Object cmdlet and excluded some properties before piping them into an Export-Csv cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each "row" in the result set, and we do not necessarily want that information in our CSV file.

Deleting Data

The following line deletes any records that match the criteria:

Select-Basecamp -Connection $conn -Table Projects -Where "Drafts = True" | Remove-Basecamp

Inserting and Updating Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into Basecamp, checking first whether a record already exists and needs to be updated instead of inserted.

Import-Csv -Path C:\MyProjectsUpdates.csv | %{
  $record = Select-Basecamp -Connection $Basecamp -Table Projects -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-Basecamp -Connection $basecamp -Table Projects -Columns ("Name","DocumentsCount") -Values ($_.Name, $_.DocumentsCount) -Where ("Id = `'"+$_.Id+"`'")
  }else{
    Add-Basecamp -Connection $basecamp -Table Projects -Columns ("Name","DocumentsCount") -Values ($_.Name, $_.DocumentsCount)
  }
}

As always, our goal is to simplify the way you connect to data. With cmdlets users can install a data module, set the connection properties, and start building. Download Cmdlets and start working with your data in PowerShell today!