Quick ETL Hacks With PowerShell

sqlinsix(58)

Published in

#etl

Words

230

Reading

2 min

Listen

Play

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.