Split a row in a csv file into multiple rows using powershell-CodePudding

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.

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.

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

CodePudding user response：

@olaf

Organisationseinheit;Description;Username;Password;Internetaddress;EMail-Address;Berechtigung
Expensya;Expensya API Subscription Key - Secondary;;10;;;"Berechtigung_eins"
Expensya;Expensya API Subscription Key - Primary;;20;;;"Berechtigung_eins";"Berechtigung_zwei"
Expensya;Expensya API Token - Projects;;30;;;"Berechtigung_eins";"Berechtigung_zwei";"Berechtigung_drei"
Expensya;Expensya API Token - HR;;40;;;"Berechtigung_eins";"Berechtigung_drei"
Expensya;Expensya API Token - SAP;;50;;;"Berechtigung_eins";"Berechtigung_sechs"
Expensya;Expensya API Subscription Key - Secondary;;10;;;"Berechtigung_eins";"Berechtigung_zwei";"Berechtigung_vierunddreissig"

This would be the ideal output im trying to get

CodePudding user response：

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