powershell - Transform a NoteProperty and computed column object into a string -

i want import file infos , md5 hash in sql server table using write-objecttosql module. i'm trying in 1 line if possible. here's have:

gci -file|select directory,name,extension,length,creationtime,lastaccesstime,lastwritetime,@{name='md5'; expression={get-filehash $_ -algorithm md5|select hash}}|write-objecttosql -server abc -database pdoc -tablename files 

and here's in sql server:

i don't md5 column nor directory column. here's th get-member info:

ps c:\users\pollusb> gci -file|select directory,name,extension,length,@{name='md5'; expression={get-filehash $_ -algorithm md5|select hash}}|gm     typename: selected.system.io.fileinfo  name        membertype   definition ----        ----------   ---------- equals      method       bool equals(system.object obj) gethashcode method       int gethashcode() gettype     method       type gettype() tostring    method       string tostring() directory   noteproperty directoryinfo directory=c:\users\pollusb extension   noteproperty string extension=.pdoc length      noteproperty long length=65 md5         noteproperty selected.system.management.automation.pscustomobject md5=@{hash=c30ccc4447297d94fe10f54c18dbd5f9} name        noteproperty string name=345.pdoc 

so sql server import noteproperties not object.

now, if @ before write-objecttosql. removed time columns nicier result.

ps c:\users\pollusb> gci -file|select directory,name,extension,length,@{name='md5'; expression={get-filehash $_ -algorithm md5|select hash}} | ft -autosize  directory        name             extension length md5 ---------        ----             --------- ------ --- c:\users\pollusb 345.pdoc         .pdoc         65 @{hash=c30ccc4447297d94fe10f54c18dbd5f9} c:\users\pollusb deletedroute.txt .txt          51 @{hash=de585876a6249b9fdf176697f5d35ea4} c:\users\pollusb dir.txt          .txt       11815 @{hash=77f3ce7f713966d4f8e39d12e1d77947} c:\users\pollusb open.ps1         .ps1         116 @{hash=f5b0126d5b0a5040be34fdc257add7f4} c:\users\pollusb pdoc.bat         .bat          34 @{hash=e3630400a370f6d94ece198407fdb151} 

my question there way transform md5 column have md5 result , not @{hash= , closing } , transform directory column in string ?

converting directory name string instead of system.io.directoryinfo, replace select directory custom directory:

@{n='directory'; e={$_.directory -as [string]}} 

when file hash, expand property:

get-filehash $_ -algorithm md5 | select -expandproperty hash 

that return actual hash, not md5 object containing hash property.

adding new properties:

gci -file admin.bat | select @{n='directory'; e={$_.directory -as [string]}},    name,    extension,    length,    @{name='md5'; expression={get-filehash $_ -algorithm md5 | select -expandproperty hash} } | gm 

which gets string noteproperty's:

name        membertype   definition                                         ----        ----------   ----------                                         equals      method       bool equals(system.object obj)                     gethashcode method       int gethashcode()                                  gettype     method       type gettype()                                     tostring    method       string tostring()                                  directory   noteproperty system.string directory=d:\                        extension   noteproperty system.string extension=.bat                       length      noteproperty system.int64 length=2901                           md5         noteproperty system.string md5=876056eb083579dff07e85015d4c0272 name        noteproperty system.string name=admin.bat