当前位置： 文江博客话题详情

使用PowerShell将CSV文件中的一行分为多行

发布于 2025-01-26 14:27:03 字数 5751 浏览 3 评论 0原文

这是我在本网站上的第一篇文章，所以请保持温柔：)（也是Powershell Noob） 我收到了一个.csv文件和.txt文件。我的工作是将来自TXT文件的数据输入右行中的CSV文件。

我设法做了这一部分。

正如您在标题“ Berechtigung”下看到的那样，有许多角色由A分开； 那就是我必须从CSV文件中的TXT文件中包含的信息。

我需要做的最后一件事是在帮助的帮助下将不同的角色分开；并将它们写入下一行。

这可能吗？

#dynamic variables
$password_Title = "Description" #Title of the password in the txt file (Description in pw7)
$row_Password = "Password" #row password in the txt file
$file_Path = "" #Path where the csv and txt file are located 
$txt_Content = "$file_Path\expensya.txt"  #Content of the txt file
$csv_File = "$file_Path\expensya.csv" #The CSV file which needs to be edited
$output_File_Path = "$file_Path\CSV-edited.csv" #Location and name of the of the new created csv file

$distance_row_title_pw = 4 #Distance between the row Description and password in the txt file
$distance_row_pw_Berechtigte = 8  #Distance between the row password and Berechtigte in the txt file 
$distance_row_Roles_description = 3 #Distance between the row Berechtigte and description in the txt file
#------------------------------------------------------------------------------------------------------------------------------------------------------------

# static variables
$content = Get-Content -Path $txt_Content 
$csv_File = Import-Csv -Path $csv_File -Delimiter ';' -Encoding Default 
$password_Description = "" #In this variable the the row under description will be saved from the txt file
$password = "" #The password will be saved in this variable
$row_Berechtigte = "Berechtigte"  #Row named Berechtigte in the txt file
$csv_row = 1 

#------------------------------------------------------------------------------------------------------------------------------------------------------------


$csv_File | Add-Member -MemberType NoteProperty -Name "Berechtigung" -Value $null 

#code execution

:forloop for($entry = 1; $entry -lt $content.Length; $entry++)
{
   if($content[$entry] -eq $password_title) 
   {
      $password_Description = $content[$entry+1]
      $entry = $entry + $distance_row_title_pw
   }  

   if ($content[$entry] -eq $row_Password)
   {
      $password = $content[$entry+1]
      $entry = $entry + $distance_row_pw_Berechtigte
   }

   if($content[$entry] -eq $row_Berechtigte) 
   {
   $csv_File| ForEach{if($_.$password_Title -eq $password_Description  -and $_.$row_Password-eq $password)
      {
         $csv_row = $csv_row +1
         if($_.Berechtigung -eq $null)
         {
            $roles = $content[$entry+1]
            $_.Berechtigung = $roles
            $entry = $entry + $distance_row_Roles_description
            continue forloop
         }
         elseif ($_.$password_Title -eq $password_Description  -and $_.$row_Password-eq $password -and $_.Berechtigung -eq $content[$entry+1]) 
         {
            Write-Host "Fehler bei diesem Eintrag $password_Description in Zeile $csv_row. Identical Values!"
            $csv_File | Export-Csv -Path $output_File_Path  -Delimiter ';' -NoTypeInformation | % {$_.Berechtigung -replace '"', ''}
            break
         }
      }
      }
   }
   
}

$csv_File | Export-Csv -Path $output_File_Path -Delimiter ';' -NoTypeInformation

这是CSV文件

Organisationseinheit;Description;Username;Password;Internetaddress;EMail-Address
Expensya;Expensya API Subscription Key - Secondary;;10;;
Expensya;Expensya API Subscription Key - Primary;;20;;
Expensya;Expensya API Token - Projects;;30;;
Expensya;Expensya API Token - HR;;40;;
Expensya;Expensya API Token - SAP;;50;;
Expensya;Expensya API Subscription Key - Secondary;;10;;

，这是TXT文件：

Password (v7): Expensya API Subscription Key - Primary (Expensya)
Description
Expensya API Subscription Key - Primary
Username

