PowerShell: Check Column length in CSV file

reynowa · 2021-02-23T18:29:46.000Z

I need to import a csv file and check to see if a column contains 9 characters exactly. If column 1 does not have 9 characters, I want to send a message that my job failed. Anyone have any ideas on the best way to write the if statement regarding the column length?

tulioarends · 2021-02-23T18:42:57.000Z

You cannot check the length of the “column”, you have to iterate over all the items and check the length of that particular property.

Use a for, a while or a do-while loop.

reynowa · 2021-02-23T18:57:25.000Z

After thinking about this, I’m wondering if it might be better to code this project with logic that pads left zeros if the number of characters is less than 9. I need column 1 to have 9 characters and if the file comes in with only 5 characters, I would need to pad left with zeros. I know I had code to do this before, but I can’t seem to find it. Anyone written something like this?

jitensh · 2021-02-23T19:01:17.000Z

en example

(Import-Csv C:\scripts\data.csv|select -exp employeeid | measure -Line -Character).Characters

CSV

employeeid,name
123,Peter Pan

tulioarends · 2021-02-23T19:20:49.000Z

Use the padLeft() method of the String class:

"ABCDF".PadLeft(9,'0')

I normally would use the -f (format) operator but the syntax is geared to pad strings with spaces instead of zeroes.

Neally · 2021-02-23T19:21:56.000Z

fillawea:

After thinking about this, I’m wondering if it might be better

You are asking 2 different things

OP → find rows in column who’s character count is less than 9
NOW → now the same as OP but add left padding to make it 9

Maybe, elaborate, what s the bigger picture? What is the issue or what are you trying to achieve?

Both is possible, but before we just throw code at you, maybe we should talk about the actual use case?

reynowa · 2021-02-23T19:36:45.000Z

Sorry for the confusion. I am combining files for an import process that runs nightly from different sources. When column 1 does not equal 9 characters, the file import will fail. I have one person who doesn’t consistently send me data with column 1 formatted to 9 characters. So…I was at first thinking of kicking the file and sending a message for them to fix it. Then I thought I ought to just pad the zeros myself so I don’t have to deal with the extra work of kicking the file back. Sometimes column 1 is delivered to my SFTP correctly, and sometimes column 1 is missing the extra characters to make it 9 characters long. I’m trying to find the easiest way to do this in POSH. Here’s what I just came up with:

$AGFILE = Import-Csv “C:\AGSD\Data\TransFinder\AGCharter\AGCharterStudents.csv”
Foreach ($Item in $AGFILE)
{
$Item.Student_Number = string.PadLeft(9,‘0’)
}
$AGFILE
$AGFILE| Export-CSV Import-Csv “C:\AGSD\Data\TransFinder\AGCharter\AGCharter_LeftPAdding.csv” -NoTypeInformation

I am getting this error: Error: “String must be exactly one character long.”

So, then I scratched that and worked it into this:

$ AGFILE = Import-Csv “C:\AGSD\Data\TransFinder\AGCharter\AGCharterStudents.csv”

Foreach ($Item in $ AGFILE )
{
if ($Item.Student_Number.Length -lt 9)
{
$Item.Student_Number = $Item.Student_Number.Insert(0,‘0’)
}
}

$ AGFILE
$ AGFILE | Export-CSV “C:\AGSD\Data\TransFinder\AGCharter\AGCharter_LeftPAdding.csv” -NoTypeInformation

This one gives me 1 zero padded in front in my column. I’m still not able to get my column to be 9 characters.

Neally · 2021-02-23T19:38:40.000Z

If you post code, please use the ‘Insert Code’ button. Please and thank you!

PLEASE READ BEFORE POSTING! Read if you're new to the PowerShell forum! Programming & Development

Hi, and welcome to the PowerShell forum! Don’t apologize for being a “noob” or “newbie” or “n00b.” There’s just no need – nobody will think you’re stupid, and the forums are all about asking questions. Just ask! Use a descriptive subject. Don’t say “Need help” or “PowerShell Help”, actually summarize what the problem is. It helps the rest of us keep track of which problem is which. Don’t post massive scripts. We’re all volunteers and we don’t have time to read all that, nor will we copy…

davidkenney · 2021-02-23T19:39:37.000Z

From what I have experienced so far, @tulioarends is correct. Iterating through them isn’t the end of the world though and allows you to do the padding you need.

Using @jitensh ’s CSV columns example:

