Managing data with PowerShell

At Bitrise we love automation: it can save you time for creative work. Originally, PowerShell was developed to support system administrators automating and managing tasks and processes of the Windows operating system. Why should we consider and how can we use it in data business?

At Bitrise we love automation: it can save you time for creative work. Originally, PowerShell was developed to support system administrators automating and managing tasks and processes of the Windows operating system. Why should we consider and how can we use it in data business?

Why PowerShell?

PowerShell commands can be run in the terminal while supporting an object-oriented concept, we get the best of both worlds:

  • PowerShell works with objects,
  • but without labyrinth of versions and environments.
  • No need to compile.
  • It is relatively easy to find the needed cmdlets by their Verb-Noun format.
  • Built-in commands for HTTP requests.
  • Packages to manage cloud services like the ones of Google Cloud or Microsoft Azure.
  • Several options for specific tasks due to .NET.
  • It is cross-platform.

Installing on Linux

Unfortunately, as of today, installation on Ubuntu is not that straightforward. PowerShell Core depends on libicu60 which cannot be downloaded from the regular repositories.

Installing packages and modules

In PowerShell Install-Package and Install-Module cmdlets adds new capabilities to your setup or session.
In case you need it, for example in a Dockerfile, you can use pwsh with -Command parameter in the following format:

Mind the '&' before your commands: it is needed when running pwsh from another shell.

Once we have PowerShell installed we can start using it for, besides several others, the following use cases.

Sample applications

CSV to JSON

PowerShell can work with CSV and JSON out of the box. The next snippet

  1. imports a CSV,
  2. adds current datetime property as updated_at and
  3. exports data to JSON.

CSV to Document Database

It is a common task for data experts to collect information from flat files into a database. Here CSV files are our source and CosmosDB is our Destination.

Query Cosmos DB

You can use SQL here.

Excel files

For several organizations data means Excel. Fortunately, there is a really feature-rich package from Doug Finke, to cope with XLSX files and automate tasks. It is available on both GitHub and PowerShell Gallery.

Install ImportExcel module for the current user:

Install-Module ImportExcel -scope CurrentUser

Then you can import your XLSX, CSV or JSON and define Excel tasks in Export-Excel command. E.g. importing a sheet from an XLSX file and exporting pivot table into another can be achieved with this few lines:

Download Recurly export files

At Bitrise, we use Recurly to manage our subscriptions. Unfortunately, relatively strict limits on number of API calls in a specific time frame are set by the provider. The suggested way of retrieving data is setting up exports on Recurly's site and collecting daily batches:

  1. We need to GET a temporary download url to each daily export of each endpoint.
  2. The export files are available for an hour. We can download them by another call.
No items found.
The Mobile DevOps Newsletter

Explore more topics

App Development

Learn how to optimize your mobile app deployment processes for iOS, Android, Flutter, ReactNative, and more

Bitrise & Community

Check out the latest from Bitrise and the community. Learn about the upcoming mobile events, employee spotlights, women in tech, and more

Mobile App Releases

Learn how to release faster, better apps on the App Store, Google Play Store, Huawei AppGallery, and other app stores

Mobile DevOps

Learn Mobile DevOps best practices such as DevOps for iOS, Android, and industry-specific DevOps tips for mobile engineers

Mobile Testing & Security

Learn how to optimize mobile testing and security — from automated security checks to robust mobile testing and more.

Product Updates

Check out the latest product updates from Bitrise — Build Insights updates, product news, and more.

The Mobile DevOps Newsletter

Join 1000s of your peers. Sign up to receive Mobile DevOps tips, news, and best practice guides once every two weeks.