Password
20
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:24:23
Berechtigte
Administrator (Administrator); Administrators; Role_ApplMan_Technisch_-_Expensya
 
Password (v7): Expensya API Subscription Key - Secondary (Expensya)
Description
Expensya API Subscription Key - Secondary
Username

Password
10
Internetaddress

EMail-Address

Letzte Änderung
09.12.2021 13:43:00
Berechtigte
Administrator (Administrator); Administrators; Role_ApplMan_Technisch_-_Expensya
 
Password (v7): Expensya API Token - HR (Expensya)
Description
Expensya API Token - HR
Username

Password
40
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:22:38
Berechtigte
Administrator (Administrator); Administrators; Role_ApplMan_Technisch_-_Expensya
 
Password (v7): Expensya API Token - Projects (Expensya)
Description
Expensya API Token - Projects
Username

Password
30
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:22:59
Berechtigte
Administrator (Administrator); Administrators; Role_ApplMan_Technisch_-_Expensya
 
Password (v7): Expensya API Token - SAP (Expensya)
Description
Expensya API Token - SAP
Username

Password
50
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:21:59
Berechtigte
Administrator (Administrator); Administrators; Role_ApplMan_Technisch_-_Expensya
 
Password (v7): Expensya SSO Secret (App Registration) (Expensya)
Description
Expensya API Subscription Key - Secondary
Username
-
Password
10
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 16:23:11
Berechtigte
Administrator (Administrator); Administrators; Role_ApplMan_Technisch_-_Expensya
 
原文

this is my first post on this website so please be gentle :) (Also a powershell noob)
I have received a .csv file and a .txt file. My job is to input data from the txt file into the csv file in the right rows.

I have managed to do that part.

that is the output in the csv file.

As you can see under the headline "Berechtigung" there is a number of roles separated by a ;
That was the information i had to include from the txt file in the csv file.

The last thing i need to do is separate the different roles with the help of ; and write them into the next row.

I want this output

Is this possible?

#dynamic variables
$password_Title = "Description" #Title of the password in the txt file (Description in pw7)
$row_Password = "Password" #row password in the txt file
$file_Path = "" #Path where the csv and txt file are located 
$txt_Content = "$file_Path\expensya.txt"  #Content of the txt file
$csv_File = "$file_Path\expensya.csv" #The CSV file which needs to be edited
$output_File_Path = "$file_Path\CSV-edited.csv" #Location and name of the of the new created csv file

$distance_row_title_pw = 4 #Distance between the row Description and password in the txt file
$distance_row_pw_Berechtigte = 8  #Distance between the row password and Berechtigte in the txt file 
$distance_row_Roles_description = 3 #Distance between the row Berechtigte and description in the txt file
#------------------------------------------------------------------------------------------------------------------------------------------------------------

# static variables
$content = Get-Content -Path $txt_Content 
$csv_File = Import-Csv -Path $csv_File -Delimiter ';' -Encoding Default 
$password_Description = "" #In this variable the the row under description will be saved from the txt file
$password = "" #The password will be saved in this variable
$row_Berechtigte = "Berechtigte"  #Row named Berechtigte in the txt file
$csv_row = 1 

#------------------------------------------------------------------------------------------------------------------------------------------------------------


$csv_File | Add-Member -MemberType NoteProperty -Name "Berechtigung" -Value $null 

#code execution

:forloop for($entry = 1; $entry -lt $content.Length; $entry++)
{
   if($content[$entry] -eq $password_title) 
   {
      $password_Description = $content[$entry+1]
      $entry = $entry + $distance_row_title_pw
   }  

   if ($content[$entry] -eq $row_Password)
   {
      $password = $content[$entry+1]
      $entry = $entry + $distance_row_pw_Berechtigte
   }

   if($content[$entry] -eq $row_Berechtigte) 
   {
   $csv_File| ForEach{if($_.$password_Title -eq $password_Description  -and $_.$row_Password-eq $password)
      {
         $csv_row = $csv_row +1
         if($_.Berechtigung -eq $null)
         {
            $roles = $content[$entry+1]
            $_.Berechtigung = $roles
            $entry = $entry + $distance_row_Roles_description
            continue forloop
         }
         elseif ($_.$password_Title -eq $password_Description  -and $_.$row_Password-eq $password -and $_.Berechtigung -eq $content[$entry+1]) 
         {
            Write-Host "Fehler bei diesem Eintrag $password_Description in Zeile $csv_row. Identical Values!"
            $csv_File | Export-Csv -Path $output_File_Path  -Delimiter ';' -NoTypeInformation | % {$_.Berechtigung -replace '"', ''}
            break
         }
      }
      }
   }
   
}

