JSON repeating inputs section from Excel

Question

I have a specific JSON string that I need to match for a rest call. I'm pulling the data from an excel spreadsheet. One of the sections has repeating input like below. The data in my spreadsheet looks like this:

The JSON I need to generate looks like:

"detailInputs": [
  {
    "name": "SOGrid",
    "repeatingInputs": [
        {
            "inputs": [
                {
                    "name": "ItemNumber",
                    "value": "XYZ"
                },
                {
                    "name": "Quantity",
                    "value": "1"
                }
            ]
        },
        {
            "inputs": [
                {
                    "name": "ItemNumber",
                    "value": "ABC"
                },
                {
                    "name": "Quantity",
                    "value": "3"
                }
            ]
        }
    ]

What I've tried so far is below (note jsonArraystring is the header information formatted in a previous section):

using (var conn = new OleDbConnection(connectionString))
{
    sheetName = "Detail";
    conn.Open();
    var cmd = conn.CreateCommand();
    cmd.CommandText = $"SELECT * FROM [{sheetName}$]";

    using (var rdr = cmd.ExecuteReader())
    {
        var query = rdr.Cast<DbDataRecord>().Select(row => new {
            name = row[0],
            value = row[1],
            //description = row[2]
        });

        var json = JsonConvert.SerializeObject(query);
        jsonArrayString = jsonArrayString + ",\"detailInputs\":[{\"name\":\"SOGrid\",\"repeatingInputs\":[{\"inputs\": " + json + "}]}]}";

This is very close, but puts the "repeating Inputs" are all in one inputs section.

I also tried assigning the values to a dictionary and list in hopes of pulling the appropriate pairs and formatting the JSON from that, this is the beginning of that, but I'm not familiar enough with unraveling the key value pairs to get that formatted correctly.

using (var conn = new OleDbConnection(connectionString))
{
    sheetName = "Detail";
    conn.Open();
    int counter = 0;
    var cmd = conn.CreateCommand();
    cmd.CommandText = $"SELECT * FROM [{sheetName}$]";
    var values = new List<Dictionary<string, object>>();
    var ListValues = new List<string>();

    using (var rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            var fieldValues = new Dictionary<string, object>();
            var fieldValuesList = new List<string>();

            for (int i = 0; i < rdr.FieldCount; i++)
            {
                fieldValues.Add(rdr.GetName(i), rdr[i]);
                fieldValuesList.Add(rdr.GetName(i));
            }

            // add the dictionary on the values list
            values.Add(fieldValues);
        }

The root question is how can I create a repeating inputs structure as shown in the JSON sample, by pulling from excel data.

Answer 1

What you want to do is to serialize the contents of the Excel worksheet as the array value of the "repeatingInputs" property, using a specific structure. I would suggest breaking this down into a series of LINQ transformations.

First, introduce a couple of extension methods:

public static class DataReaderExtensions
{
    // Adapted from this answer https://stackoverflow.com/a/1202973
    // To https://stackoverflow.com/questions/1202935/convert-rows-from-a-data-reader-into-typed-results
    // By https://stackoverflow.com/users/3043/joel-coehoorn
    public static IEnumerable<T> SelectRows<T>(this IDataReader reader, Func<IDataRecord, T> select)
    {
        while (reader.Read())
        {
            yield return select(reader);
        }
    }
}

public static class EnumerableExtensions
{
    // Adapted from this answer https://stackoverflow.com/a/419058
    // To https://stackoverflow.com/questions/419019/split-list-into-sublists-with-linq/
    // By https://stackoverflow.com/users/50776/casperone
    public static IEnumerable<List<T>> ChunkWhile<T>(this IEnumerable<T> enumerable, Func<List<T>, T, bool> shouldAdd)
    {
        if (enumerable == null || shouldAdd == null)
            throw new ArgumentNullException();
        return enumerable.ChunkWhileIterator(shouldAdd);
    }

    static IEnumerable<List<T>> ChunkWhileIterator<T>(this IEnumerable<T> enumerable, Func<List<T>, T, bool> shouldAdd)
    {
        List<T> list = new List<T>();
        foreach (var item in enumerable)
        {
            if (list.Count > 0 && !shouldAdd(list, item))
            {
                yield return list;
                list = new List<T>();
            }
            list.Add(item);
        }
        if (list.Count != 0)
        {
            yield return list;
        }
    }
}

The first method packages an IDataReader into an enumerable of typed objects, one for each row. Doing this makes it easier to feed the data reader's contents into subsequent LINQ transformations. The second method breaks a flat enumerable into an enumerable of "chunks" of lists, based on some predicate condition. This will be used to break the rows into chunks at each ItemNumber row.

Using these two extension methods we can generate the required JSON as follows:

public static string ExtractRows(string connectionString, string sheetName)
{
    using (var conn = new OleDbConnection(connectionString))
    {
        conn.Open();
        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandText = string.Format("SELECT * FROM [{0}]", sheetName);
            using (var rdr = cmd.ExecuteReader())
            {
                var query = rdr
                    // Wrap the IDataReader in a LINQ enumerator returning an array of key/value pairs for each row.
                    // Project the first two columns into a single anonymous object.
                    .SelectRows(r =>
                    {
                        // Check we have two columns in the row, and the first (Name) column value is non-null.
                        // You might instead check that we have at least two columns.
                        if (r.FieldCount != 2 || r.IsDBNull(0))
                            throw new InvalidDataException();
                        return new { Name = r[0].ToString(), Value = r[1] };
                    })
                    // Break the columns into chunks when the first name repeats
                    .ChunkWhile((l, r) => l[0].Name != r.Name)
                    // Wrap in the container Inputs object
                    .Select(r => new { Inputs = r });

                // Serialize in camel case
                var settings = new JsonSerializerSettings
                {
                    ContractResolver = new CamelCasePropertyNamesContractResolver(),
                };
                return JsonConvert.SerializeObject(query, Formatting.Indented, settings);
            }
        }
    }
}

Which will generate the required value for "repeatingInputs":

[
  {
    "inputs": [
      {
        "name": "ItemNumber",
        "value": "XYZ"
      },
      {
        "name": "Quantity",
        "value": "1"
      }
    ]
  },
  {
    "inputs": [
      {
        "name": "ItemNumber",
        "value": "ABC"
      },
      {
        "name": "Quantity",
        "value": "3"
      }
    ]
  }
]