Vista - Trying to use the output as a table

I am not having alot of luck creating a table type object from the output of
a file.

I can force it into a table and export it to a file as csv and then import
but i am not sure what things work. I am trying my first real world usage of
psh to manage my tape backup system. Arcserve has a pretty decent set of
command line utilities that until now i have automated through vbscript. But
there are somethings i cannot do easily in vbscript that are native to psh
and i am making my first attempt.

(Begincode)
..\ca_devmgr -cahost natl-itb -mediainfo 4 0 0 | ft |Out-File c:\devmgr.txt
cls
Import-Csv c:\devmgr.txt | sort ID
(endcode)
I get a table but i cannot get it to sort the ID column. I get the list in
some random order.

On Wed, 5 Jul 2006 05:09:02 -0700, Daren Daigle
<DarenDaigle@discussions.microsoft.com> wrote:

>I am not having alot of luck creating a table type object from the output of
>a file.
>
>I can force it into a table and export it to a file as csv and then import
>but i am not sure what things work. I am trying my first real world usage of
>psh to manage my tape backup system. Arcserve has a pretty decent set of
>command line utilities that until now i have automated through vbscript. But
>there are somethings i cannot do easily in vbscript that are native to psh
>and i am making my first attempt.
>
>(Begincode)
>.\ca_devmgr -cahost natl-itb -mediainfo 4 0 0 | ft |Out-File c:\devmgr.txt
>cls
>Import-Csv c:\devmgr.txt | sort ID
>(endcode)
>I get a table but i cannot get it to sort the ID column. I get the list in
>some random order.

Daren,

Can you post the first few lines of content in you devmgr.txt file so
that we can see what the import-csv cmdlet is actually seeing?

I am suspicious of you having a format-table in the middle of your
first line. I suspect it shouldn't be there. But since I am not
familiar with the software you're using I can't say more than that.
Suffice to say I would be surprised if a format-table were the correct
approach there.

Thanks

Andrew Watt MVP

(devmgr.txt)
SLOT NAME ID SEQ. SERNO LOADED WP WORM
1 B1-WKIN-27 c173 1 HQA216S NO NO NO
2 B1-WKFL-27 8a79 1 HQA199S NO NO NO
3 <<Blank Media>> 0000 0 NO NO NO
4 <<Blank Media>> 0000 0 NO NO NO
5 <<Blank Media>> 0000 0 NO NO NO
6 <<Blank Media>> 0000 0 NO NO NO
7 <<Blank Media>> 0000 0 NO NO NO
8 <<Blank Media>> 0000 0 NO NO NO
9 <<Blank Media>> 0000 0 NO NO NO
10 <<Slot Is Empty>> 0000 0 -- -- --
11 <<Slot Is Empty>> 0000 0 -- -- --
(Devmgr)

"Andrew Watt [MVP]" wrote:

> On Wed, 5 Jul 2006 05:09:02 -0700, Daren Daigle
> <DarenDaigle@discussions.microsoft.com> wrote:
>
> >I am not having alot of luck creating a table type object from the output of
> >a file.
> >
> >I can force it into a table and export it to a file as csv and then import
> >but i am not sure what things work. I am trying my first real world usage of
> >psh to manage my tape backup system. Arcserve has a pretty decent set of
> >command line utilities that until now i have automated through vbscript. But
> >there are somethings i cannot do easily in vbscript that are native to psh
> >and i am making my first attempt.
> >
> >(Begincode)
> >.\ca_devmgr -cahost natl-itb -mediainfo 4 0 0 | ft |Out-File c:\devmgr.txt
> >cls
> >Import-Csv c:\devmgr.txt | sort ID
> >(endcode)
> >I get a table but i cannot get it to sort the ID column. I get the list in
> >some random order.
>
> Daren,
>
> Can you post the first few lines of content in you devmgr.txt file so
> that we can see what the import-csv cmdlet is actually seeing?
>
> I am suspicious of you having a format-table in the middle of your
> first line. I suspect it shouldn't be there. But since I am not
> familiar with the software you're using I can't say more than that.
> Suffice to say I would be surprised if a format-table were the correct
> approach there.
>
> Thanks
>
> Andrew Watt MVP
>

