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-26 13:36:23Z

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, 2020 at 13:36

answered Jun 25, 2020 at 20:26

Cinchoo

6,3562 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

cminus Over a year ago

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?

Cinchoo Over a year ago

there is bug with assigning value converter to fields. made adjustment to the code to assign converter. see my updates above,

Cinchoo Over a year ago

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.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related