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

Question

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

I've used following solution: mac-blog.org.ua/powershell-invoke-sqlcmd-convertto-json — Bogdan Sahlean
– Bogdan Sahlean, Commented Jul 22, 2017 at 14:47

mjolinor · Accepted Answer · 2013-12-19 19:24:10Z

18

After playing with a sample datatable a bit, I ended up with this:

($ds.Tables["mytable"] | select $ds.Tables["mytable"].Columns.ColumnName ) | ConvertTo-Json

answered Dec 19, 2013 at 19:24

mjolinor

68.7k7 gold badges118 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Yasir Over a year ago

perfect, just an extra )

MsBugKiller Over a year ago

oh ya this is very helpful. I write my data to JSON files and it looks like this: $JSONFileNameFormat = "C:\thao\nVision\Data\test\Json\{0}.{1}.JSON" $JsonOutputFile = [string]::Format($JSONFileNameFormat, $($Row[0]),$($Row[1])) ($DataSet_Table.Tables[0] | select $DataSet_Table.Tables[0].Columns.ColumnName ) | ConvertTo-Json -Compress | Out-File $JsonOutputFile

Michael Erickson Over a year ago

I found the essential transform here was: $dt | select $dt.Columns.ColumnName, that is enough to get an array of PS objects with the necessary properties. From there you can iterate, or convert it all at once -- Great answer!

EdTheHorse · Accepted Answer · 2019-07-25 13:58:34Z

7

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

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

answered Jul 25, 2019 at 13:58

EdTheHorse

711 silver badge3 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related