How to convert DataTable to JSON using ConvertTo-json in Powershell v3

user_3120253 0
Viewed 9040

I have a DataTable $dt with same data, I would like to pipe the data to JSON using the cmdlet ConvertTo-JSON in Powershell v3

$ds.Tables["mytable"] | ConvertTo-Json

The result is all the properties of the DataTable are returned, but I only need the records in the data table.

I am wondering if there is a way to do this without looking through each column/ row and adding then into a custom object..etc.

This is what I get when I run the above line;

[
    {
        "RowError":  "",
       "RowState":  2,
        "Table":  {
                  "CaseSensitive":  false,
                  "IsInitialized":  true,
                  "RemotingFormat":  0,
                  "ChildRelations":  "",
                  "Columns":  "Id Name IsActive",
                  "Constraints":  "",
                  "DataSet":  "System.Data.DataSet",
                  "DefaultView":  "System.Data.DataRowView System.Data.DataRowView",
                  "DisplayExpression":  "",
                  "ExtendedProperties":  "System.D.......

Thanks

Yasir

http://www.sqlist.co.uk

powershell json
2 Answers

You can also exclude properties with Select-Object, removing the noise. 

$dt | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors | ConvertTo-Json
user_107294690
Related