Extract JSON from CSV file and save to separate CSV file in PowerShell

Question

I have data in a CSV. I would like to save the columns "Steps" that is a JSON string to a separate CSV file. Also the "Attachments" key is array of strings. I have not been successful. I've include the content of the CSV file

Summary,Description,Steps,PreCondition1,PreCondition2,Issue key,Issue id
Summary Data,Description data,"[{""id"":  5495,""index"":  1,""step"":  ""Data steps"",""data"":  ""Data 1"",""result"":  ""Data results 1"",""attachments"":  [""Test""]},{""id"":  5496,""index"":  2,""step"":  ""Data steps 2"",""data"":  ""Data 2"",""result"":  ""Data results 2"",""attachments"": []},{""id"":  5497,""index"":  3,""step"":  ""Data steps 3"",""data"":  ""Data 3"",""result"": ""Data results 3"",""attachments"": []},{""id"":  5498,""index"":  4,""step"":  ""Data steps 4"",""data"":  ""Data 4"",""result"":  ""Data results 4"",""attachments"": []},{""id"":  5499,""index"":  5,""step"":  ""Data steps 5"",""data"":  ""Data 5"",""result"":  ""Data results "",""attachments"": []}]",Data pre-condition1,Data pre-condition2,XXXX-1,17607

Expected results:

id,index,step,data, result,attachments

5495,1,Data steps,Data 1,Data results 1,Test

Would you like 1 CSV file per input row, or do you want to merge the JSON from the entire input CSV into a single output file? — Mathias R. Jessen
– Mathias R. Jessen, Commented Jan 12, 2022 at 15:57

Theo · Accepted Answer · 2022-01-13 13:16:36Z

1

Not quite sure if this is what you seek, but try

Import-Csv -Path 'X:\yourFile.csv' | ForEach-Object {
    $_.Steps | ConvertFrom-Json | ForEach-Object {
               # re-create the 'attachments' column by joining the array values with a semi-colon
               $_ | Select-Object *, @{Name = 'attachments'; Expression = {$_.attachments -join ';'}} -ExcludeProperty attachments |
               Export-Csv -Path 'X:\steps.csv' -NoTypeInformation -Force -Append
  }    
}

^{Note: you may need to ensure the output file 'X:\steps.csv' does not exist before (re) running this code because of the -Append switch.}

edited Jan 13, 2022 at 13:16

answered Jan 12, 2022 at 16:03

Theo

61.9k8 gold badges28 silver badges48 bronze badges

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

2 Comments

Anastasios Over a year ago

Did not work. I added some information in the question about the expected results. I apologize for not providing information before.

Theo Over a year ago

@Anastasios thanks for the edit so others may benefit. However.. I believe you still would need switch -Append, otherwise the file will be overwritten every time.

Collectives™ on Stack Overflow

Extract JSON from CSV file and save to separate CSV file in PowerShell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related