$csv_File | Export-Csv -Path $output_File_Path -Delimiter ';' -NoTypeInformation

this is the csv file

Organisationseinheit;Description;Username;Password;Internetaddress;EMail-Address
Expensya;Expensya API Subscription Key - Secondary;;10;;
Expensya;Expensya API Subscription Key - Primary;;20;;
Expensya;Expensya API Token - Projects;;30;;
Expensya;Expensya API Token - HR;;40;;
Expensya;Expensya API Token - SAP;;50;;
Expensya;Expensya API Subscription Key - Secondary;;10;;

and this is the txt file:

Password (v7): Expensya API Subscription Key - Primary (Expensya)
Description
Expensya API Subscription Key - Primary
Username

Password
20
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:24:23
Berechtigte
Administrator (Administrator); Administrators; Role_ApplMan_Technisch_-_Expensya
 
Password (v7): Expensya API Subscription Key - Secondary (Expensya)
Description
Expensya API Subscription Key - Secondary
Username

Password
10
Internetaddress

EMail-Address

Letzte Änderung
09.12.2021 13:43:00
Berechtigte
Administrator (Administrator); Administrators; Role_ApplMan_Technisch_-_Expensya
 
Password (v7): Expensya API Token - HR (Expensya)
Description
Expensya API Token - HR
Username

Password
40
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:22:38
Berechtigte
Administrator (Administrator); Administrators; Role_ApplMan_Technisch_-_Expensya
 
Password (v7): Expensya API Token - Projects (Expensya)
Description
Expensya API Token - Projects
Username

Password
30
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:22:59
Berechtigte
Administrator (Administrator); Administrators; Role_ApplMan_Technisch_-_Expensya
 
Password (v7): Expensya API Token - SAP (Expensya)
Description
Expensya API Token - SAP
Username

Password
50
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:21:59
Berechtigte
Administrator (Administrator); Administrators; Role_ApplMan_Technisch_-_Expensya
 
Password (v7): Expensya SSO Secret (App Registration) (Expensya)
Description
Expensya API Subscription Key - Secondary
Username
-
Password
10
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 16:23:11
Berechtigte
Administrator (Administrator); Administrators; Role_ApplMan_Technisch_-_Expensya
 

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（1）

故人爱我别走 2025-02-02 14:27:03

由于您的文本文件中的示例数据 是完全限制的，并且不包含许多不同的 berechtigungen 我略有限制，以便能够展示如何解析纯文本带有转换的文件。

这是我保存为d的修改后的输入数据：\ sample \ input.log

Password (v7): Expensya API Subscription Key - Primary (Expensya)
Description
Expensya API Subscription Key - Primary
Username

Password
20
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:24:23
Berechtigte
Administrator (Administrator); Friedjoff; Role_ApplMan_Technisch_-_Expensya

Password (v7): Expensya API Subscription Key - Secondary (Expensya)
Description
Expensya API Subscription Key - Secondary
Username

Password
10
Internetaddress

EMail-Address

Letzte Änderung
09.12.2021 13:43:00
Berechtigte
Administrator (Administrator); Egon; Role_ApplMan_Analog_-_Expensya
 
Password (v7): Expensya API Token - HR (Expensya)
Description
Expensya API Token - HR
Username

Password
40
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:22:38
Berechtigte
Administrator (Administrator); Bernd; Role_ApplMan_Organisatorisch_-_Expensya
 
Password (v7): Expensya API Token - Projects (Expensya)
Description
Expensya API Token - Projects
Username

