Powershell .CSV values piped to select-objects ret

Question

When i use import-csv to retrieve data from a .csv file

I'm able to retrieve the data in the file in the console pane using Import-CSV:

import-csv \\TestPath\licenses_v2.csv 

However, when i pipe that output to a select-object statement the same amount of rows are retrieved but they're now all empty.

import-csv \\TestPath\licenses_v2.csv | 
select-object FirstName,LastName,Department,Title

This occured after i began testing the following two seperate scripts, in an attempt to TRUNCATE before an INSERT statement for a SQL table.

$database = 'DATABASE'
$server = 'SERVER'
$table = 'TABLE'
Import-CSV \\TESTPATH\licenses_v2.csv | 
ForEach-Object { Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "truncate table $table; insert into $table VALUES ('$($_.FirstName)','$($_.LastName)','$($_.Department)','$($_.Title)')" 
}

$database = 'DATABASE'
$server = 'SERVER'
$table = 'TABLE'
Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "truncate table $table"
Import-CSV \\TESTPATH\licenses_v2.csv | 
ForEach-Object { Invoke-Sqlcmd -Database $database -ServerInstance $server -Query "insert into $table VALUES ('$($_.FirstName)','$($_.LastName)','$($_.Department)','$($_.Title)')" 
}

What do i have to do to reverse this?

Answer 1

You're using a pipe delimited CSV, when the default is comma. You see data in screen #1 because it creates a single object for every column. You see the headers empty, because powershell does not match any objects under those literal headers (since there's only a single header, named a combination of all.)

Note that Select can return headers when matched with literally nothing:

'' | Select Name,Date

Name Date
---- ----

---

To fix, just use Import-Csv -Delimiter '|'

See https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/import-csv?view=powershell-6#optional-parameters

If you specify a character other than the actual string delimiter in the file, Import-Csv cannot create objects from the CSV strings. Instead, it returns the strings.