Powershell is great for admin tasks. Stuff like iterating through files and folders, copying and transforming files are very, very easily done. But inevitably there will always be stuff that are easier to do via a “normal” language such as C#.
Trying to solve a problem I had at work, I needed to transform a CSV file by changing the fields -which is easily done via powershell- and, at the same time, do a “get only the highest record of every group”. This is done with LINQ, which you can use in powershell but it’s cumbersome and will result in many, many lines of code.
So I wanted to do this in a more clean way, in C#. The general template to include C# inside a powershell script is the following:
#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, November 2018
#
# Here goes the C# code:
Add-Type -Language CSharp @"
using System;
namespace DotJim.Powershell
{
public static class Magician
{
private static string spell = "";
public static void DoMagic(string magicSpell)
{
spell = magicSpell;
}
public static string GetMagicSpells()
{
return "Wingardium Leviosa\r\n" + spell;
}
}
}
"@;
# And here's how to call it:
[DotJim.Powershell.Magician]::DoMagic("Expelliarmus")
$spell = [DotJim.Powershell.Magician]::GetMagicSpells()
Write-Host $spell
Note here that the C# classes don’t have to be static; but if they are, they’re easier to call (no instantiation needed). Of course this only works if all you need to do is provide an input and get a manipulated output. If you need more complex stuff then yes, you can use non-static classes or whatever C# functionality solves your problems. Here’s the previous example, but with a non-static class:
#
# Source: DotJim blog (https://dandraka.com)
# Jim Andrakakis, November 2018
#
# Here goes the C# code:
Add-Type -Language CSharp @"
using System;
namespace DotJim.Powershell
{
public class Magician
{
private string spell = "";
public void DoMagic(string magicSpell)
{
spell = magicSpell;
}
public string GetMagicSpells()
{
return "Wingardium Leviosa\r\n" + spell;
}
}
}
"@;
# Here's how to create an instance:
$houdini = New-Object -TypeName DotJim.Powershell.Magician
# And here's how to call it:
$houdini.DoMagic("Expelliarmus")
$spell = $houdini.GetMagicSpells()
Write-Host $spell
UPDATE: What if you need to use some assembly in the C# code, either a system one or a dll that your have anywhere on disk?
In this case, you add -ReferencedAssemblies to the Add-Type line, like this:Add-Type -Language CSharp -ReferencedAssemblies @('System.Xml', 'System.Runtime.Serialization', "C:\app\ServiceBusExplorer\Microsoft.ServiceBus.dll") @"
using System;
using System.Runtime.Serialization;
using System.Xml;
using Microsoft.ServiceBus;
using Microsoft.ServiceBus.Messaging;
namespace DotJim.Powershell
{
// etc etc
}
"@;
The main advantage of having C# inside the powershell script (and not in a separate dll file) is that it can be deployed very easily with various Devops tools. Otherwise you need to deploy the dll alongside which can, sometimes, be the source of trouble.
So here’s my complete working code, which worked quite nicely:
#
# Source: DotJim blog (http://dandraka.com)
# Jim Andrakakis, November 2018
#
# The purpose of this script is to read a CSV file with bank data
# and transform it into a different CSV.
#
# 1. The Bank class is a POCO to hold the data which I need
# from every line of the CSV file.
# 2. The Add() method of the BankAggregator class adds the
# record to the list after checking the data for correctness.
# 3. The Get() methof of the BankAggregator class does a
# LINQ query to get the 1st (max BankNr) bank record
# from every record with the same Country/BIC.
# It then returns a list of strings, formatted the way
# I want for the new (transformed) CSV file.
#
# Here is where I inline the C# code:
Add-Type -Language CSharp @"
using System;
using System.Collections.Generic;
using System.Linq;
namespace DotJim.Powershell {
public class Bank {
public int BankNr;
public string Country;
public string BIC;
}
public static class BankAggregator {
private static List list = new List();
public static void Add(string country, string bic, string bankNr) {
//For debugging
//Console.WriteLine(string.Format("{0}{3}{1}{3}{3}{2}", country, bic, bankNr, ";"));
int mBankNr;
// Check data for correctness, discard if not ok
if (string.IsNullOrWhiteSpace(country) ||
country.Length != 2 ||
string.IsNullOrWhiteSpace(bic) ||
string.IsNullOrWhiteSpace(bankNr) ||
!int.TryParse(bankNr, out mBankNr) ||
mBankNr & gt; = 0) {
return;
}
list.Add(new Bank() {
BankNr = mBankNr, Country = country, BIC = bic
});
}
public static List Get(string delimiter) {
// For every record with the same Country & BIC, keep only
// the record with the highest BankNr
var bankList = from b in list
group b by new {
b.Country, b.BIC
}
into bankGrp
let maxBankNr = bankGrp.Max(x = & gt; x.BankNr)
select new Bank {
Country = bankGrp.Key.Country,
BIC = bankGrp.Key.BIC,
BankNr = maxBankNr
};
// Format the list the way I want the new CSV file to look
return bankList.Select(x = & amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; amp; gt; string.Format("{0}{3}{1}{3}{3}{2}",
x.Country, x.BIC, x.BankNr, delimiter)).ToList();
}
}
}
"@;
# Read one or more files with bank data from the same dir
# where the script is located ($PSScriptRoot)
$srcSearchStr = "source_bankdata*.csv"
$SourcePath = $PSScriptRoot
$destPath = $SourcePath
$fields = @("Country","BIC","EmptyField","BankId")
$filesList = Get-ChildItem -Path $SourcePath -Filter $srcSearchStr
foreach ($file in $filesList)
{
Write-Host "Processing" $file.FullName
# Fields in the source CSV:
# BANKNUMMER = BankNr
# BANKLAND = Country
# BANKSWIFT = BIC
$data = Import-Csv -Path $file.FullName -Delimiter ";"
foreach ($item in $data)
{
# Call the C# code to add the CSV lines to the list
[DotJim.Powershell.BankAggregator]::Add($item.BANKLAND,$item.BANKSWIFT,$item.BANKNUMMER)
}
# Call the C# code to get the transformed data
$list = [DotJim.Powershell.BankAggregator]::Get(";")
Write-Host "Found" $list.Count "valid rows"
# Now that we have the list, write it in the new CSV
Out-File -FilePath "$destPath\transformed_bankdata_$(New-Guid).csv" -Encoding UTF8 -InputObject $list
}
Have fun coding!
Dot Jim 