CODEWITHSUNDEEP
sql-serverpowershell
Thijs
Thijs

Reputation: 387

Powershell format SQL output

I wrote a SQL query 

$MeasurementsOlderThanOneWeek = sqlcmd -S "ms-sql-1264" -d "ShellPlus" -Q "SELECT [OrderID],[MasterID],[FamilyName],[GivenName],[DateOfBirth],[AppointmentDateAndTime] FROM [ShellPlus].[dbo].[Requests] LEFT JOIN [ShellPlus].[dbo].[Patients] ON [ShellPlus].dbo.Requests.PatientID = [ShellPlus].dbo.Patients.InternalPatientID WHERE AppointmentDateAndTime < dateadd(week,-1,getdate())"

to get some rows out of a database. Powershell gives it to me like this:

UMCA1834969                      1658276                              Larry van der                                    J                                                1945-06-21 00:00:00.000 2019-10-15 11:00:00.000

There are a lot of spaces between the columns, is there a way to remove the whitespace in between and replace it with a tab? Trim doesn't do anything?

Upvotes: 0

Views: 398

Answers (2)

Thijs
Thijs

Reputation: 387

I already solved using a for loop and going through the text line by line... But when i use Invoke-Sqlcmd i get this:

PS H:> $MeasurementsOlderThanOneWeek = invoke-sqlcmd -Server "ms-sql-1264" -Database "ShellPlus" -Query "SELECT [OrderID],[MasterID],[FamilyName],[GivenName], [DateOfBirth],[AppointmentDateAndTime] FROM [ShellPlus].[dbo].[Requests] LEFT JOIN [ShellPlus].[dbo].[Patients] ON [ShellPlus].dbo.Requests.PatientID = [ShellPlus].dbo.Patients.InternalPatientID WHERE AppointmentDateAndTime < dateadd(week,-1,getdate())"

At line:2 char:13
+ "ShellPlus" -Query "SELECT [OrderID],[MasterID],[FamilyName],[GivenNa ...
+             ~~~~~~
Unexpected token '-Query' in expression or statement.
At line:2 char:20
+ ... llPlus" -Query "SELECT [OrderID],[MasterID],[FamilyName],[GivenName],
+                    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Unexpected token '"SELECT [OrderID],[MasterID],[FamilyName],[GivenName], 
[DateOfBirth],[AppointmentDateAndTime] FROM [ShellPlus].[dbo].[Requests] LEFT JOIN 
[ShellPlus].[dbo].[Patients] ON [ShellPlus].dbo.Requests.PatientID = 
[ShellPlus].dbo.Patients.InternalPatientID WHERE AppointmentDateAndTime < 
dateadd(week,-1,getdate())"' in expression or statement.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : UnexpectedToken

Best regards, Thijs

Upvotes: 0

Peter Schneider
Peter Schneider

Reputation: 2939

Instead of using sqlcmd you might use invoke-sqlcmd (part of the SqlServer module)

$MeasurementsOlderThanOneWeek = invoke-sqlcmd -Server "ms-sql-1264" -Database 
"ShellPlus" -Query "SELECT [OrderID],[MasterID],[FamilyName],[GivenName], 
[DateOfBirth],[AppointmentDateAndTime] FROM [ShellPlus].[dbo].[Requests] LEFT JOIN 
[ShellPlus].[dbo].[Patients] ON [ShellPlus].dbo.Requests.PatientID = 
[ShellPlus].dbo.Patients.InternalPatientID WHERE AppointmentDateAndTime < 
dateadd(week,-1,getdate())"

$MeasurementsOlderThanOneWeek | foreach-object {
    "$($_.OrderID) $($_.MasterID) ..."
}

Upvotes: 1

Related Questions