Reputation: 11
$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
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