I realize that you are not familiar with PowerShell, but it's probably something that you should look into. I was in your position about 3 years ago and now use it in place of batch files 90% of the time.
In PowerShell this is relatively simple. You can run the array of strings through a ForEach loop, creating an object and adding members to it for each property, then when you reach a new Source line output the previous object and start a new one. It automatically makes an array for you, and you can pipe that to Export-CSV.
What I will do specifically is setup a variable $Record as an empty string.
Then I get the content of the file, and pipe it to a Where statement that will match each line against a RegEx match. That will create the automatic variable $Matches, which gets passed down the pipe along with the line. The match will capture everything before the first colon, and then everything following the colon and any trailing whitespace.
This is piped to a ForEach loop, which will perform once for each line. It checks if $Matches[1] (everything before the first colon) = 'Source'. If it does, it outputs the current contents of $Record, and creates a new $Record as a custom object with one property: 'Source' = $Matches[2] (everything after the first colon and trailing whitespace). If $Matches[1] does not equal 'Source' then it adds a new property to $Record where the property name is $Matches[1], and the value is $Matches[2]. For cleanliness I have performed the .Trim() method on $Matches[2] just to make sure there's no leading or trailing spaces or line feeds or anything strange.
After I process everything I run it through a Where statement again to remove blank records (such as the first one I setup beforehand). Then I output $Record one more time. As you said you wanted this in a CSV I have piped the whole loop and the trailing $Record to Export-CSV
$Record = ""
$Output = @()
Get-Content Input_data.txt | Where{$_ -match "([^:]*):\s*?(\S.*)"}|Foreach{
if($Matches[1] -eq "Source"){
$Output += $Record
$Record = [PSCustomObject]@{'Source'=$Matches[2].trim()}
}else{
$Record | Add-Member $Matches[1] $Matches[2].trim()
}
}|?{![string]::IsNullOrEmpty($_)} | Export-Csv Output.csv -NoTypeInformation
$Output += $Record
$Output | Export-Csv Output.csv -NoTypeInformation -Append
The result is a csv file with these contents:
"Source","Destination","Total bytes","MB per min"
"X:\folder_abc","Y:\Abc_folder","208,731,021","256.5"
"X:\folder_def","Y:\xyz_folder","123,134,545","326"
"X:\folder_foo","Y:\Baz_folder","24,344","532"
Or if you don't pipe it to Export-CSV it simply displays it on screen:
Source Destination Total bytes MB per min
------ ----------- ----------- ----------
X:\folder_abc Y:\Abc_folder 208,731,021 256.5
X:\folder_def Y:\xyz_folder 123,134,545 326
X:\folder_foo Y:\Baz_folder 24,344 532
Edit: Ok, you get errors with Add-Member the way I'm using it. That means you have an older version of PowerShell. There's 2 solutions to that. The first, and my recommendation, update PowerShell. Sometimes that isn't an option though, so that's fine, we can work with that.
The way I'm using Add-Member doesn't work if you are on PS v1 or v2. How I'm using it is that if you pipe an object to Add-Member and then specify 2 string arguments it assumes the first is a NotePropertyName and the second is a NotePropertyValue. You can see what it looks like above. So what to do if that doesn't work is to use the more verbose syntax of:
Add-Member -InputObject $TargetVariable -MemberType NoteProperty -Name Name -Value Value
In our case it means we replace the Add-Member line as such:
Add-Member -InputObject $Record -MemberType NoteProperty -Name $Matches[1] -Value $Matches[2].trim()
And you went and changed the input. That's easy to fix... Change the RegEx match from "([^:]*):\s*?(\S.*)" to "([^=]*)=\s*?(\S.*)". So putting it all together:
$Record = ""
$Output = @()
Get-Content Input_data.txt | Where{$_ -match "([^=]*)=\s*?(\S.*)"}|Foreach{
if($Matches[1] -eq "Source"){
If(![String]::IsNullOrEmpty($Record)){$Output += $Record}
$Record = [PSCustomObject]@{'Source'=$Matches[2].trim()}
}else{
Add-Member -InputObject $Record -MemberType NoteProperty -Name $Matches[1] -Value $Matches[2].trim()
}
}
$Output += $Record
$Output | Export-Csv C:\Temp\Output.csv -NoTypeInformation
Edit2: I guess I had forgotten that -Append isn't an option for Export-Csv in older versions of PowerShell. This can be worked with by collecting all data and outputting it once at the end. I have updated the last script in my answer by creating an empty array $Output near the top, then in the loop instead of just outputting $Record whenever one is completed I add it to the array. I also modified that line to go through an If statement to avoid adding blank records to the array. Then after the ForEach loop I add the last record to the array, and finally output the entire array of records to a CSV file.
