VinnyGuitara VinnyGuitara - 2 months ago 10
SQL Question

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?

Answer

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)