Create a Specific JSON file from csv in PowerShell

Question

I have no experience with PowerShell and I was asked to create this script as a favor for a friend of mine. The script is supposed to read a csv file (These files have different columns except for time and host, which are common among all files), and output its content into a JSON file of the following format:

CSV file contains columns:

{
"time": 1437522387,
"host": "dataserver992.example.com",
"event": { 
    "message": "Something happened",
    "severity": "INFO",
    "source": "testapp"
    #...All columns except for time and host should be under "event"
    }
}

*The only guaranteed columns are time and host. All other column headers vary from file to file.

This is part of what I have so far:

$csvFile = Import-Csv $filePath


function jsonConverter($file)
{    
    #Currently not in use
    $eventString = $file| select * -ExcludeProperty time, host 


    $file | Foreach-Object {
        Write-Host '{'
        Write-Host '"host":"'$_.host'",'
        Write-Host '"time":"'$_.time'",'

        Write-Host '"event":{'

        #TODO: Put all other columns (key, values) under event - Except for 
        time and host

        Write-Host '}'
    }    
}

jsonConverter($csvFile)

Any ideas of how I could extract only the remaining columns, row by row, outputting its content to a key, value JSON format like the example above? Thank you!

score 4 · Accepted Answer · 2019-01-04 15:53:34Z

4

Provided your csv looks like this:

"host","message","time","severity","source"
"dataserver992.example.com","Something happened","1437522387","INFO","testapp"

this script:

$filepath = '.\input.csv'
$csvData = Import-Csv $filePath

$NewCsvData  = foreach($Row in $csvData){
   [PSCustomObject]@{
       time  =  $Row.time
       host  =  $Row.host
       event = ($Row| Select-Object -Property * -ExcludeProperty time,host)
   }
}

$NewCsvData | ConvertTo-Json

will output this Json:

{
    "time":  "1437522387",
    "host":  "dataserver992.example.com",
    "event":  {
                  "message":  "Something happened",
                  "severity":  "INFO",
                  "source":  "testapp"
              }
}

edited Jan 4, 2019 at 15:53

answered Jan 4, 2019 at 15:42

user6811411

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

2 Comments

user6811411 Over a year ago

I first didn't remember that the parameter -ExcludeProperty requires the parameter -Property to also be present.

Nicolas Dos Santos Over a year ago

Great solution! Thank you so much!

Gert Jan Kraaijeveld · Accepted Answer · 2019-01-04 15:22:28Z

0

If your powershell version is 3.0 or higher (it should):

Import-CSV $filepath | ConvertTo-JSON

Done!

answered Jan 4, 2019 at 15:22

Gert Jan Kraaijeveld

1,1878 silver badges12 bronze badges

2 Comments

Nicolas Dos Santos Over a year ago

Hey, that doesn't work because "event" is not actually a column in the csv file. "event" is like a key where I have to put all columns except for host and time. This is a customized JSON file. See example above.

Gert Jan Kraaijeveld Over a year ago

Just baked something like @LotPings. But slightly less distinguished. So use his code ;-)

Collectives™ on Stack Overflow

Create a Specific JSON file from csv in PowerShell

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related