'Split a row in a csv file into multiple rows using powershell
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
Solution 1:[1]
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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |