Powershell format SQL output

Question

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?

This more or less per design, as you call sqlcmd (Application), which returns a list of formatted strings. Take a look at invoke-sqlcmd, which will return a list of objects, with properties. — Peter Schneider
– Peter Schneider, Commented Oct 23, 2019 at 8:48
If possible, you can convert this output to string and replace all the white space with whatever you want --> $MeasurementsOlderThanOneWeek | foreach { [string]$_ -replace '\s{2,}',"`t" }. — AdminOfThings
– AdminOfThings, Commented Oct 23, 2019 at 10:42

Peter Schneider · Accepted Answer · 2019-10-23 08:51:58Z

1

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) ..."
}

answered Oct 23, 2019 at 8:51

Peter Schneider

2,9591 gold badge16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thijs · Accepted Answer · 2019-10-24 09:39:14Z

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

That's because you probably have pasted two lines in powershell.. it should be pasted as single line...

Collectives™ on Stack Overflow

Powershell format SQL output

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related