Password
30
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:22:59
Berechtigte
Administrator (Administrator); Fritz; Role_ApplMan_Magisch_-_Expensya
 
Password (v7): Expensya API Token - SAP (Expensya)
Description
Expensya API Token - SAP
Username

Password
50
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:21:59
Berechtigte
Administrator (Administrator); Hans; Role_ApplMan_Bla_-_Expensya
 
Password (v7): Expensya SSO Secret (App Registration) (Expensya)
Description
Expensya API Subscription Key - Secondary
Username
-
Password
10
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 16:23:11
Berechtigte
Administrator (Administrator); Rudi; Role_ApplMan_Keks_-_Expensya

现在，我使用卷曲括号和标签创建了一个模板来指定所需的值：

$Template = 
@'
Password (v7): Expensya API Subscription Key - Primary ({Orga*:Expensya})
Description
Expensya API {Description:Subscription Key - Primary}
Username

Password
{Password:20}
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:24:23
Berechtigte
{Berechtigung:Administrator (Administrator); Friedjoff; Role_ApplMan_Organisatorisch_-_Expensya}
 
Password (v7): Expensya API Subscription Key - Secondary ({Orga*:Expensya})
Description
Expensya API {Description:Subscription Key - Secondary}
Username

Password
{Password:10}
Internetaddress

EMail-Address

Letzte Änderung
09.12.2021 13:43:00
Berechtigte
{Berechtigung:Administrator (Administrator); Hans; Role_ApplMan_Analog_-_Expensya}
 
Password (v7): Expensya API Token - HR ({Orga*:Expensya})
Description
Expensya API {Description:Token - HR}
Username

Password
{Password:40}
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:22:38
Berechtigte
{Berechtigung:Administrator (Administrator); Franz; Role_ApplMan_Theoretisch_-_Expensya}
 
Password (v7): Expensya API Token - Projects ({Orga*:Expensya})
Description
Expensya API {Description:Token - Projects}
Username

Password
{Password:30}
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:22:59
Berechtigte
{Berechtigung:Administrator (Administrator); Kunz; Role_ApplMan_Technisch_-_Expensya}
'@

最后我使用了convertfrom----字符串带有4个示例来提取所需数据：

Get-Content -Path 'D:\sample\input.log' | 
    ConvertFrom-String -TemplateContent $Template

输出看起来像这样：

Orga     Description                  Password Berechtigung
----     -----------                  -------- ------------
Expensya Subscription Key - Primary   20       Administrator (Administrator); Friedjoff; Role_ApplMan_Technisch_-_Expensya
Expensya Subscription Key - Secondary 10       Administrator (Administrator); Egon; Role_ApplMan_Analog_-_Expensya
Expensya Token - HR                   40       Administrator (Administrator); Bernd; Role_ApplMan_Organisatorisch_-_Expensya
Expensya Token - Projects             30       Administrator (Administrator); Fritz; Role_ApplMan_Magisch_-_Expensya
Expensya Token - SAP                  50       Administrator (Administrator); Hans; Role_ApplMan_Bla_-_Expensya
Expensya Subscription Key - Secondary 10       Administrator (Administrator); Rudi; Role_ApplMan_Keks_-_Expensya

您可以学习如何使用convertfrom-string将
纯文本解析技术

Since your sample data in your text file is quite limitted and does not contain a lot of different sets of Berechtigungen I chnaged it slightly to be able to show how to parse plain text files with ConvertFrom-String.

Here are the modified input data I saved as D:\sample\input.log

Password (v7): Expensya API Subscription Key - Primary (Expensya)
Description
Expensya API Subscription Key - Primary
Username

Password
20
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:24:23
Berechtigte
Administrator (Administrator); Friedjoff; Role_ApplMan_Technisch_-_Expensya

Password (v7): Expensya API Subscription Key - Secondary (Expensya)
Description
Expensya API Subscription Key - Secondary
Username

Password
10
Internetaddress

EMail-Address

Letzte Änderung
09.12.2021 13:43:00
Berechtigte
Administrator (Administrator); Egon; Role_ApplMan_Analog_-_Expensya
 
