Building objects from Text log

I got asked to convert text files with certain values in them and turn them into a table

The following is an output of a SQL Log for fragmentation.

I see that the first ‘paragraph’ is information based on the server the second is the database and the third is based on the tables and details of the fragmentation.

There will be other table/fragmentation information.

What I would like is to capture the SQL server/version the database that it is under and the table fragmention (details like [GEN_ACCOUNT] (INDEX), [CTSO_Landing] [GEN_ACCOUNT] (database),

There will be a number of database and tables listed in the log.

By the multidimensional type of the data I think XML will be the best way to do it. That way I can create a report out of it.

I don’t expect anyone to write the script I have planned (it is fun writing it). Curtis kindly helped me in a previous post on how to use regular expression on single text and extract that…although I know that regex matches line by line but I think I need to get Powershell to process the text as a paragraph? Is there a way to get it to process the paragraph as a group one by one and search for regex that way?

For example the first paragraph is information about the server. My plan is to use regex to determine if the paragraph is server information and if it is then it will extract the values for the server as objects down the line and then would be added to a collection.

I would use a foreach loop to process each information.

The guidance that I need help with is how to get powershell to analyse only that block of text

Below is an excerpt of the text log. (Names have been changed).

Date and time: 2016-05-07 04:30:00
Server: CTSO_SVR\DATAHUB
Version: 12.0.4213.0
Edition: Enterprise Edition: Core-based
Procedure: [dbadb].[dbo].[IndexOptimize]
Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 20, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y'
Source: http://ola.hallengren.com

Date and time: 2016-05-07 04:30:00
Database: [CTSO_Landing]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: FULL
Availability group:DATAHUB-AG
Availability group role: PRIMARY

Date and time: 2016-05-07 04:30:01
Command: ALTER INDEX [GEN_ACCOUNT] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 52391, Fragmentation: 98.2535
Outcome: Succeeded
Duration: 00:00:08
Date and time: 2016-05-07 04:30:09

Date and time: 2016-05-07 04:30:10
Command: ALTER INDEX [accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 4954, Fragmentation: 98.6677
Outcome: Succeeded
Duration: 00:00:04
Date and time: 2016-05-07 04:30:14

I am aware that it might be possible to get into SQL Server and get the objects that way but this may prove to be a good exercise. Thank so much for the guidance.

Ola has addressed this with a switch:

@logtotable=‘Y’

It would write directly to a table, so you don’t have to parse anything.

Hi, Ak thanks for the reply I am not quite sure what you mean. Could you please elaborate ? Thanks :slight_smile:

EDIT: thanks for that. I did a bit of digging and see what you meant. I will let the other team know about this but I still think it would be a good idea to know how to parse text to objects for other log files. So I still pose the question out there about the powershell handling paragraph at a time for regex? Thanks. :slight_smile:

When I added a URL to his documentation page my reply disappeared without an error. I’m glad you found it. I’ll let someone else help you with a question about paragraphs.

@wei-yen-tan
If the records we all the same type, then you could parse the log very quickly using the ConvertFrom-String cmdlet. I was unsuccessful using this cmdlet with this dataset since there are multiple record types. Maybe someone more advanced with ConvertFrom-String could offer some input.

With that said, below is an example of parsing the data line by line. It is a quick example, so there is some more handling an decisions that need to be made, like what you want to do with the multiple date time stamps. Right now It just keeps the last one instead of all of them.

Basically each line is prefixed with a field designation, so we use that designation with Switch to determine what to do when that field is found. We’ve designated “Server” at the Start of record, so when Server is found, the existing record is output and a new record is started. This, of course, assumes that the logs are sequential and no simultaneous processes are run.

cls
$data = @'
Date and time: 2016-05-07 04:30:00
Server: CTSO_SVR\DATAHUB
Version: 12.0.4213.0
Edition: Enterprise Edition: Core-based
Procedure: [dbadb].[dbo].[IndexOptimize]
Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 20, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y'
Source: http://ola.hallengren.com

Date and time: 2016-05-07 04:30:00
Database: [CTSO_Landing]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: FULL
Availability group:DATAHUB-AG
Availability group role: PRIMARY

Date and time: 2016-05-07 04:30:01
Command: ALTER INDEX [GEN_ACCOUNT] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 52391, Fragmentation: 98.2535
Outcome: Succeeded
Duration: 00:00:08
Date and time: 2016-05-07 04:30:09

Date and time: 2016-05-07 04:30:10
Command: ALTER INDEX [accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 4954, Fragmentation: 98.6677
Outcome: Succeeded
Duration: 00:00:04
Date and time: 2016-05-07 04:30:14

Date and time: 2016-05-07 04:30:00
Server: CTSO_SVR\DATAHUB
Version: 12.0.4213.0
Edition: Enterprise Edition: Core-based
Procedure: [dbadb].[dbo].[IndexOptimize]
Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 20, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y'
Source: http://ola.hallengren.com

Date and time: 2016-05-07 04:30:00
Database: [CTSO_Landing]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: FULL
Availability group:DATAHUB-AG
Availability group role: PRIMARY

Date and time: 2016-05-07 04:30:01
Command: ALTER INDEX [GEN_ACCOUNT] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 52391, Fragmentation: 98.2535
Outcome: Succeeded
Duration: 00:00:08
Date and time: 2016-05-07 04:30:09

Date and time: 2016-05-07 04:30:10
Command: ALTER INDEX [accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 4954, Fragmentation: 98.6677
Outcome: Succeeded
Duration: 00:00:04
Date and time: 2016-05-07 04:30:14
'@ -split "`r`n"

$data |
ForEach-Object {
    If ($_) {
        Switch ($_.substring(0,$_.indexof(":"))) {
            'Server' {
                If ($psrecord) {
                    $psrecord
                }
                $psrecord = [pscustomobject]@{
                    'Server' = "$input".substring("$input".indexof(":") + 2)
                    'Version' = ""
                    'Date and time' = ""
                    'Database' = ""
                    'Command' = ""
                    'Tables' = ""
                }
            }
            'Version' {$psrecord.Version = "$input".substring("$input".indexof(":") + 2)}
            'Date and time' {
                If ($psrecord) {
                    $psrecord.'Date and time' = "$input".substring("$input".indexof(":") + 2)
                }
            }
            'Database' {$psrecord.Database = "$input".substring("$input".indexof(":") + 2)}
            'Command' {
                $psrecord.Command = "$input".substring("$input".indexof(":") + 2)
                $psrecord.Tables = ([regex]::Matches("$input","\[([^dbo].*?)]") | ForEach-Object {$_.Groups[1].Value}) -join ","
            }
        }
    }
}
$psrecord
Remove-Variable psrecord

Results:

Server        : CTSO_SVR\DATAHUB
Version       : 12.0.4213.0
Date and time : 2016-05-07 04:30:00
Database      : [CTSO_Landing]
Command       : ALTER INDEX [accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Tables        : accout_idx1,CTSO_Landing,GEN_ACCOUNT

Server        : CTSO_SVR\DATAHUB
Version       : 12.0.4213.0
Date and time : 2016-05-07 04:30:14
Database      : [CTSO_Landing]
Command       : ALTER INDEX [accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Tables        : accout_idx1,CTSO_Landing,GEN_ACCOUNT

Hiya Curtis. Thanks for the tip on this. I have decided to rework the structure a little bit.

I saw that you were matching the value server and then getting the value after the semi colon.

What I would like is to look for the word index and get the first enclosed bracket , in the above example it would get [account_idx]. I have used select-string to find the word command and then used the -context parameter to select the first 2 lines preceding it and the 3 lines after it.

This has given me the paragraphs setting I want and then I can use Foreach to iterate through them I think.

Thank you Curtis for giving me the idea. I now know I must make an effort to study regex too.

Hey @wei-yen-tan,
For fun I wrote the following based on the sample dataset to parse each record type and convert it into a PowerShell Custom Object. You can then store and/or manipulate the objects as you would any other PowerShell object. Hope this helps, I sure enjoyed writing it!

cls
$data = @'
Date and time: 2016-05-07 04:30:00
Server: CTSO_SVR\DATAHUB
Version: 12.0.4213.0
Edition: Enterprise Edition: Core-based
Procedure: [dbadb].[dbo].[IndexOptimize]
Parameters: @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @FragmentationLevel2 = 20, @PageCountLevel = 1000, @SortInTempdb = 'N', @MaxDOP = NULL, @FillFactor = NULL, @PadIndex = NULL, @LOBCompaction = 'Y', @UpdateStatistics = NULL, @OnlyModifiedStatistics = 'N', @StatisticsSample = NULL, @StatisticsResample = 'N', @PartitionLevel = 'Y', @MSShippedObjects = 'N', @Indexes = NULL, @TimeLimit = NULL, @Delay = NULL, @WaitAtLowPriorityMaxDuration = NULL, @WaitAtLowPriorityAbortAfterWait = NULL, @LockTimeout = NULL, @LogToTable = 'Y', @Execute = 'Y'
Source: http://ola.hallengren.com

Date and time: 2016-05-07 04:30:00
Database: [CTSO_Landing]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: FULL
Availability group:DATAHUB-AG
Availability group role: PRIMARY

Date and time: 2016-05-07 04:30:01
Command: ALTER INDEX [GEN_ACCOUNT] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 52391, Fragmentation: 98.2535
Outcome: Succeeded
Duration: 00:00:08
Date and time: 2016-05-07 04:30:09

Date and time: 2016-05-07 04:30:10
Command: ALTER INDEX [accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Comment: ObjectType: Table, IndexType: NonClustered, ImageText: N/A, NewLOB: No, FileStream: N/A, ColumnStore: No, AllowPageLocks: Yes, PageCount: 4954, Fragmentation: 98.6677
Outcome: Succeeded
Duration: 00:00:04
Date and time: 2016-05-07 04:30:14
'@ -split "`n"

Function ParseRecord {
    Param($data, $type, $currentline)
    Switch ($type) {
        Server {
                $RecordObject = [pscustomobject]@{
                    'Date and time' = ""
                    'Server' = ""
                    'Version' = ""
                    'Edition' = ""
                    'Procedure' = ""
                    'Parameters' = ""
                    'Source' = ""
                } #[pscustomobject}
        } #Server
        Database {
                $RecordObject = [pscustomobject]@{
                    'Date and time' = ""
                    'Database' = ""
                    'Status' = ""
                    'Standby' = ""
                    'Updateability' = ""
                    'User access' = ""
                    'Is accessible' = ""
                    'Recovery model' = ""
                    'Availability group' = ""
                    'Availability group role' = ""
                } #[pscustomobject}
        } #Database
        Command {
                $RecordObject = [pscustomobject]@{
                    'Start Date and time' = ""
                    'Command' = ""
                    'Comment' = ""
                    'Outcome' = ""
                    'Duration' = ""
                    'Stop Date and time' = ""
                } #[pscustomobject}
        } #Command
    } #Switch
    While ($data[$currentline]) {
        $field = $data[$currentline].substring(0,$data[$currentline].indexof(":"))
        If ($field -eq 'Date and time' -AND $type -eq 'Command') {
            If ($RecordObject.'Start Date and time') {
                $RecordObject.'Stop Date and time' = $data[$currentline].substring($data[$currentline].indexof(":") + 2)
            } Else {
                $RecordObject.'Start Date and time' = $data[$currentline].substring($data[$currentline].indexof(":") + 2)
            } # If Else
        } Else {
            $RecordObject.$field = $data[$currentline].substring($data[$currentline].indexof(":") + 2)
        } #If Else
        $currentline++
    } #While
    Remove-Variable 'field'
    return @{Object = $RecordObject; lastline = $currentline}
} #Function

$lines = $data.Count - 1
$currentline = 0
While ($currentline -lt $lines) {
    $recordtype = $data[$currentline +1].substring(0,$data[$currentline +1].indexof(":"))
    $record = ParseRecord $data $recordtype $currentline
    $currentline = $record.lastline
#    $record.Object
    $currentline++

    Switch ($recordtype) {
        Server {
            "Server: $($record.Object.Server)"
            "Version: $($record.Object.Version)"
        } #Server
        Database {
            "Database: $($record.Object.Database)"
        } #Database
        Command {
            "Extractions: $(([regex]::Matches($record.Object.Command,"\[([^dbo].*?)]") | ForEach-Object {$_.Groups[1].Value}) -join ",")"
        } #Command
    } #Switch
} #While

Results:

Server: CTSO_SVR\DATAHUB
Version: 12.0.4213.0
Database: [CTSO_Landing]
Extractions: GEN_ACCOUNT,CTSO_Landing,GEN_ACCOUNT
Extractions: accout_idx1,CTSO_Landing,GEN_ACCOUNT

Thanks Curtis! In the extractions can I refer to the accout_idx ,CTSO_Landing and gen_account individually?

For example:

accout_idx1,CTSO_Landing,GEN_ACCOUNT

I would extract accout_idx out on its own as its an index.

CTSO_Landing as a database and GEN_Account as a table.

Thank you so much for your guidance with this. I can then start playing with it and build something referring to your examples… :slight_smile:

EDIT: Found out that I can do split on the extraction field, and then reference it that way.
Thank you very much Curtis, this is going to be fun. :slight_smile:

Well, the extractions is just a join of multiple objects, so you can change that to not join them and just return the first instance instead like this:

“Extraction: $(([regex]::Matches($record.Object.Command,”[([^dbo].*?)]“) | ForEach-Object {$_.Groups[1].Value})[0])”

Hi Curtis,

In your first example you have “$input” listed in your object properties but is not defined.

Is $input the current line?

I suppose if it is I could do something like this:

$b =$input  | select-string -allmatches "\[([^dbo].*?)]" 
		$psrecord = [pscustomobject]@{
					'Index' ="$b.Matches.Captures.captures.groups[1].value"
					'Date' = ""
					'Database' = ""
					'Table' = ""
					'Page Count' = ""
					'Fragmentation' = ""
					'Outcome' =""
				}

$input is an automatic variable from the pipleline input. I am using this because I need to calculate based on the original data from the pipeline input, not the current $_

For Example:

$data = "Hi my name is: Steve"
$data |
ForEach-Object {
    Switch ($data.Substring($data.IndexOf(':')+2)) {
        Steve {"`$_ = $_"
               "`$input = $input"
        }
    }
}

Results

$_ = Steve
$input = Hi my name is: Steve

glad to see you already have your answer. in the interest of knowledge sharing, here is my attempt

# input trimmed to save space
$data = @'
Date and time: 2016-05-07 04:30:00
Database: [CTSO_Landing]
Status: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Is accessible: Yes
Recovery model: FULL
Availability group:DATAHUB-AG
Availability group role: PRIMARY

Date and time: 2016-05-07 04:30:01
Command: ALTER INDEX [GEN_ACCOUNT] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)
Comment: ObjectType: Table, IndexType: Clustered, ImageText: No, NewLOB: No, FileStream: No, ColumnStore: No, AllowPageLocks: Yes, PageCount: 52391, Fragmentation: 98.2535
Outcome: Succeeded
Duration: 00:00:08
Date and time: 2016-05-07 04:30:09
'@.Split("`n")

$data = $data | % {if ($_ -match '^(?:\s+)?$') {'&'} else {$_}}

$sections = ($data | Out-String).Split('&')

$results = $(foreach ($item in $sections) {
    $item = $item.Split("`n") | ? {$_ -notmatch '^(?:\s+)?$'}
    $hash = @{}
    for ($i = 0; $i -lt $item.Count; $i++) {
        $name = $item[$i].substring(0, $item[$i].indexof(':')).trim()
        $value = $item[$i].substring($item[$i].indexof(':') + 1).trim()
        $count = 0
        $newname = $name
        while ($newname -in $hash.keys) {
            $count++
            $newname = "$name$count"
        }
        $hash.Add($newname, $value)
    }
    [pscustomobject]$hash
})

$results

*edit: side-note - curtis, you may be unintentionally excluding some captures in your regex. if you only want to exclude [dbo] this may be an option (i think… I’m not great with lookahead / lookbehind)

$test = '[btest][accout_idx1] ON [CTSO_Landing].[dbo].[GEN_ACCOUNT] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)'

"Extraction: $(([regex]::Matches($test, "\[((?!dbo).*?)]") | ForEach-Object {$_.Groups[1].Value})[0])"

Thank you very much Curtis,

I have reworked it a bit and this is my final result:

$data = get-content "E:\test\db.txt" 
$data2 = $data -split "`r`n"

[CmdletBinding()]
$data2 | ForEach-Object {if ($_){
                Switch ($_.substring(0,$_.indexof(":"))) {
                    'Command'{ 
                    if ($psrecord)
                    {
                        $psrecord
                    }
                        $b = $input | select-string -allmatches "\[([^dbo].*?)]"
                         
            	        $psrecord = [pscustomobject]@{
					        'Index' = ($b.Matches[0].Groups[1].Value)
					        'Date' = ""
					        'Database' = ($b.Matches[1].Groups[1].Value)
					        'Table' = ($b.Matches.Captures[2].Groups.value[1])
					        'PageCount' = ""
					        'Fragmentation' = ""
                            'Operation' = ""
					        'Outcome' =""
				        }
                    
                        $Rebuild = $input | select-string -allmatch "Rebuild"
                        if ($Rebuild -ne $null){
                            $psrecord.Operation = "Rebuild"
                        }

                       
                }
                    'Comment'{
                            $b = $input -split ","
                            $psrecord.PageCount = [int]($b[7]).Substring(12) 
                            $psrecord.Fragmentation = [int]($test[8]).Substring(16)

                    }
                    'outcome'{

                            $psrecord.Outcome = "$input".substring("$input".indexof(":") + 2)
                    }

                    
                    'Date and Time' {
                          If ($psrecord) {
                    $psrecord.Date = ([datetime]"$input".substring("$input".indexof(":") + 2))
                            }
                    }

   
            }

        }
}

What I found is at times it creates errors at times like this:

Cannot index into a null array.
At E:\Test\test2.ps1:48 char:22
+                         $psrecord = [pscustomobject]@{
+                         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : NullArray
 
Exception calling "Substring" with "2" argument(s): "Length cannot be less than zero.
Parameter name: length"
At E:\Test\test2.ps1:39 char:30
+ $data3 | ForEach-Object {if ($_){
+                              ~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentOutOfRangeException

On investigation I did some debugging and I have some new lines which I thought was removed the split “`n`r”?

It also has some lines that have

Msg 50000, xxxx
Msg 9002,xxxx

(x marking the other lines)

Thank you very much Curtis for giving me the guidelines. I owe you a beer!

I’ve managed to work around the problems and it is now working.