Hi



I see output of many commands coming in table format, and they look nice.

is there any cmdlet that I can use to format my script's output and print
them in such table format way? in my scenario, my script is reading a
database and i like to display the data in table format.

thanks,
umesh
--
Umesh

"Old programmers never die. They just terminate and stay resident."

Check out Format-Table

get-help format-table -full

will give you the details

--
Richard Siddaway
All scripts are supplied "as is" and with no warranty
PowerShell MVP
Blog: http://richardsiddaway.spaces.live.com/
PowerShell User Group: http://www.get-psuguk.org.uk


"Umesh Thakur" wrote:

> Hi
>
> I see output of many commands coming in table format, and they look nice.
>
> is there any cmdlet that I can use to format my script's output and print
> them in such table format way? in my scenario, my script is reading a
> database and i like to display the data in table format.
>
> thanks,
> umesh
> --
> Umesh
>
> "Old programmers never die. They just terminate and stay resident."
>

I am sorry, i think I didn't explain it completely.

I am enumerating thru rows of a SQL server table, and I like to print it in
table format in the same way as output of certain cmdlets are shown in
tabular format (eg. get-process).

I tried reading a row, stroring its column values separated by columns in a
string variable and then output it using write-host command and |
format-table piping.. however, its doesn't show in tabular format.


--
Umesh

"Old programmers never die. They just terminate and stay resident."



"RichS [MVP]" wrote:

> Check out Format-Table
>
> get-help format-table -full
>
> will give you the details
>
> --
> Richard Siddaway
> All scripts are supplied "as is" and with no warranty
> PowerShell MVP
> Blog: http://richardsiddaway.spaces.live.com/
> PowerShell User Group: http://www.get-psuguk.org.uk
>
>
> "Umesh Thakur" wrote:
>

> > Hi
> >
> > I see output of many commands coming in table format, and they look nice.
> >
> > is there any cmdlet that I can use to format my script's output and print
> > them in such table format way? in my scenario, my script is reading a
> > database and i like to display the data in table format.
> >
> > thanks,
> > umesh
> > --
> > Umesh
> >
> > "Old programmers never die. They just terminate and stay resident."
> >

in your script you can create custom psobject with neccessary properties and
fill them with your data from SQL and stream this object to output using
Format-Table formatting.
--
WBR, Vadims Podans
MVP: PowerShell
PowerShell blog - www.sysadmins.lv

"Umesh Thakur" <UmeshThakur@xxxxxx> rakstīja ziņojum�
"news:6EE60933-F1AF-49D2-916A-E20BD8BFA979@xxxxxx"...

> I am sorry, i think I didn't explain it completely.
>
> I am enumerating thru rows of a SQL server table, and I like to print it
> in
> table format in the same way as output of certain cmdlets are shown in
> tabular format (eg. get-process).
>
> I tried reading a row, stroring its column values separated by columns in
> a
> string variable and then output it using write-host command and |
> format-table piping.. however, its doesn't show in tabular format.
>
>
> --
> Umesh
>
> "Old programmers never die. They just terminate and stay resident."
>
>
>
> "RichS [MVP]" wrote:
>

>> Check out Format-Table
>>
>> get-help format-table -full
>>
>> will give you the details
>>
>> --
>> Richard Siddaway
>> All scripts are supplied "as is" and with no warranty
>> PowerShell MVP
>> Blog: http://richardsiddaway.spaces.live.com/
>> PowerShell User Group: http://www.get-psuguk.org.uk
>>
>>
>> "Umesh Thakur" wrote:
>>

>> > Hi
>> >
>> > I see output of many commands coming in table format, and they look
>> > nice.
>> >
>> > is there any cmdlet that I can use to format my script's output and
>> > print
>> > them in such table format way? in my scenario, my script is reading a
>> > database and i like to display the data in table format.
>> >
>> > thanks,
>> > umesh
>> > --
>> > Umesh
>> >
>> > "Old programmers never die. They just terminate and stay resident."
>> >

thanks for your help Vadims.
I tried to use custom psobject with an example at following site:

http://blog.sapien.com/index.php/200...tputs-a-table/

however, when I print each row, it comes with column headers as well. column
headers shold come only once, and rows should follow. perhaps, I am missing
something or doing something wrong.. you can put me in right direction.

Here is a script that I am writing:

$adOpenStatic = 3
$adLockOptimistic = 3

$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset
$dbPath = "d:\test.accdb"
$sql = Read-Host "Enter SQL query to run against the database:"

