PowerShell - Want to create fixed width output file for data retrieved from DB using invoke-sqlcmd

Question

$ObjDataSet2 = Invoke-SQLcmd -AbortOnError -Query $Query2 -Server $dbServer -Database $dbName -Username $dbUser -Password $dbPwd

$ObjDataSet2 |  ConvertTo-Csv -Delimiter '|' -NoTypeInformation | out-file -Filepath $OutFile -Append

I am brand new to PowerShell, so don't know a lot, this may be a simple straight forward solution.

I am using above statements to get data from DB to create a pipe delimited file. Instead of pipe delimited I want to generate a fixed width file, I am not sure how to and what the syntax is. Below is the current output from above statements followed by expected fixed width output.

Current Output:

Product_Code|Prodct_ID|Product_Type|Cost    
PM1234566|12345|Ellipse|10.13    
PM12345672|1234609|Wheel|12.10    
PM123456812|123470987|Rod|100.90    
PM1234569|12348|Ellipse|14

========

Expected Output:

Product_Code Prodct_ID   Product_Type    Cost    
PM1234566    12345       Ellipse         10.13    
PM12345672   1234609     Wheel           12.10    
PM123456812    123470987   Rod             100.90    
PM1234569       12348       Ellipse         14

==========

Required Column Widths:

Product_Code:  1-13

Product_ID:   14-25

Product_Type: 26-41

Cost:         42-50

Thanks in advance for the help, formatting of fixed length is not coming out right when it gets converted, please look at the details below for width of each field.

Lee_Dailey · Accepted Answer · 2019-12-16 19:26:28Z

2

this uses the -f string format operator and the ability to assign column sizes & left/right alignment. a negative alignment means to align LEFT instead of the default align-right.

i don't have your SQL stuff handy, so i used the CSV that you posted as the input. you can replace that easily enuf. [grin]

the $Results collection can be gracefully output to a file as needed.

# fake reading in a CSV file
#    in real life, use Import-CSV
$ProductList = @'
Product_Code|Product_ID|Product_Type|Cost
PM1234566|12345|Ellipse|10.13
PM12345672|1234609|Wheel|12.10
PM123456812|123470987|Rod|100.90
PM1234569|12348|Ellipse|14
'@ | ConvertFrom-Csv -Delimiter '|'

# the "-13" tells the formatter to left-align in a 13 character field
$FormatString = '{0,-13}{1,12}{2,16}{3,9}'

$Results = foreach ($PL_Item in $ProductList)
    {
    # send the resulting string to the `$Results` collection
    $FormatString -f $PL_Item.Product_Code, $PL_Item.Product_ID, $PL_Item.Product_Type, $PL_Item.Cost
    }

# send the collection to the screen
$Results

output ...

PM1234566           12345         Ellipse    10.13
PM12345672        1234609           Wheel    12.10
PM123456812     123470987             Rod   100.90
PM1234569           12348         Ellipse       14

answered Dec 16, 2019 at 19:26

Lee_Dailey

7,5292 gold badges25 silver badges27 bronze badges

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

2 Comments

user12547230 Over a year ago

Hi Lee, Thanks for the solution, I was able to plug in suggested format statement in my code and it worked like a charm, have only on issue now format statement is taking away column headings, is there a way to retain column headings in the file.

Lee_Dailey Over a year ago

@user12547230 - you will need to read the 1st line of the file to get the headers and then add that as the 1st line of your output. you can use the same format string for the headers. ///// Export-CSV with a tab delimiter won't works for fixed width since it is not aware of anything other than tabs.

Collectives™ on Stack Overflow

PowerShell - Want to create fixed width output file for data retrieved from DB using invoke-sqlcmd

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related