Daren,

Assuming that you displayed the sample lines using the get-content
cmdlet then you don't appear to have a comma-separated file. If that's
the case then it explains why import-csv is struggling.

Andrew Watt MVP

On Wed, 5 Jul 2006 06:21:02 -0700, Daren Daigle
<DarenDaigle@discussions.microsoft.com> wrote:

>(devmgr.txt)
>SLOT NAME ID SEQ. SERNO LOADED WP WORM
>1 B1-WKIN-27 c173 1 HQA216S NO NO NO
>2 B1-WKFL-27 8a79 1 HQA199S NO NO NO
>3 <<Blank Media>> 0000 0 NO NO NO
>4 <<Blank Media>> 0000 0 NO NO NO
>5 <<Blank Media>> 0000 0 NO NO NO
>6 <<Blank Media>> 0000 0 NO NO NO
>7 <<Blank Media>> 0000 0 NO NO NO
>8 <<Blank Media>> 0000 0 NO NO NO
>9 <<Blank Media>> 0000 0 NO NO NO
>10 <<Slot Is Empty>> 0000 0 -- -- --
>11 <<Slot Is Empty>> 0000 0 -- -- --
>(Devmgr)
>
>"Andrew Watt [MVP]" wrote:
>
>> On Wed, 5 Jul 2006 05:09:02 -0700, Daren Daigle
>> <DarenDaigle@discussions.microsoft.com> wrote:
>>
>> >I am not having alot of luck creating a table type object from the output of
>> >a file.
>> >
>> >I can force it into a table and export it to a file as csv and then import
>> >but i am not sure what things work. I am trying my first real world usage of
>> >psh to manage my tape backup system. Arcserve has a pretty decent set of
>> >command line utilities that until now i have automated through vbscript. But
>> >there are somethings i cannot do easily in vbscript that are native to psh
>> >and i am making my first attempt.
>> >
>> >(Begincode)
>> >.\ca_devmgr -cahost natl-itb -mediainfo 4 0 0 | ft |Out-File c:\devmgr.txt
>> >cls
>> >Import-Csv c:\devmgr.txt | sort ID
>> >(endcode)
>> >I get a table but i cannot get it to sort the ID column. I get the list in
>> >some random order.
>>
>> Daren,
>>
>> Can you post the first few lines of content in you devmgr.txt file so
>> that we can see what the import-csv cmdlet is actually seeing?
>>
>> I am suspicious of you having a format-table in the middle of your
>> first line. I suspect it shouldn't be there. But since I am not
>> familiar with the software you're using I can't say more than that.
>> Suffice to say I would be surprised if a format-table were the correct
>> approach there.
>>
>> Thanks
>>
>> Andrew Watt MVP

get-content? you can see how i got my text. My whole idea is that the
program gives me a list of slots in my tape library and then the various
columns you see. Now i just want to start parsing through this in a way i
can get access to the various columns and rows.

All i did is pipe it out to FT then out to a file. was there a better way
to export the information?

how do you massage the data to create a CSV from this information. FT
appears to recognize the layout somwhat and attempt to parse it but it does
not work.

"Andrew Watt [MVP]" wrote:

