0

I have a below data in a text file.

author ; testname1 Revision ; 121 Date ; 10/5/2018 Path ; dev/test1 Message ; notes: testdata1 author ; testname2 Revision ; 1212 Date ; 10/6/2018 Path ; dev/test2 Message ; notes: testdata2 author ; testname3 Revision ; 1213 Date ; 10/5/2018 Path ; dev/test3 Message ; notes: testdata3

I want to read this and export to CSV which look like below.

author,Revision,Date,Path,Message testname1,121,10/5/2018,dev/test1,notes: testdata1 testname2,1212,10/6/2018,dev/test2,notes: testdata2 testname3,1213,10/5/2018,dev/test3,notes: testdata3

Any suggestions?

I have tried below code

$local:InputFilePath = "path of file" $local:OutFilePathCSV = "path of csv file" $local:CSVDelimiter = "," $local:OutDataList = New-Object -TypeName System.Collections.Arraylist $local:CurrentDataList = New-Object -TypeName System.Collections.Hashtable Select-String -Path $InputFilePath -Pattern "^[\s]" -NotMatch | ForEach-Object { $local:CurrentLine = ($_.Line).TrimEnd() $CurrentLine $OutDataList.Add($(New-Object -TypeName System.Management.Automation.PSObject -Property $CurrentDataList)) | Out-Null $CurrentDataList.Clear() if ($CurrentLine -match "^[\s]*([\w]*)[\s]+(.*)") { $CurrentDataList.Add($matches[1], $matches[2]) $matches[1] $matches[2] #break } } $OutDataList | Sort-Object -Property Serial | Select-Object -Property author, Revision, Date, Action, Path | Export-Csv -Path $OutFilePathCSV -Delimiter $CSVDelimiter -NoTypeInformation
Improve this question
6
  • 2
    Please show us what you have tried so far. You will need Get-Content and Export-Csv Commented Oct 17, 2018 at 13:31
  • I am new to stackoverflow also .., figuring out a way to post the code Commented Oct 17, 2018 at 13:44
  • I am sure i tried get content and export csv and hash table .., all those stuff but no luck. Commented Oct 17, 2018 at 13:46
  • Yes, but we would really like to see some of your efforts, even if they fail so we can help you with the code. Commented Oct 17, 2018 at 13:51
  • I need to correct this below two lines Select-String -Path $InputFilePath -Pattern "^[\s]" -NotMatch $CurrentLine -match "^[\s]*([\w]*)[\s]+(.*)" Commented Oct 17, 2018 at 14:10

2 Answers 2

Reset to default
1

Found a super simple way to solve your problem by creating a hashtable and exporting it to a csv:

#requires -Version 3 $path = 'C:\Temp\data.txt' $data = Get-Content -Path $path -ReadCount 5 $collection = foreach ($obj in $data) { $out = [ordered]@{} foreach ($line in $obj.Split("`n")) { $a, $b = ($line -split ';').Trim() $out[$a] = $b } [pscustomobject]$out } $newPath = 'C:\Temp\file.csv' $collection | Export-Csv -Path $newPath -Encoding ascii -NoTypeInformation -Force

This solution assumes your text document is well-formed.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks a lott!! @TheIncorrigible1
An alternative to a fixed read count is splitting at the first field with a RegEX using a postive look ahead $data = (Get-Content .\data.txt -Raw) -split "`n(?=author)" +1
0

You're on the right path. However, there are a couple of ways to do this. This is the easiest, most straight-forward method. However, because you have all the different columns listed individually in the input file, you will need to preemptively know, at least,the number of columns, and the delimiter used (here it's the semicolon).

Sticking with your example text file, and as much as your code as possible, this is how you would do it.

$InputFilePath = "path of file" $OutFilePathCSV = "path of csv file" $CSVDelimiter = "," $OutDataList = New-Object -TypeName System.Collections.Arraylist $ColumnNumbers = 5 $InputFileDelimter = ';' $InputFileData = Select-String -Path $InputFilePath -Pattern "^[\s]" -NotMatch for ($i = 0 ; $i -lt $InputFileData.count; $i += $ColumnNumbers) { $CurrentLine = New-Object PSObject for ($j = 0 ; $j -lt $ColumnNumbers; $j++) { if ($InputFileData[$i+$j].Line -match "^(.*)\s*$InputFileDelimter\s*(.*)\s*$") { $CurrentLine | Add-Member -MemberType NoteProperty -Name $matches[1] -Value $matches[2] } } $OutDataList.Add($CurrentLine) } $OutDataList | Select-Object -Property Author, Revision, Date, Action, Path | Export-Csv -Path $OutFilePathCSV -Delimiter $CSVDelimiter -NoTypeInformation

Tips/info:

  • With your regex "^[\s]*([\w]*)[\s]+(.*)"
    • ^[\s]* is redundant because you are already excluding lines that begin with white space in the select string.
    • ([\w]*) will not capture columns with spaces in the name (not necessary here, but for future reference).
    • (.*) will capture the delimiter as well.
  • With the regex "^(.*)\s*$InputFileDelimter\s*(.*)\s*$"
    • ^(.*)\s* will capture full column names without the white space before the delimiter. You can swap this for ^\w* if it will always be only one word.
    • $InputFileDelimter\s*(.*)\s*$ will capture the entire column value, without leading or trailing white space.

Notes:

  • $CSVDelimter isn't necessary because Export-CSV defaults to using a comma.
  • You don't have a column named Serial, so the sort doesn't do anything in your code.

Hope this helps! Good luck with PowerShell!

Edit:

From the comment on another answer below: Fetch data from txt file and export it to CSV

An alternative to a fixed read count is splitting at the first field with a RegEX using a postive look ahead $data = (Get-Content .\data.txt -Raw) -split "`n(?=author)"

This is actually a really good idea, and I like it. The only problem is that you have to be sure your first column is always listed first, per group of properties.

Improve this answer

1 Comment

Thanks for the detailed explanation!! @J. Doe

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.