CODEWITHSUNDEEP
powershellparsingsplittrim
catalyph
catalyph

Reputation: 101

String trim and split

I have a text file that I read and I need to get the values from.

Example text file:

[Site 01]
DBServer=LocalHost
DBName=Database01
Username=admin
Password=qwerty

[Site 02]
DBServer=192.168.0.10
DBName=Database02
Username=admin
Password=qwerty

Currently my code reads through the file and places each each as an array entry for each line DBServer= that is found and this text file can have many sites:

$NumOfSites = Get-Content $Sites |
              Select-String -Pattern "DBServer=" -Context 0,3
$i = 0
$NumOfSites | ForEach-Object {
    $svr  = $NumOfSites[$i] -isplit "\n" |
            % { ($_ -isplit 'DBServer=').Trim()[1] }
    $db   = $NumOfSites[$i] -isplit "\n" |
            % { ($_ -isplit 'DBName='.Trim())[1] }
    $uid  = $NumOfSites[$i] -isplit "\n" |
            % { ($_ -isplit 'Username='.Trim())[1] }
    $pswd = $NumOfSites[$i] -isplit "\n" |
            % { ($_ -isplit 'Password='.Trim())[1] }
    $i = $i+1
}

I can't get each attribute to split out properly without some extra spaces or something nicely as a string variable. I just need to extract the info to put into an SQL connection line as variables from the format of the file example I have.

Upvotes: 2

Views: 2270

Answers (2)

TheMadTechnician
TheMadTechnician

Reputation: 36322

Other than the record headers (i.e. [Site 01]) the rest can be handled by ConvertFrom-StringData just fine. We can just convert the records to objects directly splitting on the header row more or less. ConvertFrom-StringData turns a multi-line string into a hashtable, and you can just cast that as a [PSCustomObject] and viola, you have objects that are easy to use.

$NumOfSites = Get-Content $Sites -raw
$SiteObjects = $NumOfSites -split '\[.+?\]'|%{[PSCustomObject](ConvertFrom-StringData -StringData $_)}

Then you can manipulate $SiteObjects however you see fit (output to CSV if you want, or filter on any property using Select-Object). Or, if you're looking to make connections you can loop through it building your connections as needed...

ForEach($Connection in $SiteObjects){
    $ConStr = "Server = {0}; Database = {1}; Integrated Security = False; User ID = {2}; Password = {3};" -f $Connection.DBServer.Trim(), $Connection.DBName.Trim(), $Connection.Username.Trim(), $Connection.Password.Trim()
    <Do stuff with SQL>
}

Edit: Updating my answer since the sample text was changed to add <pre> and </pre>. We just need to remove those, and since the OP is getting errors about methods on null values we'll filter for null as well.

$NumOfSites = Get-Content $Sites -raw
$SiteObjects = $NumOfSites -replace '<.*?>' -split '\[.+?\]' | ?{$_} |%{[PSCustomObject](ConvertFrom-StringData -StringData $_)}
ForEach($Connection in $SiteObjects){ 
    $svr = $Connection.DBServer.Trim() 
    $db  = $Connection.DBName.Trim() 
    $uid = $Connection.Username.Trim() 
    $pwd = $Connection.Password.Trim()
}

Upvotes: 3

Maximilian Burszley
Maximilian Burszley

Reputation: 19684

Here's a suggestion if you only care about getting the value after the equals:

Get-Content Example.txt |
  ForEach-Object {
      Switch -Regex ($_) {
          'dbs.+=' { $svr = ($_ -replace '.+=').Trim()
          .. etc ..
      }
  }

Get-Content piped to ForEach-Object will interpret each line as its own object.

Edit:
You were most of the way there, but it's unnecessary to -split the lines

$NumOfSites = Get-Content $Sites | Select-String -pattern "DBServer=" -Context 0,3
$NumOfSites | ForEach-Object {
    Switch -Wildcard ($_) {
        'DBS*=' { $svr = ($_ -replace '.+=').Trim() }
        'DBN*=' { $db  = ($_ -replace '.+=').Trim() }
        'U*='   { $uid = ($_ -replace '.+=').Trim() }
        'P*='   { $pw  = ($_ -replace '.+=').Trim() }
    }
}

Upvotes: 1

Related Questions