> Daren,
>
> Assuming that you displayed the sample lines using the get-content
> cmdlet then you don't appear to have a comma-separated file. If that's
> the case then it explains why import-csv is struggling.
>
> Andrew Watt MVP
>
> On Wed, 5 Jul 2006 06:21:02 -0700, Daren Daigle
> <DarenDaigle@discussions.microsoft.com> wrote:
>
> >(devmgr.txt)
> >SLOT NAME ID SEQ. SERNO LOADED WP WORM
> >1 B1-WKIN-27 c173 1 HQA216S NO NO NO
> >2 B1-WKFL-27 8a79 1 HQA199S NO NO NO
> >3 <<Blank Media>> 0000 0 NO NO NO
> >4 <<Blank Media>> 0000 0 NO NO NO
> >5 <<Blank Media>> 0000 0 NO NO NO
> >6 <<Blank Media>> 0000 0 NO NO NO
> >7 <<Blank Media>> 0000 0 NO NO NO
> >8 <<Blank Media>> 0000 0 NO NO NO
> >9 <<Blank Media>> 0000 0 NO NO NO
> >10 <<Slot Is Empty>> 0000 0 -- -- --
> >11 <<Slot Is Empty>> 0000 0 -- -- --
> >(Devmgr)
> >
> >"Andrew Watt [MVP]" wrote:
> >
> >> On Wed, 5 Jul 2006 05:09:02 -0700, Daren Daigle
> >> <DarenDaigle@discussions.microsoft.com> wrote:
> >>
> >> >I am not having alot of luck creating a table type object from the output of
> >> >a file.
> >> >
> >> >I can force it into a table and export it to a file as csv and then import
> >> >but i am not sure what things work. I am trying my first real world usage of
> >> >psh to manage my tape backup system. Arcserve has a pretty decent set of
> >> >command line utilities that until now i have automated through vbscript. But
> >> >there are somethings i cannot do easily in vbscript that are native to psh
> >> >and i am making my first attempt.
> >> >
> >> >(Begincode)
> >> >.\ca_devmgr -cahost natl-itb -mediainfo 4 0 0 | ft |Out-File c:\devmgr.txt
> >> >cls
> >> >Import-Csv c:\devmgr.txt | sort ID
> >> >(endcode)
> >> >I get a table but i cannot get it to sort the ID column. I get the list in
> >> >some random order.
> >>
> >> Daren,
> >>
> >> Can you post the first few lines of content in you devmgr.txt file so
> >> that we can see what the import-csv cmdlet is actually seeing?
> >>
> >> I am suspicious of you having a format-table in the middle of your
> >> first line. I suspect it shouldn't be there. But since I am not
> >> familiar with the software you're using I can't say more than that.
> >> Suffice to say I would be surprised if a format-table were the correct
> >> approach there.
> >>
> >> Thanks
> >>
> >> Andrew Watt MVP
>

As your file is Fixed Length this is a bit tricky,but you can use ADO to
do this, as I already did the functions in my CSV series I came up with
this :

Going from my CSV examples here :

http://mow001.blogspot.com/2006/03/w...-part-one.html
http://mow001.blogspot.com/2006/03/w...-part-two.html

# I modified the connect-Csv (FMT=FixedLength)

function connect-txt {
Param ($Path = ".")
$ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=$path;Extended properties='text;HDR=Yes;FMT=FixedLength'"
$Conn = new-object System.Data.OleDb.OleDbConnection($connString)
$conn.open()
$Tables =
$Conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)

$Tables | select TABLE_NAME
}

# this did stay the same

