代码之家 › 专栏 › 技术社区 › Dhanesh Agrawal

powershell脚本,用于处理excel单元格中的多行数据并使用import excel或import csv转换为csv

export-csv import-csv import-from-excel powershell excel

0

Dhanesh Agrawal · 技术社区 · 6 年前

问:我想把excel转换成csv格式,但是excel在单元格中包含多行数据,还有“;”作为分隔符。所需的输出是一个CSV文件,它的“分隔”是“列”分隔符,而“是”,作为同一单元内数据的分隔符。我不能使用excel,因此我使用模块“importexel”。

输入excel格式(问题末尾的附件):-

Row1# c1# c2#                    c3#         c4#
Row2#
Row3#
Row4#     HEADER1                HEADER2     HEADER3
Row5#     04.05 test &amp; t     test        TEST
Row5#     04.06.01 test test                 TEST
Row5#     04.07.01 test test 
Row5#     04.10.02 test &amp; t

row表示excel的行,c表示列,header表示列的标题。可以看到,excel的第1-3行和第一列是空的。第二列和第四列的文本是多行的,但在同一个单元格(同行ROW5)中。

然后我尝试为每个对象导入excel,并将新行字符替换为“,”作为分隔符,使用(export csv)将其导出为csv。

我写的剧本如下:

$test = Import-excel -NoHeader -StartRow(4) test.xlsx

$test | foreach-object {

if($_.HEADER1) {
$_.HEADER1 = $_.HEADER1.replace("\n",", ")
$_.HEADER1 = $_.HEADER1.replace("&amp;","& ")
   }
}

$test | Export-csv new-test.csv -Delimiter '|' -NoTypeInformation

输出我得到的csv文件:

Row1# c1# c2#                       c3#
Row2#     HEADER1|HEADER2|HEADER3
Row3#     04.05 test &amp            t
Row4#     04.06.01 test test          
Row5#     04.07.01 test test 
Row6#     04.10.02 test &amp         t
Row7#     test|TEST
Row8#     TEST

可以看到,输入中delimeter“;”后面的文本转到另一列,多行仍被解析为单独的行。我希望所有这些多行合并成一个带“,”作为分隔符的行,以及“;”分隔符后的文本。

所需的CSV文件:

Row1# c1# c2#                
Row2#     HEADER1|HEADER2|HEADER3
Row3#     04.05 test &  t,04.06.01 test test,04.07.01 test test,04.10.02 test & t|test|TEST,TEST

我已经尝试过很多关于StAcExcel的问题,但是出于某种原因,没有一个现有的解决方案对我有用。

附件是我试图转换的excel文件示例。 test.xlsx

0 回复 | 直到 6 年前

1

rokumaru 6 年前

代码的问题在于您指定了 -NoHeader 参数。
你必须说明 `n 而不是 \n 作为换行符 Replace() 方法。

$test = Import-Excel test.xlsx -StartRow 4
$test | ForEach-Object {
    if($_.HEADER1) {
        $_.HEADER1 = $_.HEADER1.Replace("`n", ", ")
        $_.HEADER1 = $_.HEADER1.Replace("&amp;", "&")
    }
    if($_.HEADER3) {
        $_.HEADER3 = $_.HEADER3.Replace("`n", ", ")
    }
}

$test | Export-Csv new-test.csv -Delimiter "|" -NoTypeInformation

输出:

"HEADER1"|"HEADER2"|"HEADER3"
"04.05 test & t, 04.06.01 test test, 04.10.02 test & t, 04.07.01 test test"|"test"|"TEST, TEST"

使用 $obj.psobject.Properties 属性替换所有属性的值。

# import
$data = Import-Excel test.xlsx -StartRow 4

# replace the values of all properties
$data | ForEach-Object { $_.psobject.Properties } | Where-Object Value -is string |
ForEach-Object { $_.Value = $_.Value.Replace("`n",", ").Replace("&amp;", "&") }

# export
$data | Export-Csv new-test.csv -Delimiter "|" -NoTypeInformation

如果要插入空行和列,请在转换后添加它们。 ConvertTo-CSV .

$data | ConvertTo-Csv -Delimiter "|" -NoTypeInformation | ForEach-Object { "" } { "|" + $_ } | Out-File new-test.csv

输出:


|"HEADER1"|"HEADER2"|"HEADER3"
|"04.05 test & t, 04.06.01 test test, 04.10.02 test & t, 04.07.01 test test"|"test"|"TEST, TEST"

如果标题重复,则 -报头 参数是必需的。并在保存前删除自动附加的标题。

$data = Import-Excel test.xlsx -StartRow 4 -StartColumn 2 -NoHeader
$data | foreach { $_.psobject.Properties } | where Value -is string | foreach { $_.Value = $_.Value.Replace("`n",", ").Replace("&amp;", "&") }

$data | ConvertTo-Csv -Delimiter "|" -NoTypeInformation | Select-Object -Skip 1 | Out-File new-test.csv