How to increase the performance of StreamReader

Question

I have to encrypt a specific column in a large csv, and output it to a new csv, however the file size is about 4 Million records

I have tried to use the streamReader instead of import-csv cmdlet.

The file structure looks like this:

date_time;msisdn;campaign_name;campaign_type;subs_status;substate_name;start_date;end_date;message_state;channel_state;amount_usage;amount_recharge;amount_call_bonus;amount_call;amount_message;amount_message_bonus;amount_data;amount_data_bonus;message_date_time
2019-06-11 12:44:15;99999999;C500_VL_090619;Jun_19;PROVISIONED;null;2019-06-10;2019-06-30;NOT_SENT;NOT_SENT;1200;null;null;1200;null;null;null;null;2019-06-10 16:20:55.0
2019-06-11 12:44:15;88888888;C500_VL_090619;Jun_19;PROVISIONED;null;2019-06-10;2019-06-30;NOT_SENT;NOT_SENT;null;null;null;null;null;null;null;null;2019-06-10 16:20:55.0
2019-06-11 12:44:15;95555555;C500_VL_090619;Jun_19;PROVISIONED;null;2019-06-10;2019-06-30;NOT_SENT;NOT_SENT;null;null;null;null;null;null;null;null;2019-06-10 16:20:55.0
2019-06-11 12:44:15;35555555;C500_VL_090619;Jun_19;PROVISIONED;null;2019-06-10;2019-06-30;NOT_SENT;NOT_SENT;null;null;null;null;null;null;null;null;2019-06-10 16:20:55.0

Here's the code I tried

################# 
# Powershell Allows The Loading of .NET Assemblies 
# Load the Security assembly to use with this script  
################# 
[Reflection.Assembly]::LoadWithPartialName("System.Security") 

################# 
# This function is to Encrypt A String. 
# $string is the string to encrypt, $passphrase is a second security "password" that has to be passed to decrypt. 
# $salt is used during the generation of the crypto password to prevent password guessing. 
# $init is used to compute the crypto hash -- a checksum of the encryption 
################# 
function Encrypt-String($String, $Passphrase, $salt="SaltCrypto", $init="IV_Password", [switch]$arrayOutput) 
{ 
    # Create a COM Object for RijndaelManaged Cryptography 
    $r = new-Object System.Security.Cryptography.RijndaelManaged 
    # Convert the Passphrase to UTF8 Bytes 
    $pass = [Text.Encoding]::UTF8.GetBytes($Passphrase) 
    # Convert the Salt to UTF Bytes 
    $salt = [Text.Encoding]::UTF8.GetBytes($salt) 

    # Create the Encryption Key using the passphrase, salt and SHA1 algorithm at 256 bits 
    $r.Key = (new-Object Security.Cryptography.PasswordDeriveBytes $pass, $salt, "SHA1", 5).GetBytes(32) #256/8 
    # Create the Intersecting Vector Cryptology Hash with the init 
    $r.IV = (new-Object Security.Cryptography.SHA1Managed).ComputeHash( [Text.Encoding]::UTF8.GetBytes($init) )[0..15] 

    # Starts the New Encryption using the Key and IV    
    $c = $r.CreateEncryptor() 
    # Creates a MemoryStream to do the encryption in 
    $ms = new-Object IO.MemoryStream 
    # Creates the new Cryptology Stream --> Outputs to $MS or Memory Stream 
    $cs = new-Object Security.Cryptography.CryptoStream $ms,$c,"Write" 
    # Starts the new Cryptology Stream 
    $sw = new-Object IO.StreamWriter $cs 
    # Writes the string in the Cryptology Stream 
    $sw.Write($String) 
    # Stops the stream writer 
    $sw.Close() 
    # Stops the Cryptology Stream 
    $cs.Close() 
    # Stops writing to Memory 
    $ms.Close() 
    # Clears the IV and HASH from memory to prevent memory read attacks 
    $r.Clear() 
    # Takes the MemoryStream and puts it to an array 
    [byte[]]$result = $ms.ToArray() 
    # Converts the array from Base 64 to a string and returns 
    return [Convert]::ToBase64String($result) 
}





$files = Get-ChildItem ".\input\"



$infile = Get-Item .\input\test.csv
$reader = New-Object -TypeName System.IO.StreamReader -ArgumentList $infile


$allLines = @()

while ($line = $reader.ReadLine())
{
      "===================Start===================="
     $array = $line.split(";")

      $array[1]
     $array[1] =  Encrypt-String $array[1].msisdn "MyStrongPassword"
     $array
   "====================END==================="

   $b = $($array -join '";"')


  $allLines += $b 

   }



   $allLines | Out-File -FilePath .\output\outputFile.csv


$reader.close()

The encrypt function credit goes to this dev: https://github.com/buuren/powershell/blob/master/misc/encryptPassword.ps1

However, the file is taking 1 hour

any way to make it faster?

Answer 1

By not using the slow array concatenation $allLines += $b and using a System.IO.StreamWriter together with the System.IO.StreamReader, this should be faster:

$reader = New-Object System.IO.StreamReader("D:\test.csv")
$writer = New-Object System.IO.StreamWriter("D:\outputFile.csv")

# special care for the first headers line; you don't want to encrypt that
$writer.WriteLine(($reader.ReadLine()))

while (($line = $reader.ReadLine()) -ne $null) {
    $fields = $line -split ';'
    $fields[1] = Encrypt-String $fields[1] "MyStrongPassword"
    $writer.WriteLine(($fields -join ';'))
}
$reader.Dispose()
$writer.Dispose()

From your code, I gather you want to add quotes around each field. In that case use:

$reader = New-Object System.IO.StreamReader("D:\test.csv")
$writer = New-Object System.IO.StreamWriter("D:\outputFile.csv")

# special care for the first headers line; you don't want to encrypt that
$writer.WriteLine(('"{0}"' -f (($reader.ReadLine()) -replace ';', '";"')))

while (($line = $reader.ReadLine()) -ne $null) {
    $fields = $line -split ';'
    $fields[1] = Encrypt-String $fields[1] "MyStrongPassword"
    $writer.WriteLine(('"{0}"' -f ($fields -join '";"')))
}
$reader.Dispose()
$writer.Dispose()

^{Note: This of course does nothing to speed up the processing time of the downloaded Encrypt-String function..}