How to convert string to integer with ConvertTo-Json?

Question

I am using PowerShell to convert a CSV file to a JSON format. The problem is the CSV wraps double quotes around the column values, even integers. How do I convert them to integers during the conversion process?

This is the script I am using to convert a directory of *.csv files to *.json:

Get-ChildItem .\*.csv | Foreach-Object {
  $basename = $_.BaseName
  import-csv $_ | ConvertTo-Json -Compress | Foreach {$_ -creplace '"NULL"','null'} | Out-File ".\$basename.json"
}

This is my source CSV:

"Id","Name","Active"
"1","Test 1","1"
"2","Test 2","0"
"3","Test 3","1"

And here is what it outputs:

[
{"Id":"1","Name":"Test 1","Active":"1"}
{"Id":"2","Name":"Test 2","Active":"0"}
{"Id":"3","Name":"Test 3","Active":"1"}
]

How do I make it output this instead?:

[
{"Id":1,"Name":"Test 1","Active":1}
{"Id":2,"Name":"Test 2","Active":0}
{"Id":3,"Name":"Test 3","Active":1}
]

Andy Arismendi · Accepted Answer · 2013-07-15 03:39:13Z

3

The reason that's happening is that Import-Csv creates an object with a string property for each column, so ConvertTo-Json is giving you quoted strings. You could use Select-Object between Import-Csv and ConvertTo-Json to recast the number properties as integers, but I think the simplest way to do it is to add a regex replace in your foreach block:

import-csv $_ | ConvertTo-Json -Compress | 
    Foreach {$_ -creplace '"NULL"','null' -replace ':"([0-9]+)"',':$1'} | 
        Out-File ".\$basename.json"

edited Jul 15, 2013 at 3:39

Andy Arismendi

53.1k17 gold badges113 silver badges130 bronze badges

answered Jul 15, 2013 at 2:59

Adi Inbar

12.4k13 gold badges60 silver badges71 bronze badges

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

Collectives™ on Stack Overflow

How to convert string to integer with ConvertTo-Json?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related