$import = import-csv c:\import.csv
foreach ($i in $import) {
   if (($i.employeeid).Length -eq $null) { #This is just incase you have a bad record in the CSV.  It might need to be -eq '0' instead.
      #Do something for records that don't have anything in this column
      Clear-Variable i  #I like to clear the variable after processing.  I have been bit a couple of times with bad data without it.
      Continue #After we do something with it, we don't need further processing
      }
   if (($i.employeeid).Length -gt 9) {
      #Do something to deal with that column having greater than 9 characters
      Clear-Variable i  #Remove this if you remove the Continue below this line
      Continue #After you do something with it, you may or may not need further processing.  I would put it in a different file for further review of the record.  Remove this if you want the record anyway.
      }
   if (($i.employeeid).Length -lt 9) {
      $i.employeeid = $i.employeeid.PadLeft(9,'0')
      }
   #Doing whatever you need it to do with the data now.  We are assuming that if it doesn't hit on the If statements, it is 9 characters long already.
   Clear-Variable i
   }

It looks long because I have some programing notes/insights in there that won’t be needed in your final script.

Depending on how many records you anticipate to have the required length already, you could put a “-eq 9” statement in at the top to do your next action and then Continue in order to save on some processing time/power on the records that don’t need to be adjusted.

David

Edit: As a follow up to your second post, I would definitely export any records with null/0 or ‘-gt 9’ length to a “review” file to send back to the originator so they can clean up the data.

Edit2: The first two If statements are only there for catching a bad record. If you NEVER have that column empty or greater than 9, you can remove them.

Neally · 2021-02-23T19:43:09.000Z

besides whitepaces… if you look at the fantastic info Tulio gave you…

it should be more like so

$AGFILE = Import-Csv "C:\AGSD\Data\TransFinder\AGCharter\AGCharterStudents.csv"

Foreach ($Item in $AGFILE ) {
    if ($Item.Student_Number.Length -lt 9) {
        $Item.Student_Number = $Item.Student_Number.padleft(9,'0')
    }
}

$AGFILE
$AGFILE | Export-CSV "C:\AGSD\Data\TransFinder\AGCharter\AGCharter_LeftPAdding.csv" -NoTypeInformation

reynowa · 2021-02-23T19:50:15.000Z

Thank you all so much! I’ve got it working now. And sorry for my ugly code posting. I forgot that was there. It’s been a long, long day.

spiceuser-8ua35 · 2021-08-21T01:13:17.000Z

So - the selected answer isn’t how I would approach it, but depending on your size of data and how much manipulation you want to do - using ForEach and If statements is logical and will work.

An alternate approach for those that stumble here trying to Check the Column length in a CSV file - I wouldn’t say definitively that you cannot check the length per se and you don’t need to iterate over every row unless you want to and plan to take action on the rows. In this case an early filter with Where-Object is going to be better than iterating every row; but more on that later, let’s tackle the column length piece first.

$colLengths = Import-CSV .\file.csv | ForEach-Object{$_."employeeid".Length} | Select-Object -Unique
Write-Output $colLengths

If all you need is to consume your CSV and see the various lengths for a particular column, that will get it done for you. *Swap out Select-Object for Sort-Object -Unique and you’ll have your lengths sorted for easier viewing. I would probably stick with something similar to @davidkenney ’s answer though if you need to take action while iterating through them.

If you were using Get-Content instead of Import-CSV then I would say a Where-Object filter is necessary, there’s no reason to get all of the content if you’re not going to take action on it all. Import-CSV is a little different IMO since we’re loading it to memory, but that’s not to say that something like the .Where() method wouldn’t help out in terms of speed and using ‘Split’ mode to create send a faulty file to the creator. Especially helpful if you’re processing multiple CSVs from the same location as written below for example:

$files = (Get-ChildItem -LiteralPath "C:\LoadDirectory" -Filter *.csv)
$updatedFile = New-Object System.Collections.ArrayList
Foreach($CSV in $files){
	$goodData, $badData = (Import-CSV $CSV).Where({$_."employeeid".Length -eq 9}, 'Split')
	If($badData){
		$badData | Export-CSV -LiteralPath "$($CSV.DirectoryName)\$($CSV.BaseName)_JunkRows.csv"
		Foreach($row in $badData){
			<#Fix it here
			Switch( $row.employeeid.Length ){
				0 {"Length is zero"}
				{$_ -gt 9} {"Length is more than 9"}
				{$_ -lt 9} {$row."employeeid" = $row."employeeid".PadLeft(9,'0')}
			#>
		[void]$updatedFile.Add($row)
		}
		$updatedFile | Export-CSV -LiteralPath "$($CSV.DirectoryName)\$($CSV.BaseName)Fixed.csv"
		}
	}

Personally - I would opt for the Switch statements because I like the syntax, but if you do If then I’d probably lean toward If/ElseIf so that you’re not evaluating every If. I think the idea of the Continues was to get at that, but ElseIf would be cleaner and if you want to use Switch with a ;break then you probably can as well.

The advantage in this case of using the .Where() method with ‘Split’ mode is that you’re no longer iterating over all rows, only the rows that don’t have the correct length “badData”. So not only would it be faster and easier to split out your good data from bad data, but it lets you update the bad data without iterating over the rows that are fine.