fixed-width query results

Question

I'm using PowerShell to run a query that automatically creates a UTF8 text file. One of the requirements is that query the results are fixed-width and the results start at the first character. I have certain currency fields containing currency that are meeting the fixed-width requirement but the results do not start at the first character. I am trying to figure out how to use PS to a) make sure the results are a fixed width and b) start at the first character of the column.

cast(coalesce(SLCDPM.FBAL, '') as char(20)) as [BALANCE],

results dont start at character 1 of the field but is 20 characters long

however this

cast(coalesce(SLCDPM.FBAL, '') as varchar(20)) as [BALANCE]
OR
LTRIM(cast(coalesce(SLCDPM.FBAL, '') as varchar(20))) as [BALANCE]

results start at character 1 but fixed-width not correct.

Invoke-Sqlcmd -ServerInstance "ESTSVRSTD\MSSQLP901" -Database "C1PROD" -Query "SELECT  distinct
cast(coalesce(ARECEIVABLE.FCUSTNO, ' ') as char(20)) as [CUSTNO],
cast(coalesce(CUSTOMER.fcompany, ' ') as char(255)) as [COMPANY],
cast(coalesce(CUSTOMER.FMSTREET, ' ') as char(240)) as [ADDRESS1],
cast(coalesce(CUSTOMER.FCITY, ' ') as char(50)) as [CITY],
cast(coalesce(CUSTOMER.fstate, ' ') as char(50)) as [STATE],
cast(coalesce(CUSTOMER.FZIP, ' ') as char(20)) as [ZIP],
cast(coalesce(CUSTOMER.FCRLIMIT, ' ') as char(20)) as [CRD_LIMIT],
replace(convert(varchar, FDLPAYDATE,101),'/','') as [LASTPAY],
cast(coalesce(CUSTOMER.FBAL, '') as char(40)) as [BALANCE],
cast(coalesce(CUSTOMER.FNPAYAMT, '') as char(20)) as [LASTAMT],
cast(coalesce(CUSTOMER.FANN_SALES, '') as char(20)) as [YTDSALES],
cast(coalesce('EAST SERVICE', ' ') as char(20)) as [BUSINESSUNIT]
FROM         
CUSTOMER 
right JOIN
ARECEIVABLE ON
CUSTOMER.fcustno = ARECEIVABLE.fcustno 
LEFT JOIN
shmast ON ARECEIVABLE.fsono = shmast.fcsono AND ARECEIVABLE.fcustno = shmast.fcnumber
where FBAL > ' '
AND CUSTOMER.fcountry = 'United States of America'       
ORDER BY CUSTNO" | ConvertTo-Csv -NoTypeInformation -Delimiter "`t" | 
  Select-Object -Skip 1 | 
  % { $_ -replace "`t`"", '' -replace '[\r?\n]', " " } | % {$_ -replace '"',""} | Out-File ("\\ESTSVRSTD\D\ARCUSTOMER.txt") -Force -Encoding ascii 

Answer 1

Considering you want to use Powershell to extract the data in a fixed width format, you are better off doing the formatting in Powershell.

As we have no sample data, no sample query, nor expected results, this is an example you'll need to use for your own uses. You can use create the format you want and assign that to a variable, like shown inDavid Brabant's answer, and then put that into a file.

As you state "the results start at the first character" I assume you mean that you want the data to be headerless, hence the -HideTableHeaders switch.

The example uses sys.databases as an example, and demonstrates all columns have a fixed width:

$format = @{Expression={$_.name};width=128}, @{Expression={$_.database_id};width=5}, @{Expression={$_.compatibility_level};width=5}, @{Expression={$_.collation_name};width=60}, @{Expression={$_.recovery_model};width=15}
Invoke-Sqlcmd -ServerInstance 'srv-sql2022-dev\sandbox' -TrustServerCertificate `
-Query "SELECT name, database_id, compatibility_level, collation_name, recovery_model FROM sys.databases" | 
Format-Table $format -HideTableHeaders > db.txt

Which results in the following for me:

master                                                                                                                               1   160 SQL_Latin1_General_CP1_CI_AS                                               3
tempdb                                                                                                                               2   160 SQL_Latin1_General_CP1_CI_AS                                               3
model                                                                                                                                3   160 SQL_Latin1_General_CP1_CI_AS                                               3
msdb                                                                                                                                 4   160 SQL_Latin1_General_CP1_CI_AS                                               3
Sandbox                                                                                                                              5   160 Latin1_General_CI_AS                                                       3
AdventureWorks2019                                                                                                                   6   160 SQL_Latin1_General_CP1_CI_AS                                               3
AdventureWorksDW2019                                                                                                                 7   160 SQL_Latin1_General_CP1_CI_AS                                               3
CaseSensitive                                                                                                                        8   160 SQL_Latin1_General_CP1_CS_AS                                               3
OldDB                                                                                                                                9   160 Latin1_General_CI_AS                                                       3
PermissionsPOC                                                                                                                      10   160 Latin1_General_CI_AS                                                       3
SecurityTest                                                                                                                        11   160 Latin1_General_CI_AS                                                       3
SecurityTest2                                                                                                                       12   160 Latin1_General_CI_AS                                                       3
SillyTest                                                                                                                           13   160 Latin1_General_CI_AS                                                       3