Powershell

Quick and dirty command-line processing on Windows.

Setup Powershell

Any user can run Powershell interactively by executing commands in the window. However, by default, running Powershell scripts (*.ps1 files) is not permitted by Windows. Microsoft decided to apply this restrictive measure, to prevent that inexperienced users run malicious scripts.

But you can change that setting (opens new window):

Set-ExecutionPolicy -Scope CurrentUser -ExecutionPolicy RemoteSigned -Force

Optional

To change this restrictive setting, once and for all, for all users, you need Administrator permissions. Inside a second Powershell Window (started with "Run as Administator"), you must execute this command at least once:

Set-ExecutionPolicy RemoteSigned

Now open a new Powershell window, as a regular user. You should be able to run *.ps1 scripts now.

Tutorial

Download the powershell-example-mdis.ps1 file for easier usage.

Alternatively, save this code block as powershell-mdis-example.ps1 . You can also enter the 7 commands into a Powershell window, with cut and paste. of course you have to change the value of the $server variable (line 7).

#!/usr/bin/pwsh
#!/snap/bin/powershell
# Get a simple JSON dataset from the REST API of the ICDP mDIS
# The dataset is "All Expeditions" (2 rows at this time)
# tested on Powershell Core 6.2.2, 6.1.0
# knb 20190807
$server = "https://data.icdp-online.org/mdis/dseis23"

# set username and password here
$postParams = @{username = 'user1'; password = 'user1password'}

$response = Invoke-WebRequest -Uri "$server/api/v1/auth/login" -Method POST -Body $postParams
#$json = ConvertFrom-Json $response.Content -AsHashtable # $json['token']
$token = ConvertFrom-Json $response.Content  | select 'token' |  %{$_.PSObject.Properties.Value}
$headers = @{'Authorization' = "Bearer $token" }

$uri = "$server/api/v1/form?name=site"
$response2 = Invoke-RestMethod -Method Get -AllowUnencryptedAuthentication  -Headers $headers -Uri $uri
# Print it as a table. Expected output: see end of this file
$items = $response2 | select items |   %{$_.PSObject.Properties.Value}
$items | Format-Table
#$items |Out-GridView    # First: Install-Module Microsoft.PowerShell.GraphicalTools
# UNCOMMENT THIS to Write data to a CSV file
#$items  | Export-csv expeditions.csv -Append -NoTypeInformation

# OPTIONAL
#
# convert CSV File to Excel:
# download this file https://github.com/gangstanthony/PowerShell/blob/master/Save-CSVasExcel.ps1
# import it, then convert it.
# IF YOU HAVE PROBLEMS: YOU MUST DO THIS ONCE: Set-ExecutionPolicy RemoteSigned
#
#Import-Module .\Save-CsvAsExcel.ps1
# see https://stackoverflow.com/a/42597053/202553 Powershell+Export-Excel module
#$items | Save-CSVasExcel expeditions.csv

#
## expected result
#id program_id expedition acr   area  chief_scientist   comment             end_date            name                                        platform
#-- ---------- ---------- ---   ----  ---------------   -------          # 1          1       9999 Hibo  Bodensee                       F. Anselmetti, A. Schwalb Hipercorig Testsite 2019-06-14 23:00:00 Testbohrung Bodensee                        Hi…
# 2          2       5064 GRIND Southern Namibia Witputs Basin Tony Prave                in preparations                         GRIND-Ediacaran-Cambrian-Transition-Namibia dr…

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

Execute this by typing powershell -noLogo -f powershell-mdis-example.ps1 on the command line. You can also uncomment line 21 (create CSV file), or try the instructions starting in line 25.

Optional

To convert mDIS results to Excel, you must download this Powershell Helper Script to your computer: Save-CsvAsExcel.ps1.

You can also install the GraphicalTools Powershell Module with Install-Module Microsoft.PowerShell.GraphicalTools and then you can use Out-GridView instead of Format-Table.