Search code examples
jqueryjsonpowershellpipeexport-to-csv

Get all the values for a specific field from a PowerShell text/json output file

I have a huge text file output from a query in Powershell. Part of it looks like this. Now, I am interested in printing out/selecting only the numerical values of a specific field eg. costInBillingCurrency. I just want "costInBillingCurrency":0.003038455451812 out of the entire large text file.


"costInBillingCurrency":0.003038455451812,"costInPricingCurrency":0.0031903782244026,"costCenter":"","date":"2022-12- 00:00:00Z","exchangeRate":"0.9617696561673479201731185381101226","exchangeRateDate":"2022-12-01T00:00:00Z"

I want the output to resemble something like this so I can input it into a CSV file. I am having trouble forming the regex and finding the right commands to use in the Powershell terminal to form my script.

header 1 Values
costInBillingCurrency 0.003038455451812
costInBillingCurrency 6.003038455451812
Solution

  • As per my comment, this is not complicated.

    One way of doing this is:

    Clear-Host
'"costInBillingCurrency":0.003038455451812,"costInPricingCurrency":0.0031903782244026,"costCenter":"","date":"2022-12- 00:00:00Z","exchangeRate":"0.9617696561673479201731185381101226","exchangeRateDate":"2022-12-01T00:00:00Z"' -replace ',.*' | 
ConvertFrom-Csv -Delimiter ':' -Header header1, Values
# Results
<#
header1               Values           
-------               ------           
costInBillingCurrency 0.003038455451812
#>

    Or this way:

    Clear-Host
('"costInBillingCurrency":0.003038455451812,"costInPricingCurrency":0.0031903782244026,"costCenter":"","date":"2022-12- 00:00:00Z","exchangeRate":"0.9617696561673479201731185381101226","exchangeRateDate":"2022-12-01T00:00:00Z"' -split ',', 2)[0] | 
ConvertFrom-Csv -Delimiter ':' -Header header1, Values
# Results
<#
header1               Values           
-------               ------           
costInBillingCurrency 0.003038455451812
#>

    Or if you really want RegEx, this.

    Clear-Host 
([RegEx]::Matches('"costInBillingCurrency":0.003038455451812,"costInPricingCurrency":0.0031903782244026,"costCenter":"","date":"2022-12- 00:00:00Z","exchangeRate":"0.9617696561673479201731185381101226","exchangeRateDate":"2022-12-01T00:00:00Z"', '(?m)^[^\r\n,]+')).Value| 
ConvertFrom-Csv -Delimiter ':' -Header header1, Values
# Results
<#
header1               Values           
-------               ------           
costInBillingCurrency 0.003038455451812
#>