Failure to convert JSON to CSV file

Question

I need to extract a particular anti-virus product scan results from a JSON file and list the elements in csv format for further processing. JSON file sample as below:

Contents of a file scanresults.json

{
  "scans": {
    "Bkav": {
      "detected": true,
      "version": "1.3.0.9899",
      "result": "W32.AIDetect.malware2",
      "update": "20230417"
    },
    "Lionic": {
      "detected": true,
      "version": "7.5",
      "result": "Trojan.Win32.Generic.4!c",
      "update": "20230417"
    },
    "Elastic": {
      "detected": true,
      "version": "4.0.85",
      "result": "malicious (high confidence)",
      "update": "20230413"
    },
    "MicroWorld-eScan": {
      "detected": true,
      "version": "14.0.409.0",
      "result": "Trojan.Ransom.Cerber.1",
      "update": "20230417"
    }
  }
}

The JSON file contains Anti-Virus scan results organized by products. It is required to retrieve results related to the product "Elastic" only in csv format for further processing as below:

detected, version, result, update
true, "4.0.85", "malicious (high confidence)", "20230413"

Base on my research, able to extract the result by jq command as shown below:

jq-win64.exe ".scans.Elastic" scanresults.json

{
  "detected": true,
  "version": "4.0.85",
  "result": "malicious (high confidence)",
  "update": "20230413"
}

Tried but failed to use map function to extract the needed results in csv format. The reference link used is https://earthly.dev/blog/convert-to-from-json/.

$ cat simple.json| jq -r '(map(keys) | add | unique) as $cols | map(. as $row | $cols | map($row[.])) as $rows | $cols, $rows[] | @csv' 
"color","id","value"
"red",1,"#f00"
"green",2,"#0f0"
"blue",3,"#00f"

Thanks for provide solution and comments.

Why do you ask twice the [same question](https://superuser.com/q/1780506/763386)? — Toto, Apr 24 '23 at 10:21
My first question is marked as closed as the problem is not stated clearly. It is recommended to open a new one with both the clarified problem statement. — Sam Chan, Apr 24 '23 at 11:19
The advice does not mean you should ask the same question. By a different question it means an entirely different question. You should [edit] the original question and allowing it to be reopened. No; Deleting the original question doesn’t change the fact this question would be a duplicate of that question. — Ramhound, Apr 24 '23 at 12:41
Your edited version still doesn't show **your** attempt BTW, just a random irrelevant attempt from the guide. — Destroy666, Apr 24 '23 at 13:09
There are online JSON to CSV converters. What is the problem with using one? — DrMoishe Pippik, Apr 24 '23 at 17:24
@Pippik, thanks for your advice. The online converter is working fine. Since I need to process many json files, I am looking for command line solution to process them by batch. — Sam Chan, Apr 25 '23 at 01:01

Io-oI · Answer 1 · 2023-04-29T17:59:18.510

A specific bat to extract the lines and compose this output layout is what you need, try:

@echo off && setlocal enabledelayedexpansion

for /f skip^=4 %%e in ('echo;prompt $E^|cmd.exe
    ')do set "_$E=%%~e[1F%%~e[0J"

for /f usebackq^delims^=: %%G in (`findstr /ni elastic ^< .\scanresults.json
   `)do for /f tokens^=1*^delims^=^:^"^  %%i in ('more .\scanresults.json +%%~G /e
        ')do echo/%%i | find "}" >nul && goto %:^) || if not defined _str (
             set "_str=%%~j")else set _str=!_str:, ,=,! "%%~j
            )
%:^)
>.\Output.csv (
     echo/detected, version, result, update
     echo/!_str!
    ) & type .\Output.csv

endlocal & exit /b

.\Output.csv:

detected, version, result, update
true, "4.0.85", "malicious (high confidence)", "20230413"

for multiple .json files, try

@echo off && setlocal enabledelayedexpansion

for /f skip^=4 %%e in ('
     echo;prompt;$E^|%comSpec%'
    )do set "_$E=%%~e[1F%%~e[0J" 

cd /d "%~dp0" && >.\Output.csv =;(
     echo/detected, version, result, update
    );=
    
for /f delims^= %%i in ('where .:*.json'
    )do call %:^) "%%~fi" && set^ _str=1<nul

%:^)
if "%~1" == "" type .\Output.csv && endlocal && exit /b
for /f usebackq^delims^=: %%G in =;(`findstr /ni elastic ^< "%~1"`
    )do for /f tokens^=1*^delims^=^:^"^  %%i in =;(' more "%~1" +%%~G /e'
        )do echo/"%%~i" | find "}" >nul && =;( echo/!_str!>>.\Output.csv && exit /b 
            );= || if not defined _str (set "_str=%%~j")else set _str=!_str:, ,=,!"%%~j 
                );=

.\Output.csv:

detected, version, result, update 
true, "1.3.0.9899", "W32.AIDetect.malware2", "20230417"   
true, "4.0.85", "malicious (high confidence)", "20230413"
true, "14.0.409.0", "Trojan.Ransom.Cerber.1", "20230417"

Failure to convert JSON to CSV file

1 Answers1