CODEWITHSUNDEEP
regexpowershellcsv
n00b
n00b

Reputation: 181

Split data from a text file 8 times to put in CSV in individual rows

I need to read some data from a text file and generate the CSV.

I am using this PowerShell script to get the data 

$PAGE = Get-Content .\DATA.txt | ForEach-Object {
     New-Object PSObject -Property @{
         FIELD1 = [regex]::Matches($_, '^[^\:]*[^\.txt:]').Value
         FIELD2 = [regex]::Match($_, 'DATA').Value
         FIELD3 = [regex]::Match($_, 'DATA\s(.+)').Value
         FIELD4  = [regex]::Match($_, 'DATA\s(\S*)\s(\S*)\s(\S*)\s(\S*)\s(\S*)\s(\S*)\s(\S*)\s(\S*)\s')
     }
}

$PAGE | Select-Object FIELD1, FIELD2, FIELD3, FIELD4 |
    Export-Csv DATA.csv -NoTypeInformation

The regex can be tested here. Sample input:

file1.txt: DATA 46546 TEST1 EUIRWY 283746827 2 1 3 3
file2.txt: DATA 96873 TEST2 KJH-ASKDJH 928374 0 0 0 0

The output of the above script on the given data is coming as this:

FIELD1 | FIELD2 | FIELD3                                     | FIELD4
file1  | DATA   | DATA 46546 TEST1 EUIRWY 283746827 2 1 3 3  | FIELD4
file2  | DATA   | DATA 96873 TEST2 KJH-ASKDJH 928374 0 0 0 0 | FIELD4

But the intended output is this.

FIELD1 | FIELD2 | FIELD3 | FIELD4
file1  | DATA   | D1     | 46546
file1  | DATA   | D2     | TEST1
file1  | DATA   | D3     | EUIRWY
file1  | DATA   | D4     | 283746827
file1  | DATA   | D5     | 2
file1  | DATA   | D6     | 1
file1  | DATA   | D7     | 3
file1  | DATA   | D8     | 3
file2  | DATA   | D1     | 96873
................................
................................
..............and so no 8 times

Basically Field4 will always have 8 strings or digits FIELD1 should be the name of the file 8 times FIELD2 will also appear 8 times that too 'DATA' and FIELD3 will always follow this sequence D1....D8. The same is to be repeated for every line in DATA.TXT file. So in sample I have taken 2 lines as file1.txt and file2.txt.

I am unable to think of how should I proceed. As I want to use the similar approach in the script because it's part of my main script and for uniformity I want to use this approach if possible.

Upvotes: 0

Views: 40

Answers (1)

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200573

I would do the match in a Where-Object filter and construct the desired objects in a for loop nested inside the ForEach-Object:

$re = '^(.*?\.txt):\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)\s+(\S+)$'
$PAGE = Get-Content .\DATA.txt | Where-Object {
    $_ -match $re
} | ForEach-Object {
    for ($i=3; $i -lt $matches.Count; $i++) {
        New-Object PSObject -Property @{
            FIELD1 = $matches[1]
            FIELD2 = $matches[2]
            FIELD3 = "D$($i-2))"
            FIELD4 = $matches[$i]
        }
    }
}

Upvotes: 1

Related Questions