Get the maximum length of every field in a CSV, Excel etc

While setting up tables in our data warehouse for use with some external APIs, it was getting a little tedious to figure out the max length of some long fields to get the write size for CREATE TABLE statements, so I wrote this little script to calculate it for me. Yeah, it’s kinda messy, so sue me (or don’t). You can swap out the import-csv part to also import from Excel or whatever else

$csv = import-csv ~\Downloads\whatever.csv
$fields = $csv | select -first 1 | get-member | where MemberType -eq ‘NoteProperty'

foreach($field in $fields)
    $data = $csv | select -ExpandProperty $field.Name
    $lengths = $data | % {$_.Length}
    $max = ($lengths | measure -Maximum).Maximum
    $field | add-member -NotePropertyName MaxLength -NotePropertyValue $max

$fields | select Name, MaxLength | Format-Table -AutoSize