PowerShell生成固定宽度且左对齐的查询结果文件问题求助

问题核心 #

需要通过PowerShell执行SQL查询并生成固定宽度+内容左对齐的UTF8文本文件，当前遇到的矛盾：

• 用CAST(...) AS char(N)：固定宽度满足，但货币字段默认右对齐，内容不从首字符开始
• 用varchar(N)或LTRIM()：内容左对齐，但无法保证固定宽度

解决方案 #

1. SQL层面：实现左对齐+固定宽度 #

对数值/货币类型字段，先转成字符串并去除左侧空格，再通过右侧补空格后截取固定长度，确保左对齐且宽度一致。通用格式：

LEFT(LTRIM(CAST(COALESCE(字段名, '') AS VARCHAR(固定宽度))) + SPACE(固定宽度), 固定宽度) AS [别名]

针对你的查询，修改所有需要固定宽度左对齐的字段：

-- 客户编号
LEFT(LTRIM(CAST(COALESCE(ARECEIVABLE.FCUSTNO, ' ') AS VARCHAR(20))) + SPACE(20), 20) AS [CUSTNO],
-- 公司名称
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.fcompany, ' ') AS VARCHAR(255))) + SPACE(255), 255) AS [COMPANY],
-- 地址
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FMSTREET, ' ') AS VARCHAR(240))) + SPACE(240), 240) AS [ADDRESS1],
-- 城市
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FCITY, ' ') AS VARCHAR(50))) + SPACE(50), 50) AS [CITY],
-- 州省
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.fstate, ' ') AS VARCHAR(50))) + SPACE(50), 50) AS [STATE],
-- 邮编
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FZIP, ' ') AS VARCHAR(20))) + SPACE(20), 20) AS [ZIP],
-- 信用额度
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FCRLIMIT, '') AS VARCHAR(20))) + SPACE(20), 20) AS [CRD_LIMIT],
-- 余额
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FBAL, '') AS VARCHAR(40))) + SPACE(40), 40) AS [BALANCE],
-- 最后付款金额
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FNPAYAMT, '') AS VARCHAR(20))) + SPACE(20), 20) AS [LASTAMT],
-- 年销售额
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FANN_SALES, '') AS VARCHAR(20))) + SPACE(20), 20) AS [YTDSALES],
-- 业务单元
LEFT(LTRIM(CAST(COALESCE('EAST SERVICE', ' ') AS VARCHAR(20))) + SPACE(20), 20) AS [BUSINESSUNIT]

2. PowerShell脚本调整 #

• 替换Encoding ascii为Encoding utf8以满足UTF8输出要求
• 简化字符串替换逻辑，避免破坏已生成的固定宽度格式

修改后的完整PowerShell脚本：

Invoke-Sqlcmd -ServerInstance "ESTSVRSTD\MSSQLP901" -Database "C1PROD" -Query @"
SELECT DISTINCT
LEFT(LTRIM(CAST(COALESCE(ARECEIVABLE.FCUSTNO, ' ') AS VARCHAR(20))) + SPACE(20), 20) AS [CUSTNO],
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.fcompany, ' ') AS VARCHAR(255))) + SPACE(255), 255) AS [COMPANY],
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FMSTREET, ' ') AS VARCHAR(240))) + SPACE(240), 240) AS [ADDRESS1],
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FCITY, ' ') AS VARCHAR(50))) + SPACE(50), 50) AS [CITY],
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.fstate, ' ') AS VARCHAR(50))) + SPACE(50), 50) AS [STATE],
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FZIP, ' ') AS VARCHAR(20))) + SPACE(20), 20) AS [ZIP],
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FCRLIMIT, '') AS VARCHAR(20))) + SPACE(20), 20) AS [CRD_LIMIT],
REPLACE(CONVERT(VARCHAR, FDLPAYDATE, 101), '/', '') AS [LASTPAY],
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FBAL, '') AS VARCHAR(40))) + SPACE(40), 40) AS [BALANCE],
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FNPAYAMT, '') AS VARCHAR(20))) + SPACE(20), 20) AS [LASTAMT],
LEFT(LTRIM(CAST(COALESCE(CUSTOMER.FANN_SALES, '') AS VARCHAR(20))) + SPACE(20), 20) AS [YTDSALES],
LEFT(LTRIM(CAST(COALESCE('EAST SERVICE', ' ') AS VARCHAR(20))) + SPACE(20), 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 | 
  ForEach-Object { $_ -replace '"', '' -replace '\r?\n', ' ' } | 
  Out-File "\\ESTSVRSTD\D\ARCUSTOMER.txt" -Force -Encoding utf8

说明 #

• SQL中SPACE(N)生成N个空格，LEFT(..., N)确保最终字符串长度严格为N，LTRIM()保证内容左对齐
• PowerShell仅去除多余引号和换行，避免破坏已生成的固定宽度格式
• 已将输出编码改为UTF8，符合需求

内容的提问来源于stack exchange，提问作者theHydra