Powershell .CSV values piped to select-objects returns empty records?

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?

Please add a few sample rows (including the header row) from your CSV file to the question. — TheMadTechnician
– TheMadTechnician, Commented Apr 11, 2018 at 18:49

Jacob Colvin · Accepted Answer · 2018-04-11 20:57:56Z

4

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://learn.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.

edited Apr 11, 2018 at 20:57

answered Apr 11, 2018 at 20:52

Jacob Colvin

2,8651 gold badge22 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Lee Over a year ago

Worked perfectly wow, I never would have thought of something that simple. Because it didn't start giving me that problem until i used the SQL truncate statement. THANK YOU!

Collectives™ on Stack Overflow

Powershell .CSV values piped to select-objects returns empty records?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related