sql - Powershell script to find line terminator?

Question

Can PowerShell "see" line terminators as

and r`n and 0x0a for example?

When returning the last character of a text file with line feed terminator I can see using Notepad ++, PowerShell returns the last character as blank, where I would have hoped it could return

What I'm trying to do is identify files that do not end with a line terminator, as these files break my SQL bulk insert.

Thanks

Answer 1

PowerShell can see line terminators. In order to see them displayed the way you want, you will have to manufacture that output.

$hash = @{10 = '
'; 13 = '
'}
if ((Get-Content file.txt -Raw) -match '(
|
)+z') {
    ([int[]][char[]]$matches.0).foreach({$hash[$_]})
}
else {
    'No Terminators Found'
}

The Get-Content -Raw reads a file as a single string. matches carriage return. matches line feed. | is a regex alternation (an effective OR). + matches one or more of the previous match. z is the end of the string (end of file in this case).

$matches will automatically contain the matched characters if -match operator returns true. Putting it into an if statement prevents its $true $false output and allows us to only check $matches if a match was successful. [char[]] converts the newline characters to an array of System.Char so that we can then return an integer array ([int[]]) of those Char objects. Having an array makes it easier to use the foreach() method and run code against each character. Without the array conversion, you'd have a single string of multiple characters from the match results.

The hash table is just a means to display the control characters in the desired format. You could just use if or switch statements to check instead.

---

By default, and control characters are not visible in the PowerShell console. Their byte and hex representations are visible though. Converting the characters to [char] and then [int] displays their byte representation as an integer. See below:

[int][char]"`r"
13
[int][char]"`n"
10

You can also utilize Format-Hex to see the hex presentation of the file contents.

Get-Content file.txt -Raw | Format-Hex

           00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

00000000   6C 69 6E 65 31                                   line1

Notice how the ending 0A ( ) and 0D ( ) are missing when a file has no ending newline characters? In contrast, see the same file below with the newline ending characters.

Get-Content file.txt -Raw | Format-Hex

           00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F

00000000   6C 69 6E 65 31 0D 0A                             line1..

You can get all the bytes from Format-Hex output by retrieving its Bytes property and notice the final 10 and 13 bytes.

(Get-Content file.txt -Raw | Format-Hex).Bytes

108
105
110
101
49
13
10