Quick ETL Hacks With PowerShell

Get Number of Fields And Values In A Delimited File

This is extremely useful if we need to get every 124th value (or another Nth value) from every line in a delimited file. Often, this is the case for a quick comparison or a quick grab of the data to see if a value may be what we're looking for.

Function Return-Fields {
    Param(
        $file
    )
    Process
    {
        $data = Get-Content $file
        
        $lineNo = 0
        $valueNo = 0

        foreach ($line in $data)
        {
            $lineNo++
            Write-Output ("=====> Line $lineNo" + [Environment]::NewLine)
    
            $valueNo = 0
            foreach ($value in $line.Split("|"))
            {
                $valueNo++
                Write-Output ("Value $valueNo : " + $value)
            }
        }
    }
}

Return-Fields -file "myfile.txt"

We could further use logic such as $line.Split("|")[3] if we wanted the 4th element or something similar. This allows us to drill further into the data.

Get Breakdown of Delimited File By Lines and Values

This is a very helpful script if we're quickly trying to get a specific value from a delimited file without importing the file. It gives us the line number and the value number. So if we need to find the 4th's lines 22nd value, it becomes easy. This also optionally outputs the entire headers in a line, so if we need these stacked, we can get them quickly. If we want to add a further delimiter (like html), then we can insert that as well.

$string = "ID#,IDidItRight,MoMoney$,I Need Some Space"
$iterate = $string.Split(",")
$finalstring = ""


foreach ($i in $iterate)
{
    Write-Output ('"' + $i.ToLower().Replace("#","").Replace("$","").Replace(" ","") + '"')
    $finalstring += $i.ToLower().Replace("#","").Replace("$","").Replace(" ","")
}

Write-Output ([Environment]::NewLine)
Write-Output ($finalstring)

Keep in mind that with PowerShell when using Get-Content, we can select the first number of rows.

More ETLHelp posts:

• Maven Build Project Without Unit Tests and Reporting Errors
• Spark Join Hacks With Scala
• Quick ETL Hacks With PowerShell