咨询如何使用PowerShell按=分隔符横向拆分Excel指定单列
PowerShell Excel数据拆分处理问题
- 提问人当前处于PowerShell学习阶段,核心需求为处理Excel工作簿中存储内容为Hash_tag的列,按照分隔符
=横向拆分3次,生成独立的多列存储拆分结果 - 已知PowerShell存在Split字符串处理方法,但对具体实现逻辑没有清晰认知,无法独立完成功能开发
- 效果参考说明:
- 拆分前原始Hash_tag列的数据样式参考:

- 拆分后期望达成的效果样式参考:

- 拆分前原始Hash_tag列的数据样式参考:
当前已编写的脚本代码
$SRReport="C:\Users\ddpierce\Desktop\Metrics\u_service_request.csv" $OBJExcel=New-Object -ComObject Excel.Application $OBJexcel.visible=$true $WorkBook=$objExcel.Workbooks.Open($SRReport) $worksheets=$workbook.worksheets $sheet=$worksheets.item(1) $sheet.activate $range=$sheet.usedrange $a = $countrange $colA=$sheet.range("A1").EntireColumn $colrange=$sheet.range("A1") $xlDelimited = 1 $xlTextQualifier = -4142 $xlTextFormat = 1 $xlConsecutiveDelimiter = $true $xlTab = $flase $xlSemicolon = $true $xlComma = $true $xlSpace = $true $xlOther = $flase $xlOtherChar = $true,':' #Convert Text To Columns #$colA.texttocolumns($colrange,$xlDelimited,$xlTextQualifier,$true,$false,$false,$false,$false)# = nothing 1 #$colA.texttocolumns($colrange,$xlDelimited,$xlTextQualifier,$false,$true,$false,$false,$false)# = nothing 2 #$colA.texttocolumns($colrange,$xlDelimited,$xlTextQualifier,$false,$false,$true,$false,$false)# = split on simi 3 #$colA.texttocolumns($colrange,$xlDelimited,$xlTextQualifier,$false,$false,$false,$true,$false)# = split on comma 4 #$colA.texttocolumns($colrange,$xlDelimited,$xlTextQualifier,$false,$false,$false,$false,$true)# = split on space 5 #$colA.texttocolumns($colrange,$xlDelimited,$xlTextQualifier,$true,$true,$true,$true,$true,$true,$true) $colA.texttocolumns($colrange,$xlDelimited,$xlTextQualifier,$xlConsecutiveDelimiter,$xlTab,$xlSemicolon,$xlComma,$xlSpace,$xlOther,$xlOtherChar) #$sheet.columns.autofit() #Save as XLSX #$workbook.saveas("C:\Users\ddpierce\Desktop\Metrics\u_service_request2.csv") #$WorkBook.save=$true #$workbook.close($true) #$objExcel.Quit()
内容的提问来源于stack exchange,提问作者Daniel Pierce
相关产品推荐
相关产品推荐

