时间戳

首页 > 解决方案 > 将 .CSV 列数据作为唯一行导出到单独的文件中，然后排序

powershell - 将 .CSV 列数据作为唯一行导出到单独的文件中，然后排序

问题描述

使用以下脚本，如何提取 IPAddress 格式的唯一列数据，然后将其保存为 ColumnName.TXT？

$ColumnNames = @('client_ip', 'server_ip', 'original_client_ip', 'original_server_ip')
Get-ChildItem -Path C:\Logs\Input\ -Filter *.csv |
    Get-Content |
        ForEach-Object { $_.Split(',')[$ColumnNames] } |
            Sort-Object -Unique | Sort -asc |
                Set-Content -Path C:\LOGS\Unique-$($ColumnNames).txt

它不适用于带有双引号的 .CSV 文件。

不知何故，我从 Microsoft 365 门户下载的 MessageTraceDetails.CSV 文件将始终具有如下标题：

"date_time_utc","client_ip","client_hostname","server_ip","server_hostname","source_context","connector_id","source","event_id","internal_message_id","message_id","network_message_id","recipient_address","recipient_status","total_bytes","recipient_count","related_recipient_address","reference","message_subject","sender_address","return_path","message_info","directionality","tenant_id","original_client_ip","original_server_ip","custom_data"

是否可以从 .CSV 文件中删除所有双引号，然后执行搜索？

标签： powershell

解决方案

如果您只处理一个输入 CSV 文件，可以这样做：

$ColumnNames = 'client_ip', 'server_ip', 'original_client_ip', 'original_server_ip'
$data = Import-Csv -Path 'C:\Logs\Input\TheInputFile.csv'

# get the headers as they are found in this CSV file
$headers = $data[0].PSObject.Properties.Name

foreach ($column in $ColumnNames) {
    # test if a column name from the array can be found in the CSV
    # and if so, output a new file with the values of that column
    if ($headers -contains $column) {
        $file = Join-Path -Path 'D:\Test' -ChildPath ('Unique-{0}.txt' -f $column)
        Set-Content -Path $file -Value ($data.$column | Sort-Object -Unique)
    }
}

但是，如果文件夹中有更多 csv 文件要处理，我认为您还需要在输出文件前面加上原始 csv 文件的基本名称，以免一次又一次地覆盖输出。

$ColumnNames = 'client_ip', 'server_ip', 'original_client_ip', 'original_server_ip'
Get-ChildItem -Path 'C:\Logs\Input' -Filter '*.csv' -File | ForEach-Object { 
    $data = Import-Csv -Path $_.FullName
    # get the headers as they are found in this CSV file
    $headers = $data[0].PSObject.Properties.Name

    foreach ($column in $ColumnNames) {
        # test if a column name from the array can be found in the CSV
        # and if so, output a new file with the values of that column
        if ($headers -contains $column) {
            $file = Join-Path -Path 'C:\LOGS' -ChildPath ('{0}_Unique-{1}.txt' -f $_.BaseName, $column)
            Set-Content -Path $file -Value ($data.$column | Sort-Object -Unique )
        }
    }
}

推荐阅读