☰

无法将 JSON 转换为 CSV 文件

tag-icon • tag-icon batch csv json jq
无法将 JSON 转换为 CSV 文件

我需要从 JSON 文件中提取特定的防病毒产品扫描结果，并以 csv 格式列出元素以供进一步处理。JSON 文件示例如下：

文件的内容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"
    }
  }
}

JSON 文件包含按产品组织的防病毒扫描结果。需要以 csv 格式检索与产品“Elastic”相关的结果，以便进行进一步处理，如下所示：

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

根据我的研究，可以通过 jq 命令提取结果，如下所示：

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

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

尝试使用 map 函数提取 csv 格式的所需结果，但失败了。使用的参考链接是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"

谢谢提供解决方案和评论。

答案1

您需要一个特定的 bat 来提取行并组成此输出布局，请尝试：

@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"

对于多个.json文件，尝试

@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"

相关内容