使用CHOETL将JSON转换为CSV并将其显示在一行而不是一列中 [英] JSON to CSV conversion using CHOETL displaying values in one row and not columns

问题描述

我正在将JSON文件转换为CSV文件. JSON具有多个嵌套对象.转换时，我可以将所有值从JSON转换为CSV.但是，所有值都显示为一行，并且相同的标题重复了多次.我正在使用CHOETL库.

I am converting a JSON file to a CSV file. The JSON has multiple nested objects. While converting, I am able to get all the values out of the JSON and into the CSV. However, all the values are being shown as one row with the same heading repeated multiple times. I am using CHOETL library.

using (var csv = new ChoCSVWriter("file1.csv").WithFirstLineHeader().WithDelimiter(","))
{
    using (var json = new ChoJSONReader("file2.json")
        .WithField("RecordID", jsonPath: "$..Events[*].RecordId")
        .WithField("RecordType", jsonPath: "$..Events[*].RecordType")
        .WithField("EventDate", jsonPath: "$..Events[*].EventDate")
    {
        csv.Write(json);
    }
}

结果显示为

• 记录ID_0记录ID_1记录ID_2
• 123 456 789

而不是

• 记录ID
• 123
• 456
• 789

这是JSON文件

[
    {
        "Id": "3e399241",
        "IdLineage": [
            "sfdsfdsfs",
            "sdfdsfdsf"

        ],
        "Individuals": [
            {
                "Id": "1232112",
                "IdLineage": [
                    "fdsfsd1"
                ],
                "Events": [
                    {

                        "RecordId": "2132121321",
                        "RecordType": "SALE",
                        "EventDate": "2016-01-04T05:00:00Z"
                    },
                    {

                        "RecordId": "123213212",
                        "RecordType": "SALE",
                        "EventDate": "2012-07-16T04:00:00Z"
                    }


                ]
            },
            {
                "Id": "ssf2112",
                "IdLineage": [],
                "Events": [

                    {

                        "RecordId": "123213ds21",
                        "RecordType": "ACXIOMRECORD",
                        "EventDate": "2017-12-17T03:33:54.875Z"
                    }
                ]
            },
            {
                "Id": "asadsad",
                "IdLineage": [],
                "Events": [
                    {

                        "RecordId": "213213sa21",
                        "RecordType": "SALE",
                        "EventDate": "2018-03-09T05:00:00Z"
                    }
                ]
            }
        ]
    }
]

推荐答案

基于您发布的示例代码，您正在通过JSON创建对象，如下所示:

Based on sample code you posted, you are creating object from JSON as below

{
   RecordID : Array,
   RecordType: Array,
   EventDate: Array
}

这将导致按以下格式生成CSV，这与预期的一样.

This leads to generate CSV in the below format, this is as expected.

RecordID_0, RecordID_1, RecordID_2, RecordType_0, RecordType_1, ....

如果要以以下格式创建CSV，则必须在每个记录字段上修复json路径

If you want to create CSV in the below format, you will have to fix the json path on each record field

RecordID, RecordType, EventData

示例代码

using (var csv = new ChoCSVWriter("file1.csv").WithFirstLineHeader().WithDelimiter(","))
{
    using (var json = new ChoJSONReader("file2.json")
    .WithField("RecordID", jsonPath: "$..Events.RecordId")
    .WithField("RecordType", jsonPath: "$..Events.RecordType")
    .WithField("EventDate", jsonPath: "$..Events.EventDate")

    {
        csv.Write(json);
    }
}

更新#1: 在查看了示例JSON之后，您便可以通过这种方式提取数据并以预期的格式生成CSV文件

UPDATE #1: After looking at the sample JSON, this is how you can pull the data and produce CSV file in expected format

StringBuilder msg = new StringBuilder();

using (var w = new ChoCSVWriter(msg)
    .WithFirstLineHeader()
    )
{
    using (var r = new ChoJSONReader("Sample32.json")
        .WithJSONPath("$..Events[*]")
        )
    {
        w.Write(r);
    }
}
Console.WriteLine(msg.ToString());

输出1:

RecordId,RecordType,EventDate
2132121321,SALE,1/4/2016 5:00:00 AM
123213212,SALE,7/16/2012 4:00:00 AM
123213ds21,ACXIOMRECORD,12/17/2017 3:33:54 AM
213213sa21,SALE,3/9/2018 5:00:00 AM

更新#2:

您必须使用Linq将ID与事件成员结合在一起.下面的示例展示了如何

You must use Linq to combine id's with event members. Sample below shows how to

using (var fw = new StreamWriter("Sample32.csv", true))
{
    using (var w = new ChoCSVWriter(fw)
        .WithFirstLineHeader()
        )
    {
        using (var r = new ChoJSONReader("Sample32.json")
            .WithJSONPath("$..Individuals[*]")
            )
        {
            w.Write(r.SelectMany(r1 => ((dynamic[])r1.Events).Select(r2 => new { r1.Id, r2.RecordId, r2.RecordType, r2.EventDate })));
        }
    }
}
Console.WriteLine(File.ReadAllText("Sample32.csv"));

输出#2:

Id,RecordId,RecordType,EventDate
1232112,2132121321,SALE,1/4/2016 5:00:00 AM
1232112,123213212,SALE,7/16/2012 4:00:00 AM
ssf2112,123213ds21,ACXIOMRECORD,12/17/2017 3:33:54 AM
asadsad,213213sa21,SALE,3/9/2018 5:00:00 AM

这篇关于使用CHOETL将JSON转换为CSV并将其显示在一行而不是一列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！