Скрипт Powershell для обработки многострочных данных в ячейке Excel и преобразования в CSV с помощью Import-excel или Import-csv

В: Я хочу преобразовать Excel в CSV, но Excel содержит многострочные данные в ячейке вместе с ";" как разделитель. Желаемый результат - файл csv с символом "|" как разделитель столбцов, но "," как разделитель данных в той же ячейке. Я не могу использовать Excel, поэтому использую модуль ImportExcel.

Введите формат Excel (прикрепленный файл в конце вопроса): -

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

Строка # представляет собой строку Excel, а С # представляет столбец, а ЗАГОЛОВОК # представляет заголовок столбцов. Как видно, строки 1-3 и первый столбец Excel пусты. Текст второго и четвертого столбца многострочный, но находится в одной ячейке (в той же строке Row5).

Затем я попытался импортировать Excel для каждого объекта и заменить символ новой строки на "," наконец экспортировать его в csv, используя (export-csv) с "|" как разделитель.

Сценарий, который я сделал, выглядит следующим образом: -

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

$test | foreach-object {

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

$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

Как видно, текст после разделителя ";" при вводе переходит в другой столбец, а многострочная строка по-прежнему разбирается на отдельные строки. Я хочу, чтобы вся эта многострочная строка была объединена в одну с разделителем «,» вместе с текстом после «';» делиметр.

Желаемый 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

Я пробовал много вопросов из stackoverflow, связанных с этим, но по какой-то причине ни одно из существующих решений, похоже, не работает для меня.

Прилагаю образец файла Excel, который я пытаюсь преобразовать.

powershell excel import-csv import-from-excel export-csv
person Dhanesh Agrawal    schedule 29.05.2019    source источник
comment
Предоставьте mvce, поскольку у нас нет доступа к test.xlsx. Возможно, возьмите несколько примеров строк и поместите их в массив $test = @('line1', 'line2')   -  person lloyd    schedule 29.05.2019
comment
@lloyd .. Спасибо за ответ. Я приложил документ к вопросу в конце. Надеюсь, это поможет, но дайте мне знать, если потребуется дополнительная информация или пример.   -  person Dhanesh Agrawal    schedule 29.05.2019

Ответы (1)


arrow_upward
1
arrow_downward

Проблема с вашим кодом заключается в том, что вы указываете параметр -NoHeader.
и вам нужно указать `n вместо \n в качестве символа новой строки в Replace() методе.

$test = Import-Excel test.xlsx -StartRow 4
$test | ForEach-Object {
    if($_.HEADER1) {
        $_.HEADER1 = $_.HEADER1.Replace("`n", ", ")
        $_.HEADER1 = $_.HEADER1.Replace("&", "&")
    }
    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("&", "&") }

# 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"

Если заголовки дублируются, параметр -NoHeader является обязательным. И перед сохранением удалите автоматически прикрепленные заголовки.

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

$data | ConvertTo-Csv -Delimiter "|" -NoTypeInformation | Select-Object -Skip 1 | Out-File new-test.csv
person rokumaru    schedule 29.05.2019
comment
Большое спасибо @rokumaru за ответ, и он работает для этих данных, но мне нужен параметр NoHeader, потому что файл Excel содержит повторяющиеся имена столбцов, а затем вышеуказанное решение дает ошибку при добавлении параметра NoHeader или HeaderName. Можем ли мы что-нибудь для этого сделать? Я скоро выложу файл с тестовыми данными. - person Dhanesh Agrawal; 29.05.2019
comment
Я приложил лист для справки. Для этого требуется NoHeader для импорта. - person Dhanesh Agrawal; 29.05.2019
comment
Это работает. Большое спасибо, но как нам удалить двойные кавычки? Я пробовал кодировку -UTF8, но это не помогает. - person Dhanesh Agrawal; 29.05.2019
comment
В этом случае используйте оператор -join вместо ConvertTo-CSV. $data | foreach { $_.psobject.Properties.Value -join "|" } | Out-File new-test.csv - person rokumaru; 29.05.2019
comment
Спасибо. Ты замечательный ! - person Dhanesh Agrawal; 29.05.2019