传递给选择对象的Powershell .CSV值返回空记录吗? [英] Powershell .CSV values piped to select-objects returns empty records?

问题描述

当我使用import-csv从.csv文件中检索数据

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

我可以使用Import-CSV检索控制台窗格中文件中的数据:

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

发生这种情况是在我开始测试以下两个单独的脚本之后，试图在对SQL表的INSERT语句之前执行TRUNCATE.

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?

推荐答案

您使用的是管道分隔的CSV，默认值为逗号.您会在屏幕#1中看到数据，因为它为每一列创建一个对象.您会看到标题为空，因为powershell不匹配这些文字标题下的任何对象(因为只有一个标题，命名为所有标题的组合).

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.)

请注意，当Select与任何字符都不匹配时，可以返回标头:

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

'' | Select Name,Date

Name Date
---- ----

---

要修复，只需使用Import-Csv -Delimiter '|'

请参见

如果在文件中指定的字符不是实际的字符串定界符，则Import-Csv无法从CSV字符串创建对象.而是返回字符串.

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.

这篇关于传递给选择对象的Powershell .CSV值返回空记录吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！