function get-DataTable {
Param ($name )
$cmd = new-object System.Data.OleDb.OleDbCommand("Select * from
[$name]",$Conn)
$da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
$dt = new-object System.Data.dataTable
[void]$da.fill($dt)
$dt.TableName = $name.split("#")[0]
return $dt
}

Now for a Fixed Length file you need an SCHEMA.INI file in the same
directory :

SCHEMA.INI

[devmgr.txt]
Format=FixedLength

Col1=SLOT Text Width 9
Col2=NAME Text Width 24

and the rest of the Columns

now after you past in 2 former functions you can do this :

# connect to current directory and load in global Scope

PoSH>. connect-txt

TABLE_NAME
----------
devmgr#txt

# and get a datatable from it

PoSH>get-datatable devmgr#txt

SLOT NAME
---- ----
1
B1-WKIN-27
2
B1-WKFL-27
3
<<Blank Media>>
4
<<Blank Media>>
5
<<Blank Media>>
6
<<Blank Media>>

# you can work with dataset :

(get-datatable devmgr#txt).get_Rows() | select name

# or you now can export it to CSV

PoSH>(get-datatable devmgr#txt).get_Rows() | export-csv -not devmgr.csv
PoSH>import-csv devmgr.csv

SLOT NAME
---- ----
1
B1-WKIN-27
2
B1-WKFL-27
3
<<Blank Media>>

# and get it back as an object

PoSH>import-csv devmgr.csv | select name

NAME
----
B1-WKIN-27
B1-WKFL-27
<<Blank Media>>

Hope this helps,
I will clean this up and make a blogentry about it also.

Greetings /\/\o\/\/

Daren Daigle wrote:
> get-content? you can see how i got my text. My whole idea is that the
> program gives me a list of slots in my tape library and then the various
> columns you see. Now i just want to start parsing through this in a way i
> can get access to the various columns and rows.
>
> All i did is pipe it out to FT then out to a file. was there a better way
> to export the information?
>
> how do you massage the data to create a CSV from this information. FT
> appears to recognize the layout somwhat and attempt to parse it but it does
> not work.
>
> "Andrew Watt [MVP]" wrote:
>
>> Daren,
>>
>> Assuming that you displayed the sample lines using the get-content
>> cmdlet then you don't appear to have a comma-separated file. If that's
>> the case then it explains why import-csv is struggling.
>>
>> Andrew Watt MVP
>>
>> On Wed, 5 Jul 2006 06:21:02 -0700, Daren Daigle
>> <DarenDaigle@discussions.microsoft.com> wrote:
>>
>>> (devmgr.txt)
>>> SLOT NAME ID SEQ. SERNO LOADED WP WORM
>>> 1 B1-WKIN-27 c173 1 HQA216S NO NO NO
>>> 2 B1-WKFL-27 8a79 1 HQA199S NO NO NO
>>> 3 <<Blank Media>> 0000 0 NO NO NO
>>> 4 <<Blank Media>> 0000 0 NO NO NO
>>> 5 <<Blank Media>> 0000 0 NO NO NO
>>> 6 <<Blank Media>> 0000 0 NO NO NO
>>> 7 <<Blank Media>> 0000 0 NO NO NO
>>> 8 <<Blank Media>> 0000 0 NO NO NO
>>> 9 <<Blank Media>> 0000 0 NO NO NO
>>> 10 <<Slot Is Empty>> 0000 0 -- -- --
>>> 11 <<Slot Is Empty>> 0000 0 -- -- --
>>> (Devmgr)
>>>
>>> "Andrew Watt [MVP]" wrote:
>>>
>>>> On Wed, 5 Jul 2006 05:09:02 -0700, Daren Daigle
>>>> <DarenDaigle@discussions.microsoft.com> wrote:
>>>>
>>>>> I am not having alot of luck creating a table type object from the output of
>>>>> a file.
>>>>>
>>>>> I can force it into a table and export it to a file as csv and then import
>>>>> but i am not sure what things work. I am trying my first real world usage of
>>>>> psh to manage my tape backup system. Arcserve has a pretty decent set of
>>>>> command line utilities that until now i have automated through vbscript. But
>>>>> there are somethings i cannot do easily in vbscript that are native to psh
>>>>> and i am making my first attempt.
>>>>>
>>>>> (Begincode)
>>>>> .\ca_devmgr -cahost natl-itb -mediainfo 4 0 0 | ft |Out-File c:\devmgr.txt
>>>>> cls
>>>>> Import-Csv c:\devmgr.txt | sort ID
>>>>> (endcode)
>>>>> I get a table but i cannot get it to sort the ID column. I get the list in
>>>>> some random order.
>>>> Daren,
>>>>
>>>> Can you post the first few lines of content in you devmgr.txt file so
>>>> that we can see what the import-csv cmdlet is actually seeing?
>>>>
>>>> I am suspicious of you having a format-table in the middle of your
>>>> first line. I suspect it shouldn't be there. But since I am not
>>>> familiar with the software you're using I can't say more than that.
>>>> Suffice to say I would be surprised if a format-table were the correct
>>>> approach there.
>>>>
>>>> Thanks
>>>>
>>>> Andrew Watt MVP

Wow (not Mow.. ), for a system that is intended to parse data like no
other shell, this seems to be a lot of work to get add data that is coming
out formated in fixed width format.

"/\\/\\o\\/\\/" wrote:

> As your file is Fixed Length this is a bit tricky,but you can use ADO to
> do this, as I already did the functions in my CSV series I came up with
> this :
>
> Going from my CSV examples here :
>
> http://mow001.blogspot.com/2006/03/w...-part-one.html
> http://mow001.blogspot.com/2006/03/w...-part-two.html
>
> # I modified the connect-Csv (FMT=FixedLength)
>
> function connect-txt {
> Param ($Path = ".")
> $ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=$path;Extended properties='text;HDR=Yes;FMT=FixedLength'"
> $Conn = new-object System.Data.OleDb.OleDbConnection($connString)
> $conn.open()
> $Tables =
> $Conn.GetOleDbSchemaTable([System.Data.OleDb.OleDbSchemaGuid]::tables,$null)
>
> $Tables | select TABLE_NAME
> }
>
> # this did stay the same
>
> function get-DataTable {
> Param ($name )
> $cmd = new-object System.Data.OleDb.OleDbCommand("Select * from
> [$name]",$Conn)
> $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd)
> $dt = new-object System.Data.dataTable
> [void]$da.fill($dt)
> $dt.TableName = $name.split("#")[0]
> return $dt
> }
>
> Now for a Fixed Length file you need an SCHEMA.INI file in the same
> directory :
>
> SCHEMA.INI
>
> [devmgr.txt]
> Format=FixedLength
>
> Col1=SLOT Text Width 9
> Col2=NAME Text Width 24
>
> and the rest of the Columns
>
> now after you past in 2 former functions you can do this :
>
> # connect to current directory and load in global Scope
>
> PoSH>. connect-txt
>
> TABLE_NAME
> ----------
> devmgr#txt
>
> # and get a datatable from it
>
> PoSH>get-datatable devmgr#txt
>
> SLOT NAME
> ---- ----
> 1
> B1-WKIN-27
> 2
> B1-WKFL-27
> 3
> <<Blank Media>>
> 4
> <<Blank Media>>
> 5
> <<Blank Media>>
> 6
> <<Blank Media>>
>
> # you can work with dataset :
>
> (get-datatable devmgr#txt).get_Rows() | select name
>
> # or you now can export it to CSV
>
> PoSH>(get-datatable devmgr#txt).get_Rows() | export-csv -not devmgr.csv
> PoSH>import-csv devmgr.csv
>
> SLOT NAME
> ---- ----
> 1
> B1-WKIN-27
> 2
> B1-WKFL-27
> 3
> <<Blank Media>>
>
> # and get it back as an object
>
> PoSH>import-csv devmgr.csv | select name
>
> NAME
> ----
> B1-WKIN-27
> B1-WKFL-27
> <<Blank Media>>
>
> Hope this helps,
> I will clean this up and make a blogentry about it also.
>
> Greetings /\/\o\/\/
>
> Daren Daigle wrote:
> > get-content? you can see how i got my text. My whole idea is that the
> > program gives me a list of slots in my tape library and then the various
> > columns you see. Now i just want to start parsing through this in a way i
> > can get access to the various columns and rows.
> >
> > All i did is pipe it out to FT then out to a file. was there a better way
> > to export the information?
> >
> > how do you massage the data to create a CSV from this information. FT
> > appears to recognize the layout somwhat and attempt to parse it but it does
> > not work.
> >
> > "Andrew Watt [MVP]" wrote:
> >
> >> Daren,
> >>
> >> Assuming that you displayed the sample lines using the get-content
> >> cmdlet then you don't appear to have a comma-separated file. If that's
> >> the case then it explains why import-csv is struggling.
> >>
> >> Andrew Watt MVP
> >>
> >> On Wed, 5 Jul 2006 06:21:02 -0700, Daren Daigle
> >> <DarenDaigle@discussions.microsoft.com> wrote:
> >>
> >>> (devmgr.txt)
> >>> SLOT NAME ID SEQ. SERNO LOADED WP WORM
> >>> 1 B1-WKIN-27 c173 1 HQA216S NO NO NO
> >>> 2 B1-WKFL-27 8a79 1 HQA199S NO NO NO
> >>> 3 <<Blank Media>> 0000 0 NO NO NO
> >>> 4 <<Blank Media>> 0000 0 NO NO NO
> >>> 5 <<Blank Media>> 0000 0 NO NO NO
> >>> 6 <<Blank Media>> 0000 0 NO NO NO
> >>> 7 <<Blank Media>> 0000 0 NO NO NO
> >>> 8 <<Blank Media>> 0000 0 NO NO NO
> >>> 9 <<Blank Media>> 0000 0 NO NO NO
> >>> 10 <<Slot Is Empty>> 0000 0 -- -- --
> >>> 11 <<Slot Is Empty>> 0000 0 -- -- --
> >>> (Devmgr)
> >>>
> >>> "Andrew Watt [MVP]" wrote:
> >>>
> >>>> On Wed, 5 Jul 2006 05:09:02 -0700, Daren Daigle
> >>>> <DarenDaigle@discussions.microsoft.com> wrote:
> >>>>
> >>>>> I am not having alot of luck creating a table type object from the output of
> >>>>> a file.
> >>>>>
> >>>>> I can force it into a table and export it to a file as csv and then import
> >>>>> but i am not sure what things work. I am trying my first real world usage of
> >>>>> psh to manage my tape backup system. Arcserve has a pretty decent set of
> >>>>> command line utilities that until now i have automated through vbscript. But
> >>>>> there are somethings i cannot do easily in vbscript that are native to psh
> >>>>> and i am making my first attempt.
> >>>>>
> >>>>> (Begincode)
> >>>>> .\ca_devmgr -cahost natl-itb -mediainfo 4 0 0 | ft |Out-File c:\devmgr.txt
> >>>>> cls
> >>>>> Import-Csv c:\devmgr.txt | sort ID
> >>>>> (endcode)
> >>>>> I get a table but i cannot get it to sort the ID column. I get the list in
> >>>>> some random order.
> >>>> Daren,
> >>>>
> >>>> Can you post the first few lines of content in you devmgr.txt file so
> >>>> that we can see what the import-csv cmdlet is actually seeing?
> >>>>
> >>>> I am suspicious of you having a format-table in the middle of your
> >>>> first line. I suspect it shouldn't be there. But since I am not
> >>>> familiar with the software you're using I can't say more than that.
> >>>> Suffice to say I would be surprised if a format-table were the correct
> >>>> approach there.
> >>>>
> >>>> Thanks
> >>>>
> >>>> Andrew Watt MVP
>

"Daren Daigle" <DarenDaigle@discussions.microsoft.com> wrote in message
news:0F3446BF-BB20-49AF-96D5-F8C02CBCCD74@microsoft.com...
> Wow (not Mow.. ), for a system that is intended to parse data like no
> other shell, this seems to be a lot of work to get add data that is coming
> out formated in fixed width format.

I would say that PoSH is intended to pipe structured information (aka
objects) like no other shell. Frankly the text parsing capabilities in V1
are weak. Lee Holmes wrote a parse-TextObject script a while back. Take a
look at it and see if it helps:

http://www.leeholmes.com/blog/parset...Vengeance.aspx

It would be very helpful when dealing with legacy utilities to have
something like this built into the shell - hopefully in V2.

--
Keith

its only 2-4 lines and a one time making of a SCHEMA.INI entry.
(as I have all the connect and Get functions in my profile (MDB CSV SQL
XLS SQL TAB))

I do not really know better tools for handling this,
it does beat Excel's support for me ;-)

I use this a lot I only for new formats need to make a ini file,
and the relations possibilities in a DataSet I use also a lot.

Greetings /\/\o\/\/

Daren Daigle wrote:
> Wow (not Mow.. ), for a system that is intended to parse data like no
> other shell, this seems to be a lot of work to get add data that is coming
> out formated in fixed width format.
>
> "/\\/\\o\\/\\/" wrote:
>

OK, i am trying a different tact. But i cannot figure out how to get this
out to a file. It will produce a csv for me but i get errors relating to
pipelines not allowing null objects.

(begincode)
$resultat = \\natl-itb\d$\arcserve\ca_devmgr -cahost natl-itb -mediainfo 4 0 0
Set-Variable Slots
foreach ( $line in $resultat ) { if ( $line -notlike "Changer" ) {
write-host -OutVariable Slots
$line.substring(0,4)","$line.substring(7,25)| Out-File -Append C:\devmgr.csv }

} (endcode)