Multidimensional array

[Stnaire]

Hi,

I've got an excel file like this:

A	B
settings.layout[0][]	[i]
settings.layout[0][]	[e]
settings.layout[0][]	[ɛ]
settings.layout[0][]	[a]
settings.layout[0][]	[y]
settings.layout[0][]	[ø]
settings.layout[0][]	[œ]
settings.layout[0][]	[u]
settings.layout[0][]	[o]
settings.layout[1][]	[ɔ]
settings.layout[1][]	[ɛ̃]
settings.layout[1][]	[f]

I excepted the output to be:

[
    {
        "settings": {
            "layout": [
                [
                    "[i]",
                    "[e]",
                    "[ɛ]",
                    "[a]",
                    "[y]",
                    "[ø]",
                    "[œ]",
                    "[u]",
                    "[o]",
                ],
                [
                    "[ɔ]",
                    "[ɛ̃]",
                    "[f]",
                ]
            ]
        }
    }
]

But instead I get:

[
    {
        "settings": {
            "layout[0]": [
                "[o]"
            ],
            "layout[1]": [
                "[f]"
            ],
            "layout[3]": [
                "****"
            ]
        }
    }
]

Am I missing something or the plugin doesn't handle the array markup recursively?

If I have in something like this in Excel:

A	B
settings.other[0].setting1	value1
settings.other[0].setting2	value2

I got the following output (as expected):

[
    "settings": {
      "other": [
        {
          "setting1": "value1",
          "setting2": "value2"
        }
      ]

But how I define an array in setting1? If I do:

A	B
settings.other[0].setting1[]	value1.1
settings.other[0].setting1[]	value1.2
settings.other[0].setting2	value2

I got the following output:

[
  "settings": {
    "other": [
      {
        "setting1": [
          "value1.2"
        ],
        "setting2": "value2"
      }
    ]
  }
]

Thanks for your help.

[stevetarver]

Hey @Stnaire, let me see if I can provide more clarity. The repo README describes how to form an array from a single cell's contents:

An embedded array key name looks like this and has ';' delimited values

aliases[]
stormagedden;bob

and produces

[{
  "aliases": [
    "stormagedden",
    "bob"
  ]
}]

The key column (your column A) is a path to an element in the json doc and the value column (your column B) is the value.

When you have a spreadsheet with entries like:

Key	Value
settings.layout[0][]	[i]
settings.layout[0][]	[e]

you are assigning settings.layout[0] multiple times - each overwriting the previous entry.

To match the expected output you list next, you would have spreadsheet entries like this:

Key	Value
settings.layout[0][]	[i];[e];...
settings.layout[1][]	[a];[b];...

Notice above how each array element has exactly one value cell...

What you display as what you actually get, is as designed.

Example 2 in your issue is a variant on example 1.

If you spend a little time with the README and work through the examples, I think things will become clear. Closing this issue as it appears the design just needed some clarification.

[Stnaire]

Hi,

You're right, I should have read the readme more carefully.

Thanks for the quick answer.

[Stnaire]

But, let me detail a little more, because my issue is not totally resolved. Doing:

A	B
settings.layout[0][]	[i];[e];[ɛ];[a];[y];[ø];[œ];[u];[o]
settings.layout[1][]	[ɔ];[ɛ̃];[f]

Produces the following output:

[
  {
    "settings": {
      "layout[0]": [
        "[i]",
        "[e]",
        "[ɛ]",
        "[a]",
        "[y]",
        "[ø]",
        "[œ]",
        "[u]",
        "[o]"
      ],
      "layout[1]": [
        "[ɔ]",
        "[ɛ̃]",
        "[f]"
      ]
  }
]

which is better that what I had, but not ideal.

Here I'm looking for an array of arrays, like this:

[{
	"settings": {
		"layout": [
			["[i]", "[e]", "[ɛ]", "[a]", "[y]", "[ø]", "[œ]", "[u]", "[o]"],
			["[ɔ]", "[ɛ̃]", "[f]"]
		]
	}
}]

And it doesn't seem possible with your approach, but I can be wrong.

Just for the sake of saying, when I over-viewed the readme, I imagined the following:

• Putting brackets in a key mean "it's an array".
• If brackets are empty it means add the value to the current array
• If brackets contain a number, this is the index to write into

So if you do:

A	B
aliases[]	a
aliases[]	b

It would mean:

[
  {
    "aliases": ["a", "b"]
  }
]

For an array of objects, it would be:

A	B
settings[0].key1	a
settings[0].key2	b
settings[1].key1	c
settings[1].key2	d

which would translate to:

[
  {
    "settings": [
      {
        key1: "a",
        key2: "b"
      },
	  {
        key1: "c",
        key2: "d"
      }
    ]
  }
]

And for an array of arrays, it would be:

A	B
settings.layout[0][]	a
settings.layout[0][]	b
settings.layout[0][]	c
settings.layout[1][]	d
settings.layout[1][]	e
settings.layout[1][]	f

which would translate to:

[
  {
    "settings": {
      "layout": [
        ["a", "b", "c"],
        ["d", "e", "f"],
      ]
    }
  }
]

Which could be simplified to:

A	B
settings.layout[0][]	a;b;c
settings.layout[1][]	d;e;f

Like you do right now, but it means the value column can be structuring or not depending on the case, which is odd in my opinion.

I think the A column should be the only responsible for structural choices, the B column should always be a simple value.

[stevetarver]

@Stnaire:

The excel-as-json encoding scheme is key-value assignment; the key is a json path to an element, and the value is assigned that element. There is no facility for denoting a multidimensional array key name and no value parser that handles arrays - values are simple types like numbers, strings, and booleans.

I am starting a new job and can't see implementing this in the foreseeable future. Please feel free to fork the repo and implement that feature.

I will leave this issue open - it is an interesting feature.

[stevetarver]

Implementation notes:

Provide syntax for creating multidimensional arrays as values:

[{
  "settings": {
    "layout": [
      ["[i]", "[e]", "[ɛ]", "[a]", "[y]", "[ø]", "[œ]", "[u]", "[o]"],
      ["[ɔ]", "[ɛ̃]", "[f]"]
    ]
  }
}]

Perhaps names[[n]] = value would set contents of the nth row - keeping with our path = value assignment strategy. But:

• How many dimensions should be supported?
• Should the multidimensional array be allowed as a value? Requires adding another value parser.
• Will the above break existing users - if they have value cells with a leading [?