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
Congratulations @sqlinsix! You have completed the following achievement on the Hive blockchain and have been rewarded with new badge(s) :
Your next target is to reach 300 posts.
You can view your badges on your board and compare yourself to others in the Ranking
If you no longer want to receive notifications, reply to this comment with the word
STOP
Check out the last post from @hivebuzz: