How to output JSON array as a single field in CSV using ChoETL

Question

I'm using ChoETL to convert JSON to CSV. Currently, if a property in the JSON object is an array it is output into separate fields in JSON. Example:

{
    "id", 1234,
    "states": [
        "PA",
        "VA"
     ]
},
{
    "id", 1235,
    "states": [
        "CA",
        "DE",
        "MD"
     ]
},

This results in CSV like this (using pipe as a delimeter)

"id"|"states_0"|"states_1"|"states_2"
"1234"|"PA"|"VA"
"1235"|"CA"|"DE"|"MD"

What I would like is for the array to be displayed in a single states field as a comma separated string

"id"|"states"
"1234"|"PA,VA"
"1235"|"CA,DE,MD"

Here is the code I have in place to perform the parsing and transformation.

public static class JsonCsvConverter
{
    public static string ConvertJsonToCsv(string json)
    {
        var csvData = new StringBuilder();
        using (var jsonReader = ChoJSONReader.LoadText(json))
        {
            using (var csvWriter = new ChoCSVWriter(csvData).WithFirstLineHeader())
            {
                csvWriter.WithMaxScanRows(1000);
                csvWriter.Configuration.Delimiter = "|";
                csvWriter.Configuration.QuoteAllFields = true;
                csvWriter.Write(jsonReader);
            }
        }

        return csvData.ToString();
    }
}

Edited: Removed test code that wasn't useful

Cinchoo · Accepted Answer · 2020-06-26T13:36:23.457

0

This is how you can produce the expected output using the code below

var csvData = new StringBuilder();
using (var jsonReader = ChoJSONReader.LoadText(json))
{
    using (var csvWriter = new ChoCSVWriter(csvData)
        .WithFirstLineHeader()
        .WithDelimiter("|")
        .QuoteAllFields()
        .Configure(c => c.UseNestedKeyFormat = false)
        .WithField("id")
        .WithField("states",  m => m.ValueConverter(o => String.Join(",", ((Array)o).OfType<string>())))
        )
    {
        csvWriter.Write(jsonReader);
    }
}

Console.WriteLine(csvData.ToString());

Output:

id|states
"1234"|"PA,VA"
"1235"|"CA,DE,MD"

PS: on the next release, this issue will be handled automatically without using valueconverters

edited Jun 26 '20 at 13:36

answered Jun 25 '20 at 20:26

Cinchoo

6,088
2
19
34

thanks, I was getting this using your code "System.InvalidCastException: 'Unable to cast object of type 'ChoETL.ChoDynamicObject' to type 'System.Array'.'" but that lead me down the path to use this code to convert the data to a string .WithField("states", valueConverter: o => o == null? "" : String.Join(",", (o as ChoDynamicObject)["states"].ConvertToFlattenObject())) If you update your answer I'll mark it as the solution. Is there any way to not have to hard code the fields or pre-analyze the JSON objects for times when the JSON structure isn't known? – cminus Jun 26 '20 at 13:21
there is bug with assigning value converter to fields. made adjustment to the code to assign converter. see my updates above, – Cinchoo Jun 26 '20 at 13:37
In regards to your question `Is there any way to not have to hard code the fields or pre-analyze the JSON objects for times when the JSON structure isn't known?`. Yes will be handled automatically on the next release. – Cinchoo Jun 26 '20 at 13:38

How to output JSON array as a single field in CSV using ChoETL

1 Answers1

Linked