Password (v7): Expensya API Token - HR (Expensya)
Description
Expensya API Token - HR
Username

Password
40
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:22:38
Berechtigte
Administrator (Administrator); Bernd; Role_ApplMan_Organisatorisch_-_Expensya
 
Password (v7): Expensya API Token - Projects (Expensya)
Description
Expensya API Token - Projects
Username

Password
30
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:22:59
Berechtigte
Administrator (Administrator); Fritz; Role_ApplMan_Magisch_-_Expensya
 
Password (v7): Expensya API Token - SAP (Expensya)
Description
Expensya API Token - SAP
Username

Password
50
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:21:59
Berechtigte
Administrator (Administrator); Hans; Role_ApplMan_Bla_-_Expensya
 
Password (v7): Expensya SSO Secret (App Registration) (Expensya)
Description
Expensya API Subscription Key - Secondary
Username
-
Password
10
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 16:23:11
Berechtigte
Administrator (Administrator); Rudi; Role_ApplMan_Keks_-_Expensya

Now I created a template using curly braces and labels to specify the desired values:

$Template = 
@'
Password (v7): Expensya API Subscription Key - Primary ({Orga*:Expensya})
Description
Expensya API {Description:Subscription Key - Primary}
Username

Password
{Password:20}
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:24:23
Berechtigte
{Berechtigung:Administrator (Administrator); Friedjoff; Role_ApplMan_Organisatorisch_-_Expensya}
 
Password (v7): Expensya API Subscription Key - Secondary ({Orga*:Expensya})
Description
Expensya API {Description:Subscription Key - Secondary}
Username

Password
{Password:10}
Internetaddress

EMail-Address

Letzte Änderung
09.12.2021 13:43:00
Berechtigte
{Berechtigung:Administrator (Administrator); Hans; Role_ApplMan_Analog_-_Expensya}
 
Password (v7): Expensya API Token - HR ({Orga*:Expensya})
Description
Expensya API {Description:Token - HR}
Username

Password
{Password:40}
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:22:38
Berechtigte
{Berechtigung:Administrator (Administrator); Franz; Role_ApplMan_Theoretisch_-_Expensya}
 
Password (v7): Expensya API Token - Projects ({Orga*:Expensya})
Description
Expensya API {Description:Token - Projects}
Username

Password
{Password:30}
Internetaddress

EMail-Address

Letzte Änderung
07.12.2021 14:22:59
Berechtigte
{Berechtigung:Administrator (Administrator); Kunz; Role_ApplMan_Technisch_-_Expensya}
'@

And at last I used ConvertFrom-String with the 4 examples to extract the desired data:

Get-Content -Path 'D:\sample\input.log' | 
    ConvertFrom-String -TemplateContent $Template

The output looks like this:

Orga     Description                  Password Berechtigung
----     -----------                  -------- ------------
Expensya Subscription Key - Primary   20       Administrator (Administrator); Friedjoff; Role_ApplMan_Technisch_-_Expensya
Expensya Subscription Key - Secondary 10       Administrator (Administrator); Egon; Role_ApplMan_Analog_-_Expensya
Expensya Token - HR                   40       Administrator (Administrator); Bernd; Role_ApplMan_Organisatorisch_-_Expensya
Expensya Token - Projects             30       Administrator (Administrator); Fritz; Role_ApplMan_Magisch_-_Expensya
Expensya Token - SAP                  50       Administrator (Administrator); Hans; Role_ApplMan_Bla_-_Expensya
Expensya Subscription Key - Secondary 10       Administrator (Administrator); Rudi; Role_ApplMan_Keks_-_Expensya

Here you can learn how to use ConvertFrom-String to parse plain text
Sophisitcated Techniques of Plain Text Parsing

回复 原文
~没有更多了~

关于作者

知你几分

暂无简介

文章
评论
27 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

櫻之舞

文章 0 评论 0

弥枳

文章 0 评论 0

m2429

文章 0 评论 0

寻找一个思念的角度

文章 0 评论 0

野却迷人

文章 0 评论 0

我怀念的。

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文