基于powershell列值的excel数据过滤
基于powershell列值的excel数据过滤
提问于 2022-11-10 10:08:03
我有一个excel，它有大约34k个数据，如下所示
SYMM_ID DATE INSTANCE Total Response Time
297900076 2022-10-23 11:25:00 PM GS_GTS_ORACLUL_L_PRDPRF 0.21
297900076 2022-10-24 02:15:00 AM GS_GTS_ORACLUL_L_PRDPRF 0.36
297900076 2022-10-24 04:20:00 AM GS_GTS_ORACLUL_L_PRDPRF 0.96
297900076 2022-10-24 04:25:00 AM GS_GTS_ORACLUL_L_PRDPRF 0.3
297900076 2022-10-24 04:30:00 AM GS_GTS_ORACLUL_L_PRDPRF 1.21
297900076 2022-10-24 04:35:00 AM GS_GTS_ORACLUL_L_PRDPRF 0.48
297900076 2022-10-24 04:40:00 AM GS_GTS_ORACLUL_L_PRDPRF 1.17
297900076 2022-10-24 04:45:00 AM GS_GTS_ORACLUL_L_PRDPRF 0.33
297900076 2022-10-24 04:50:00 AM GS_GTS_ORACLUL_L_PRDPRF 0.57
297900076 2022-10-24 04:55:00 AM GS_GTS_ORACLUL_L_PRDPRF 0.34
297900076 2022-10-23 05:00:00 AM GS_GTS_ORACLUL_L_PRDSTD 0.35
297900076 2022-10-23 05:05:00 AM GS_GTS_ORACLUL_L_PRDSTD 1.02
297900076 2022-10-23 05:10:00 AM GS_GTS_ORACLUL_L_PRDSTD 0.68
297900076 2022-10-23 05:15:00 AM GS_GTS_ORACLUL_L_PRDSTD 0.72
297900076 2022-10-23 05:20:00 AM GS_GTS_ORACLUL_L_PRDSTD 0.67
297900076 2022-10-23 05:25:00 AM GS_GTS_ORACLUL_L_PRDSTD 0.57
297900076 2022-10-23 05:30:00 AM GS_GTS_ORACLUL_L_PRDSTD 0.64
297900076 2022-10-23 05:35:00 AM GS_GTS_ORACLUL_L_PRDSTD 0.61
297900076 2022-10-23 05:40:00 AM GS_GTS_ORACLUL_L_PRDSTD 0.56
297900076 2022-10-23 05:45:00 AM GS_GTS_ORACLUL_L_PRDSTD 0.57
297900076 2022-10-23 05:50:00 AM GS_GTS_ORACLUL_L_PRDSTD 0.5
297900076 2022-10-23 05:55:00 AM GS_GTS_ORACLUL_L_PRDSTD 0.66
297900076 2022-10-23 06:00:00 AM GS_GTS_ORACLUL_L_PRDSTD 0.63
297900076 2022-10-23 06:05:00 AM GS_GTS_ORACLUL_L_PRDSTD 1.1
297900076 2022-10-23 11:00:00 PM GS_GTS_LOCCLUL_L_PRDPRF 0.36
297900076 2022-10-23 11:05:00 PM GS_GTS_LOCCLUL_L_PRDPRF 0.84
297900076 2022-10-23 11:10:00 PM GS_GTS_LOCCLUL_L_PRDPRF 0.93
297900076 2022-10-23 11:15:00 PM GS_GTS_LOCCLUL_L_PRDPRF 0.55
297900076 2022-10-23 11:20:00 PM GS_GTS_LOCCLUL_L_PRDPRF 0.53
297900076 2022-10-23 11:25:00 PM GS_GTS_LOCCLUL_L_PRDPRF 0.48
297900076 2022-10-23 11:30:00 PM GS_GTS_LOCCLUL_L_PRDPRF 0.58
297900076 2022-10-23 11:35:00 PM GS_GTS_LOCCLUL_L_PRDPRF 0.67
297900076 2022-10-23 11:40:00 PM GS_GTS_LOCCLUL_L_PRDPRF 0.38
297900076 2022-10-23 11:45:00 PM GS_GTS_LOCCLUL_L_PRDPRF 0.74
297900076 2022-10-23 11:50:00 PM GS_GTS_LOCCLUL_L_PRDPRF 0.51
297900076 2022-10-23 11:55:00 PM GS_GTS_LOCCLUL_L_PRDPRF 0.56
297900076 2022-10-24 12:00:00 AM GS_GTS_LOCCLUL_L_PRDPRF 0.45
297900076 2022-10-24 12:05:00 AM GS_GTS_LOCCLUL_L_PRDPRF 1.88
297900076 2022-10-24 12:10:00 AM GS_GTS_LOCCLUL_L_PRDPRF 0.75
297900076 2022-10-24 12:15:00 AM GS_GTS_LOCCLUL_L_PRDPRF 0.6
297900076 2022-10-24 12:20:00 AM GS_GTS_LOCCLUL_L_PRDPRF 0.63
297900076 2022-10-24 12:25:00 AM GS_GTS_LOCCLUL_L_PRDPRF 0.96
297900076 2022-10-24 12:30:00 AM GS_GTS_LOCCLUL_L_PRDPRF 0.58
297900076 2022-10-24 12:35:00 AM GS_GTS_LOCCLUL_L_PRDPRF 0.64
297900076 2022-10-24 12:40:00 AM GS_GTS_LOCCLUL_L_PRDPRF 0.73
297900076 2022-10-24 12:45:00 AM GS_GTS_LOCCLUL_L_PRDPRF 0.54
297900076 2022-10-24 12:50:00 AM GS_GTS_LOCCLUL_L_PRDPRF 0.57我使用下面的代码从工作表中获取行总数。
$workbook = $excel.workbooks.open('C:\SLAFile.xlsx')
$worksheet = $workbook.Worksheets.Item(1)
$rows = $worksheet.range("D2").currentregion.rows.count但是我必须过滤数据，并获得"PRF"和"STD"在INSTANCE中的行数。
我必须基于INSTANCE执行不同的公式(对于PRF不同，对于STD不同的公式)
请告诉我如何过滤列数据并获取行计数。
回答 1
Stack Overflow用户
发布于 2022-11-10 14:23:23
我认为您最好使用ImportExcel模块。这将使我们可以轻松地使用PowerShell中的对象。
考虑到行数，有几种方法可以做到这一点，但最简单的方法是使用Where-Object
import-module ImportExcel
$eFile=Import-Excel 'C:\Working\temp\test.xlsx'
$PRF=$eFile.'INSTANCE ' | Where-Object {$_ -like '*PRF'}
$STD=$eFile.'INSTANCE ' | Where-Object {$_ -like '*STD'}
Write-Host "*PRF - $($PRF.count)"
Write-Host "*STD - $($STD.count)"页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/74387254
复制相关文章
相似问题
腾讯云开发者
