CODEWITHSUNDEEP
powershell
user12547230
user12547230

Reputation: 11

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

$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.

Upvotes: 1

Views: 1378

Answers (1)

Lee_Dailey
Lee_Dailey

Reputation: 7489

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

Upvotes: 2

Related Questions