powershell breaking my string

Powershell breaks my strings when I have specifically told it to put the carriage return at the end of the line

I have PS script that is supposed to write a batch sql insert in groups of 100:

function WriteBatch {
    echo $sql
    $Command.CommandText = $sql
        $Command.ExecuteNonQuery()
}

$server = "norcrpsqldev08"
$Database = "SandBoxVinny"
$Path = "Y:\BMPS"
$Counter = 0

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
[string]$sql =     "
                begin 
                    insert into TestPowerShell(NameString, FileSize, Mode, Directory, CreationTime, LastAccessTime, LastWriteTime)
                    values " 

foreach($file in Get-ChildItem -Verbose -Recurse -Path $Path | Select-Object  Name, Length, Mode, Directory, CreationTime, LastAccessTime, LastWriteTime) {
    $fileName = $file.Name
    $fileSize = ([int]$file.Length)
    $fileMode = $file.Mode
    $fileDirectory = $file.Directory
    $fileCreationTime = [datetime]$file.CreationTime
    $fileLastAccessTime = [datetime]$file.LastAccessTime
    $fileLastWriteTime = [datetime]$file.LastWriteTime
    $sql = $sql + "('$fileName', '$fileSize', '$fileMode', '$fileDirectory', '$fileCreationTime', '$fileLastAccessTime', '$fileLastWriteTime'),`r`n"
    #$sql += "`n"
    $Counter++

    If($Counter -eq 100) {
        $sql = $sql.Trim().Trim(',')
        $sql = $sql + " End"
        WriteBatch
        $Counter = 0
        $sql = "
                begin 
                    insert into TestPowerShell(NameString, FileSize, Mode, Directory, CreationTime, LastAccessTime, LastWriteTime)
                    values " 
    }

}

if ($Counter -gt 0){
    $sql = $sql.Trim().Trim(',')
    $sql = $sql + " End"
    WriteBatch
}

$Connection.Close()

The resulting strings often have a line break where it is not supposed to be:

Results:

    ('PCA - implementation demand post transfer 9.14.16.xlsx', '56191', '-a---', 'Y:\BMPS\Salesforce\Projects\Merging PCA org into RCA\Data Move\Data\Final Move\Final upload\Compare reports', '09/14/2016 17:46:30', '09/14/2016 17:52:08', '09/14/2016 17:52:08'),
('RCA - implementation demand post transfer 9.14.16_bad.xlsx', '55442', '-a---', 'Y:\BMPS\Salesforce\Projects\Merging PCA org into RCA\Data Move\Data\Final Move\Final upload\Compare reports', '09/14/2016 17:46:30', '09/14/2016 17:46:55', '09/14/2016 17:46:55'
),
('RCA System - post transfer 9.14.16 No account.xlsx', '40498', '-a---', 'Y:\BMPS\Salesforce\Projects\Merging PCA org into RCA\Data Move\Data\Final Move\Final upload\Compare reports', '09/14/2016 17:20:37', '09/14/2016 17:26:49', '09/14/2016 17:26:49'),

This should look like this:

    ('PCA - implementation demand post transfer 9.14.16.xlsx', '56191', '-a---', 'Y:\BMPS\Salesforce\Projects\Merging PCA org into RCA\Data Move\Data\Final Move\Final upload\Compare reports', '09/14/2016 17:46:30', '09/14/2016 17:52:08', '09/14/2016 17:52:08'),
('RCA - implementation demand post transfer 9.14.16_bad.xlsx', '55442', '-a---', 'Y:\BMPS\Salesforce\Projects\Merging PCA org into RCA\Data Move\Data\Final Move\Final upload\Compare reports', '09/14/2016 17:46:30', '09/14/2016 17:46:55', '09/14/2016 17:46:55'),
('RCA System - post transfer 9.14.16 No account.xlsx', '40498', '-a---', 'Y:\BMPS\Salesforce\Projects\Merging PCA org into RCA\Data Move\Data\Final Move\Final upload\Compare reports', '09/14/2016 17:20:37', '09/14/2016 17:26:49', '09/14/2016 17:26:49'),

This breaks my sql. Any thoughts?

Answers


Try this:

$Host.UI.RawUI.BufferSize= @{Width=5000;Height=100}

On older versions of PowerShell you may need:

$Host.UI.RawUI.BufferSize=New-Object System.Management.Automation.Host.Size(5000, 100)

Need Your Help

Android UI glitch when setting ActionBar color

android user-interface android-fragments android-actionbar visual-glitch

Together with friends I have created an Android app and I have a strange issue connected with the UI.

How to make use of RIA Services App.config files (without copying settings to web.config)

silverlight silverlight-4.0 web-config app-config wcf-ria-services

Even though I link my web-app with my RIA Services.web library, the App.config setting files are never used and I always need to copy the module settings manually to my web.config.