Transform a NoteProperty and computed column object into a string

Question

I want to import file infos and MD5 Hash in SQL Server table using Write-ObjectToSQL Module. I'm trying to do this in one line if possible. Here's what I 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 what I get in SQL Server:

I don't get 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 will import only NoteProperties that are not object.

Now, if we look at what I get before the Write-ObjectToSQL. I removed the Time columns to get a 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 is there a way to transform the MD5 column to have only MD5 result and not the @{Hash= and the closing } AND to transform the Directory column in a string ?

Answer 1

Converting the directory name to a string instead of System.IO.DirectoryInfo, replace the Select Directory with a custom Directory:

@{n='Directory'; e={$_.Directory -as [String]}}

When you get the file hash, expand the property:

Get-FileHash $_ -Algorithm MD5 | select -ExpandProperty Hash

That will return just the actual hash, not an MD5 object containing a HASH property.

Adding the 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 you some 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