#This connection string is used for Access 2007 database. for access 2003
DB, use " Microsoft.Jet.OLEDB.4.0"
$objConnection.Open("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =
$dbPath")

$objRecordset.Open($sql, $objConnection,$adOpenStatic,$adLockOptimistic)


Write-Host "Executing following query and displaying result:"
Write-Host $sql

#get all fields of the result
$fieldsCount = $objRecordset.Fields.count
write-host "Total Fields are:" $fieldsCount

$out = new-object psobject #create new psobject for formatted output
for($c = 0;$c -lt $fieldsCount; $c++)
{
$out | add-member noteproperty $objRecordset.Fields.Item($c).name
$objRecordset.Fields.Item($c).name
}

$objRecordset.MoveFirst()
do
{
$out = new-object psobject #create new psobject for formatted output
for($c = 0;$c -lt $fieldsCount; $c++)
{
$out | add-member noteproperty $objRecordset.Fields.Item($c).name
$objRecordset.Fields.Item($c).value
}
write-output $out | ft
$objRecordset.MoveNext()
} until ($objRecordset.EOF -eq $True)


#close recordset and connection object
$objRecordset.Close()
$objConnection.Close()

--
Umesh

"Old programmers never die. They just terminate and stay resident."



"Vadims Podans [MVP]" wrote:

> in your script you can create custom psobject with neccessary properties and
> fill them with your data from SQL and stream this object to output using
> Format-Table formatting.
> --
> WBR, Vadims Podans
> MVP: PowerShell
> PowerShell blog - www.sysadmins.lv
>
> "Umesh Thakur" <UmeshThakur@xxxxxx> rakstīja ziņojum�
> "news:6EE60933-F1AF-49D2-916A-E20BD8BFA979@xxxxxx"...

> > I am sorry, i think I didn't explain it completely.
> >
> > I am enumerating thru rows of a SQL server table, and I like to print it
> > in
> > table format in the same way as output of certain cmdlets are shown in
> > tabular format (eg. get-process).
> >
> > I tried reading a row, stroring its column values separated by columns in
> > a
> > string variable and then output it using write-host command and |
> > format-table piping.. however, its doesn't show in tabular format.
> >
> >
> > --
> > Umesh
> >
> > "Old programmers never die. They just terminate and stay resident."
> >
> >
> >
> > "RichS [MVP]" wrote:
> >

> >> Check out Format-Table
> >>
> >> get-help format-table -full
> >>
> >> will give you the details
> >>
> >> --
> >> Richard Siddaway
> >> All scripts are supplied "as is" and with no warranty
> >> PowerShell MVP
> >> Blog: http://richardsiddaway.spaces.live.com/
> >> PowerShell User Group: http://www.get-psuguk.org.uk
> >>
> >>
> >> "Umesh Thakur" wrote:
> >>
> >> > Hi
> >> >
> >> > I see output of many commands coming in table format, and they look
> >> > nice.
> >> >
> >> > is there any cmdlet that I can use to format my script's output and
> >> > print
> >> > them in such table format way? in my scenario, my script is reading a
> >> > database and i like to display the data in table format.
> >> >
> >> > thanks,
> >> > umesh
> >> > --
> >> > Umesh
> >> >
> >> > "Old programmers never die. They just terminate and stay resident."
> >> >

>

"Umesh Thakur" <UmeshThakur@xxxxxx> wrote in message
newsCE6B995-91DF-42F9-8A63-8F6D10BE4098@xxxxxx

> thanks for your help Vadims.
> I tried to use custom psobject with an example at following site:
>
> http://blog.sapien.com/index.php/200...tputs-a-table/
>
> however, when I print each row, it comes with column headers as well.
> column
> headers shold come only once, and rows should follow. perhaps, I am
> missing
> something or doing something wrong.. you can put me in right direction.
>

Don't do:

write-output $out | ft

Just do:

write-output $out


You should also get rid of the write-host output.

Then, you have a script that outputs objects. When you use the script you
can choose to pipe the output to ft or any other formatter. You can also
sort, select, pick columns etc. If you put the ft into your script, you
only have that choice and your script emits text, not objects.

> Here is a script that I am writing:
>
> $adOpenStatic = 3
> $adLockOptimistic = 3
>
> $objConnection = New-Object -comobject ADODB.Connection
> $objRecordset = New-Object -comobject ADODB.Recordset
> $dbPath = "d:\test.accdb"
> $sql = Read-Host "Enter SQL query to run against the database:"
>
> #This connection string is used for Access 2007 database. for access 2003
> DB, use " Microsoft.Jet.OLEDB.4.0"
> $objConnection.Open("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =
> $dbPath")
>
> $objRecordset.Open($sql, $objConnection,$adOpenStatic,$adLockOptimistic)
>
>
> Write-Host "Executing following query and displaying result:"
> Write-Host $sql
>
> #get all fields of the result
> $fieldsCount = $objRecordset.Fields.count
> write-host "Total Fields are:" $fieldsCount
>
> $out = new-object psobject #create new psobject for formatted output
> for($c = 0;$c -lt $fieldsCount; $c++)
> {
> $out | add-member noteproperty $objRecordset.Fields.Item($c).name
> $objRecordset.Fields.Item($c).name
> }
>
> $objRecordset.MoveFirst()
> do
> {
> $out = new-object psobject #create new psobject for formatted output
> for($c = 0;$c -lt $fieldsCount; $c++)
> {
> $out | add-member noteproperty $objRecordset.Fields.Item($c).name
> $objRecordset.Fields.Item($c).value
> }
> write-output $out | ft
> $objRecordset.MoveNext()
> } until ($objRecordset.EOF -eq $True)
>
>
> #close recordset and connection object
> $objRecordset.Close()
> $objConnection.Close()
>
> --
> Umesh
>
> "Old programmers never die. They just terminate and stay resident."
>
>
>
> "Vadims Podans [MVP]" wrote:
>

>> in your script you can create custom psobject with neccessary properties
>> and
>> fill them with your data from SQL and stream this object to output using
>> Format-Table formatting.
>> --
>> WBR, Vadims Podans
>> MVP: PowerShell
>> PowerShell blog - www.sysadmins.lv
>>
>> "Umesh Thakur" <UmeshThakur@xxxxxx> rakstīja ziņojum�
>> "news:6EE60933-F1AF-49D2-916A-E20BD8BFA979@xxxxxx"...

>> > I am sorry, i think I didn't explain it completely.
>> >
>> > I am enumerating thru rows of a SQL server table, and I like to print
>> > it
>> > in
>> > table format in the same way as output of certain cmdlets are shown in
>> > tabular format (eg. get-process).
>> >
>> > I tried reading a row, stroring its column values separated by columns
>> > in
>> > a
>> > string variable and then output it using write-host command and |
>> > format-table piping.. however, its doesn't show in tabular format.
>> >
>> >
>> > --
>> > Umesh
>> >
>> > "Old programmers never die. They just terminate and stay resident."
>> >
>> >
>> >
>> > "RichS [MVP]" wrote:
>> >
>> >> Check out Format-Table
>> >>
>> >> get-help format-table -full
>> >>
>> >> will give you the details
>> >>
>> >> --
>> >> Richard Siddaway
>> >> All scripts are supplied "as is" and with no warranty
>> >> PowerShell MVP
>> >> Blog: http://richardsiddaway.spaces.live.com/
>> >> PowerShell User Group: http://www.get-psuguk.org.uk
>> >>
>> >>
>> >> "Umesh Thakur" wrote:
>> >>
>> >> > Hi
>> >> >
>> >> > I see output of many commands coming in table format, and they look
>> >> > nice.
>> >> >
>> >> > is there any cmdlet that I can use to format my script's output and
>> >> > print
>> >> > them in such table format way? in my scenario, my script is reading
>> >> > a
>> >> > database and i like to display the data in table format.
>> >> >
>> >> > thanks,
>> >> > umesh
>> >> > --
>> >> > Umesh
>> >> >
>> >> > "Old programmers never die. They just terminate and stay resident."
>> >> >

>>

> when I print each row, it comes with column headers as well

yes, this occurs when you use Format-Table within loop. You should move this
command outside of loop. Try this:

$(do
{
$out = new-object psobject #create new psobject for formatted output
for($c = 0;$c -lt $fieldsCount; $c++)
{
$out | add-member noteproperty $objRecordset.Fields.Item($c).name
$objRecordset.Fields.Item($c).value
}
$objRecordset.MoveNext()
$out
} until ($objRecordset.EOF -eq $True)) | format-table
--
WBR, Vadims Podans
MVP: PowerShell
PowerShell blog - www.sysadmins.lv

"Umesh Thakur" <UmeshThakur@xxxxxx> rakstīja ziņojum�
"newsCE6B995-91DF-42F9-8A63-8F6D10BE4098@xxxxxx"...

> thanks for your help Vadims.
> I tried to use custom psobject with an example at following site:
>
> http://blog.sapien.com/index.php/200...tputs-a-table/
>
> however, when I print each row, it comes with column headers as well.
> column
> headers shold come only once, and rows should follow. perhaps, I am
> missing
> something or doing something wrong.. you can put me in right direction.
>
> Here is a script that I am writing:
>
> $adOpenStatic = 3
> $adLockOptimistic = 3
>
> $objConnection = New-Object -comobject ADODB.Connection
> $objRecordset = New-Object -comobject ADODB.Recordset
> $dbPath = "d:\test.accdb"
> $sql = Read-Host "Enter SQL query to run against the database:"
>
> #This connection string is used for Access 2007 database. for access 2003
> DB, use " Microsoft.Jet.OLEDB.4.0"
> $objConnection.Open("Provider = Microsoft.ACE.OLEDB.12.0; Data Source =
> $dbPath")
>
> $objRecordset.Open($sql, $objConnection,$adOpenStatic,$adLockOptimistic)
>
>
> Write-Host "Executing following query and displaying result:"
> Write-Host $sql
>
> #get all fields of the result
> $fieldsCount = $objRecordset.Fields.count
> write-host "Total Fields are:" $fieldsCount
>
> $out = new-object psobject #create new psobject for formatted output
> for($c = 0;$c -lt $fieldsCount; $c++)
> {
> $out | add-member noteproperty $objRecordset.Fields.Item($c).name
> $objRecordset.Fields.Item($c).name
> }
>
> $objRecordset.MoveFirst()
> do
> {
> $out = new-object psobject #create new psobject for formatted output
> for($c = 0;$c -lt $fieldsCount; $c++)
> {
> $out | add-member noteproperty $objRecordset.Fields.Item($c).name
> $objRecordset.Fields.Item($c).value
> }
> write-output $out | ft
> $objRecordset.MoveNext()
> } until ($objRecordset.EOF -eq $True)
>
>
> #close recordset and connection object
> $objRecordset.Close()
> $objConnection.Close()
>
> --
> Umesh
>
> "Old programmers never die. They just terminate and stay resident."
>
>
>
> "Vadims Podans [MVP]" wrote:
>

>> in your script you can create custom psobject with neccessary properties
>> and
>> fill them with your data from SQL and stream this object to output using
>> Format-Table formatting.
>> --
>> WBR, Vadims Podans
>> MVP: PowerShell
>> PowerShell blog - www.sysadmins.lv
>>
>> "Umesh Thakur" <UmeshThakur@xxxxxx> rakstīja ziņojum�
>> "news:6EE60933-F1AF-49D2-916A-E20BD8BFA979@xxxxxx"...

>> > I am sorry, i think I didn't explain it completely.
>> >
>> > I am enumerating thru rows of a SQL server table, and I like to print
>> > it
>> > in
>> > table format in the same way as output of certain cmdlets are shown in
>> > tabular format (eg. get-process).
>> >
>> > I tried reading a row, stroring its column values separated by columns
>> > in
>> > a
>> > string variable and then output it using write-host command and |
>> > format-table piping.. however, its doesn't show in tabular format.
>> >
>> >
>> > --
>> > Umesh
>> >
>> > "Old programmers never die. They just terminate and stay resident."
>> >
>> >
>> >
>> > "RichS [MVP]" wrote:
>> >
>> >> Check out Format-Table
>> >>
>> >> get-help format-table -full
>> >>
>> >> will give you the details
>> >>
>> >> --
>> >> Richard Siddaway
>> >> All scripts are supplied "as is" and with no warranty
>> >> PowerShell MVP
>> >> Blog: http://richardsiddaway.spaces.live.com/
>> >> PowerShell User Group: http://www.get-psuguk.org.uk
>> >>
>> >>
>> >> "Umesh Thakur" wrote:
>> >>
>> >> > Hi
>> >> >
>> >> > I see output of many commands coming in table format, and they look
>> >> > nice.
>> >> >
>> >> > is there any cmdlet that I can use to format my script's output and
>> >> > print
>> >> > them in such table format way? in my scenario, my script is reading
>> >> > a
>> >> > database and i like to display the data in table format.
>> >> >
>> >> > thanks,
>> >> > umesh
>> >> > --
>> >> > Umesh
>> >> >
>> >> > "Old programmers never die. They just terminate and stay resident."
>> >> >

>>