CODEWITHSUNDEEP
powershell
pearlanddba
pearlanddba

Reputation: 1

PowerShell to extract values from text file and export to csv

Code will import text and scan text file looking records for matching CNAME and export servername and alias values to CSV file? The question is how to select the values? 

 Get-Content \\fzzzzz\rrrrr\ttttt\DNSExport\ExportList.txt | select-string 'CNAME' -SimpleMatch -Context 0,1 | Out-file "C:\temp\extract.txt"


    Example of my text file

    server                          Alias
    XXXXXXXXX       Alias (CNAME)   server1.xxxx.yy.zz. static
    ZZZZZZZZZ   Alias (CNAME)   server2.xxxx.yy.zz. static
    AAAAAAAAA   Alias (CNAME)   server3.xxxx.yy.zz. static
    BBBBBBBBB   Alias (CNAME)   server4.xxxx.yy.zz. static
    CCCCCCCCC   Alias (CNAME)   server5.xxxx.yy.zz. static

Upvotes: 0

Views: 724

Answers (1)

AdminOfThings
AdminOfThings

Reputation: 25001

If ExportList.txt is just a dump from a DNS server or DNS server zone and it is delimited, you may do the following:

Get-Content \\fzzzzz\rrrrr\ttttt\DNSExport\ExportList.txt |
  Where-Object { $_ -match 'Alias \(CNAME\)' } |
    ConvertFrom-Csv -Delimiter "`t" -Header 'Alias','Type','Server','Timestamp' |
      Select-Object Alias,Type,Server |
        Export-Csv output.csv -NoType

This assumes your delimiter is tab. You can change the delimiter as you see fit.

Assuming your example text file is called cname.txt with no proper delimiters, I'd do the following:

((Get-Content cname.txt).Trim() |
  Select-Object -Skip 1) -replace "(?<!Alias)\s+(?!\(CNAME\))",',' |
    ConvertFrom-Csv -Header 'Server','Type','Alias','Timestamp' |
      Select-Object Server,Alias |
        Export-Csv output.csv -NoType

Explanation:

  • Trim() removes beginning and ending white space (per line in this case).
  • Select-Object -Skip 1 skips the first line of the file. The first line would be the server and alias line.
  • (?<!Alias)\s+(?!\(CNAME\)) matches all consecutive white space that does not come between Alias and (CNAME).
  • -replace replaces the matched white space with ,.
  • ConvertFrom-Csv is merely to format the data to be comma-delimited with named column headers.
  • Select-Object Server,Alias just selects the Server and Alias data. You can remove this line completely if you want all of the data.

Upvotes: 1

Related Questions