如何解析网页中的多段JSON并提取为CSV格式？

前端JavaScript方案 #

你之前的问题在于没有把多段JSON转化为合法的JSON数组格式，直接解析会失败。正确的处理方式是给原始文本包裹外层的[]，将其转为合法的JSON数组，再进行解析和处理。

<!DOCTYPE html>
<html>
<body>
<h2>提取JSON并生成CSV</h2>
<div id="demo"></div>
<script>
// 原始多段JSON文本
const txt = '{"id":0,"name":"NAME","area":"Z","site":"https://www.a-site-here.com/en/index.html","logo":"https://www.another-site-here.com/wp-content/uploads/2022/02/image-GetFileAttachment1.png"},{"id":1,"name":"NAME2","area":"Y","site":"https://example.com","logo":"https://example.com/logo.png"}';

// 关键操作：给文本前后添加[]，转为合法的JSON数组
const validJson = `[${txt}]`;
const objArray = JSON.parse(validJson);

// 构建CSV内容（处理字段中的特殊字符，避免格式错误）
let csv = 'id,name,area,site,logo\n';
objArray.forEach(item => {
  // 替换字段中的双引号为两个双引号，符合CSV规范
  const formattedFields = [
    item.id,
    `"${item.name.replace(/"/g, '""')}"`,
    `"${item.area.replace(/"/g, '""')}"`,
    `"${item.site.replace(/"/g, '""')}"`,
    `"${item.logo.replace(/"/g, '""')}"`
  ];
  csv += formattedFields.join(',') + '\n';
});

// 展示结果
document.getElementById("demo").innerHTML = `<pre>${csv}</pre>`;
</script>
</body>
</html>

VBA方案 #

如果需要用VBA实现，需要先导入VBA-JSON库（可直接下载.bas文件导入到VBA工程），再按以下代码处理：

Sub ExtractJSONToCSV()
    Dim rawText As String
    Dim validJson As String
    Dim jsonArray As Object
    Dim jsonItem As Object
    Dim csvContent As String
    
    ' 替换为你实际的多段JSON文本
    rawText = "{""id"":0,""name"":""NAME"",""area"":""Z"",""site"":""https://www.a-site-here.com/en/index.html"",""logo"":""https://www.another-site-here.com/wp-content/uploads/2022/02/image-GetFileAttachment1.png""},{""id"":1,""name"":""NAME2"",""area"":""Y"",""site"":""https://example.com"",""logo"":""https://example.com/logo.png""}"
    
    ' 转为合法JSON数组格式
    validJson = "[" & rawText & "]"
    
    ' 解析JSON
    Set jsonArray = JsonConverter.ParseJson(validJson)
    
    ' 构建CSV表头
    csvContent = "id,name,area,site,logo" & vbCrLf
    
    ' 遍历生成CSV行
    For Each jsonItem In jsonArray
        ' 处理字段中的双引号，符合CSV规范
        Dim formattedName As String, formattedArea As String
        Dim formattedSite As String, formattedLogo As String
        formattedName = Replace(jsonItem("name"), """", """""")
        formattedArea = Replace(jsonItem("area"), """", """""")
        formattedSite = Replace(jsonItem("site"), """", """""")
        formattedLogo = Replace(jsonItem("logo"), """", """""")
        
        csvContent = csvContent & jsonItem("id") & ",""" & formattedName & """,""" & formattedArea & """,""" & formattedSite & """,""" & formattedLogo & """" & vbCrLf
    Next jsonItem
    
    ' 结果写入Sheet1的A1单元格
    Sheet1.Range("A1").Value = csvContent
    ' 如需保存为CSV文件，可取消以下注释
    ' Open "C:\your-path\output.csv" For Output As #1
    ' Print #1, csvContent
    ' Close #1
End Sub

内容的提问来源于stack exchange，提问作者TomaTifucio