0

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
theHydra
  • 21
  • 4
  • 3
    Data isn't "justified" at all. That's a presentation layer requirement, it has *nothing* to do with the SQL. Provide the value, to your application, as a numerical value, *not* a `varchar`, and then have your *application* worry about if to use left/right/centre alignment. – Thom A Apr 27 '23 at 14:03
  • These query results are going to a text file that is automatically imported into another application that processes the results. The requirement is that each field be properly formatted, meet a certain character count and the results start at a specific line and position, therefore I need to have the data in the correct position when output. I am however using the Invoke-Sqlcmd in PowerShell to run the query so I can perform other tasks like removing character, forcing UTF8. Is there anything is PS I can use to force the left alignment? – theHydra Apr 27 '23 at 14:56
  • So it's *nothing* to do with "justifying". You're actually asking how to create a fixed width text file..? – Thom A Apr 27 '23 at 14:57
  • Yes sir. I thought that's what I was doing with the forcing the data type char(20) which again does produce a 20-character field, but it's prepending the results with zeros before the value and I need to values to start at the first position and fill the rest with zeros after the value. – theHydra Apr 27 '23 at 15:09
  • This is therefore a task for whatever the tool is you are using to *extract* the data; any good ETL tool will have the option(s) for defining what your fixed width text file looks like. You should be asking about *that*, not the SQL. – Thom A Apr 27 '23 at 15:10
  • Fair enough. 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. Thank you. – theHydra Apr 27 '23 at 15:35
  • You should [edit] your qusetion to reflect what you are *really* asking then. – Thom A Apr 27 '23 at 15:36
  • Why `RIGHT JOIN` from `CUSTOMER` when `CUSTOMER.fcountry ` must have a non-`NULL` value. – Thom A Apr 27 '23 at 22:19

1 Answers1

0

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
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • After reviewing your comments and the link you provided, I understand the concept being this solution. However, would I be able to integrate this into my existing solution? I’m trying to plug in the Format-Table $format and $format = @{Expression={$_.customers};width=40} into what I already have but am not seeing the expected results. Above is the query, again only issue I’m having is with CUSTOMER.FNPAYAMT. (i couldn't include the snippet here) Hope that makes sense. – theHydra Apr 27 '23 at 21:44
  • Not without sample data and expected results, no, @theHydra . – Thom A Apr 27 '23 at 22:19
  • How can I provide that? I'm not sure how. – theHydra Apr 28 '23 at 13:58
  • [Edit](https://stackoverflow.com/posts/76121268/edit) your question to provide it, @theHydra . You'll want to use DDL and DML for the data and demonstrate the results like I have above. – Thom A Apr 28 '23 at 14:04
  • Apologies, I am confused on how to get you all that. Not sure how I can create sample data – theHydra Apr 28 '23 at 15:52
  • As I mentioned, put sample DDL and DML in your qusetion, @theHydra . – Thom A Apr 28 '23 at 15:58