Powershell file manipulation and basics

Had to write a script to download a few .csv files, cut the first lines (the header) and combine all of them into one big .csv for import into Excel. For various reasons, wrote it in Powershell. First one, so I’m taking note of some basics.

First things first, PowerShell doesn’t execute scripts by default because *security*. Execution-policy has to be set. To see the current value, run:

Get-ExecutionPolicy

To set the policy, run

Set-ExecutionPolicy <policy>

where ‘policy’ is one of:

  • Restricted – Scripts won’t run.

  • RemoteSigned – Scripts created locally will run, but those downloaded from the Internet will not (unless they are digitally signed by a trusted publisher).

  • AllSigned – Scripts will run only if they have been signed by a trusted publisher.

  • Unrestricted – Scripts will run regardless of where they have come from and whether they are signed.

In order to read the powershell help, use get-help

Things that make an appearance in this script, in order:

  • ‘do until’ loop. ‘do while’ looks the same, except with ‘while’ instead of ‘until’
  • regex comparison on a string via -match
  • string length, everything is an object in powershell, length is a property
  • asking for user input and saving it into a variable The files have names like “DDL-20131128.01.001.CSV” (PayPal reports).
Do {
    $pp_month = Read-Host 'Enter month (two digits, 01-12):'
} Until ($pp_month.length -eq 2 -and $pp_month -match "\d{2}")
Do {
    $pp_year = Read-Host 'Enter year (four digits):'
} Until ($pp_year -match "^\d{4}$")


## variable declaration
# get CSV files from example.com
$pp_host = "example.com"
$pp_user = "username"
$pp_pass = "password"
$pp_remotedir = "/path/to/files"
$pp_localdir = "c:\csv\folder"

## variable concatenation, several ways, this is 'the powershell way', aka 'inline'
## incidently the line was copy-pasted from an sh script
## the escape character in powershell is `
echo "Connecting to $pp_host..."
.\pscp.exe -pw $pp_pass $pp_user@$pp_host`:$pp_remotedir/DDL-$pp_year$pp_month* $pp_localdir

## exit code of last executable
## depends on executable, either $? or $LastExitCode
if ($? -eq 0) {
    echo "Could not connect to $pp_host, but I can still combine existing files, if any. Press Enter to continue or CTRL+C to stop"
    $x = $host.UI.RawUI.ReadKey("NoEcho,IncludeKeyUp")
} else {
    echo "Connection successful, processing files..."
}

# $skipln will be set to 11 after first use
# meaning that it will get the header from the first file and only the contents after that
$skipln = 0
ls DDL-$pp_year$pp_month*.csv | Foreach {
    echo "Processing $_"

    if ($skipln -eq 0) {
        # this is the first file
        #copy -force $_ "DDL-combined-$pp_year$pp_month.csv"
        set-content -value (get-content $_ | select -Skip $skipln) -path ".\DDL-combined-$pp_year$pp_month.csv" -encoding "utf8"
        $skipln = 11
    } else {
        # without -encoding it will use unicode, which will result in first part of the file
        # being ascii because of the copy above and the rest Unicode. this will confuse Excel
        add-content -value (get-content $_ | select -Skip $skipln) -path ".\DDL-combined-$pp_year$pp_month.csv" -encoding "utf8"
    }
}

echo ""
echo "All done, result in the following file:"
ls ".\DDL-combined-$pp_year$pp_month.csv"