Help with Json Remote dataset

Get Help
1

Hi there

Im looking for help with my Json file Im trying to use for a remote dataset? Whenever I specify something in dataroot, the Test data URL just returns two square brackets but if I leave dataroot blank, and then do a Test Data URL I get the contents showing of my json file. Ive declared all my columns etc and if I understand this correctly, I should be specifying ‘Legend’ as my dataroot? Heres the content of my json file :

[
{
“Legend”: “Actual to Date”,
“Ply”: 58645,
“Tru”: 40832,
“Dob”: 34394,
“East”: 42403,
“West”: 20615,
“Val”: 20265
},
{
“Legend”: “Budget to Date”,
“Ply”: 58007,
“Tru”: 48242,
“Dob”: 32720,
“East”: 40140,
“West”: 20564,
“Val”: 20186
},
{
“Legend”: “Forecast”,
“Ply”: 58654,
“Tru”: 40832,
“Dob”: 34348,
“East”: 42403,
“West”: 20615,
“Val”: 20265
},
{
“Legend”: “Budget”,
“Ply”: 58007,
“Tru”: 48242,
“Dob”: 32720,
“East”: 40140,
“West”: 20564,
“Val”: 20186
}
]

So when I add Legend into dataroot I get two square brackets returned only (same if I try Ply or Tru etc as a test), leave dataroot blank and use the Test Data URL, I get the following :

{
“entries”: [
[
{
“Legend”: “Actual to Date”,
“Ply”: 58645,
“Tru”: 40832,
“Dob”: 34394,
“East”: 42403,
“West”: 20615,
“Val”: 20265
},
{
“Legend”: “Budget to Date”,
“Ply”: 58007,
“Tru”: 48242,
“Dob”: 32720,
“East”: 40140,
“West”: 20564,
“Val”: 20186
},
{
“Legend”: “Forecast”,
“Ply”: 58654,
“Tru”: 40832,
“Dob”: 34348,
“East”: 42403,
“West”: 20615,
“Val”: 20265
},
{
“Legend”: “Budget”,
“Ply”: 58007,
“Tru”: 48242,
“Dob”: 32720,
“East”: 40140,
“West”: 20564,
“Val”: 20186
}
]
],
“number”: 1,
“processed”: [
[
{
“Legend”: “Actual to Date”,
“Ply”: 58645,
“Tru”: 40832,
“Dob”: 34394,
“East”: 42403,
“West”: 20615,
“Val”: 20265
},
{
“Legend”: “Budget to Date”,
“Ply”: 58007,
“Tru”: 48242,
“Dob”: 32720,
“East”: 40140,
“West”: 20564,
“Val”: 20186
},
{
“Legend”: “Forecast”,
“Ply”: 58654,
“Tru”: 40832,
“Dob”: 34348,
“East”: 42403,
“West”: 20615,
“Val”: 20265
},
{
“Legend”: “Budget”,
“Ply”: 58007,
“Tru”: 48242,
“Dob”: 32720,
“East”: 40140,
“West”: 20564,
“Val”: 20186
}
]
],
“messages”: [
“Processing 1 results into 7 potential columns”,
"Processing Result with Data Root ",
“DataRoot is an array”,
“Processing 0”,
“Processing 1”,
“Processing 2”,
“Processing 3”,
“Consolidating entries”,
“There are 4 entries in total”
]
}

Any suggestions would be lovely - new to all this

Thanks
Jimbo

2

It’s expecting something like this:

{
  "rows": [
    {
      "Legend": "Actual to Date",
      "Ply": 58645,
      "Tru": 40832,
      "Dob": 34394,
      "East": 42403,
      "West": 20615,
      "Val": 20265
   },
   {
      "Legend": "Budget to Date",
      "Ply": 58007,
      "Tru": 48242,
      "Dob": 32720,
      "East": 40140,
      "West": 20564,
      "Val": 20186
    }
  ]
}

In which case rows would be your data root.

1 Like
3

Many thanks Alex - I will go back to my Excel VBA and see what i can do to add that in.

Jimbo

4

Hi Alex

Thanks for that - I now have some VBA code that exports a Json file from Excel every time the file is saved with the structure that Xibo likes and I have values in my dataset :slight_smile: Just what I need and I can now use this as a template for other datasets I need to add :slight_smile:

If anyone else wants advice on Excel to Json using VBA for remote datasets in Xibo, feel free to ask. I can put the code up that worked for me or at least point you in the right direction where I found a great working example to base yourself from? The only reason Im offering is because unless you are a programmer by nature like myself, it took me a while to get this sorted out (over 2 weeks in fact) in a structure that Xibo wants (wasnt easy to find).

Jimbo

1 Like
5

I got the excel to JSON figured out, but having issues figuring out how to add the root element. So far, my code looks like this:

Public Sub SaveToJSON()
Dim excelRange As Range
Dim jsonItems As New Collection
Dim jsonDictionary As New Dictionary
Dim jsonFileObject As New FileSystemObject
Dim jsonFileExport As TextStream
Dim i As Long
Dim cell As Variant

Application.Sheets("RawData").Activate
Application.ActiveSheet.UsedRange
Set excelRange = Cells(1, 1).CurrentRegion

For i = 2 To excelRange.Rows.Count
    jsonDictionary("Scheduled start") = Cells(i, 1)
    jsonDictionary("Work center") = Cells(i, 2)
    jsonDictionary("Days to Act") = Cells(i, 3)
    jsonDictionary("Order") = Cells(i, 4)
    jsonDictionary("Material") = Cells(i, 5)
    jsonDictionary("Material Description") = Cells(i, 6)
    jsonDictionary("Operation Quantity") = Cells(i, 7)
    jsonDictionary("Loading Date") = Cells(i, 8)
    jsonDictionary("Customer name") = Cells(i, 9)
    jsonDictionary("Industry Std Desc.") = Cells(i, 9)

    jsonItems.Add jsonDictionary
    Set jsonDictionary = Nothing

Next i

Set jsonFileExport = jsonFileObject.CreateTextFile("\\<server>\schedule.json", True)
jsonFileExport.WriteLine (JsonConverter.ConvertToJson(jsonItems, Whitespace:=3))

End Sub
6

Hi Fred

Can you show your JSON output that this code currently gives you? You will definately need at least another couple of lines of code in order to declare an additional dictionary item in the Dim section and then to ‘wrap’ your existing excel data around that dictionary item so that Xibo feeds it correctly into its datasets.

I can give you my whole VBA code that works for me if you like - its laid out slightly differently to this though but your code above looks like where I started out from

Jimbo

7

Hey Jim,

Below is a part of the output. The full output is pretty large, so just pretend the bottom brackets are in place.

[

{
“Scheduled start”: “2019-08-12T04:00:00.000Z”,
“Work center”: “B1”,
“Days to Act”: -3,
“Order”: “6377371”,
“Material”: “FO20040854TT”,
“Material Description”: “DM790B BLACK PLASTISOL”,
“Operation Quantity”: 9999.999,
“Loading Date”: “2019-08-12T04:00:00.000Z”,
“Customer name”: “HOLT SALES & SERVICE”,
“Industry Std Desc.”: “HOLT SALES & SERVICE”
},
{
“Scheduled start”: “2019-08-12T04:00:00.000Z”,
“Work center”: “B1”,
“Days to Act”: -4,
“Order”: “6377372”,
“Material”: “FO20040854TT”,
“Material Description”: “DM790B BLACK PLASTISOL”,
“Operation Quantity”: 9999.999,
“Loading Date”: “2019-08-12T04:00:00.000Z”,
“Customer name”: “HOLT SALES & SERVICE”,
“Industry Std Desc.”: “HOLT SALES & SERVICE”
},
{

8

Ok - I would start by adding a new dictionary item into the Dim section (just add it anywhere in there or under the last one) and call it whatever you want as long as its a dictionary item. Its just a text item where you will declare your dataroot for Xibo so example would be (without speech marks) “Dim callitwhatyouwant As New Dictionary”

Then under the line “Next i” add the line of code that will assign the text to the new ‘callitwhatyouwant’ dictionary item (which will have to match whatever you declare as the data root in Xibo for your dataset) and then write the ‘collection’ ie the values from your excel sheet after that - in your case ‘jsonItems’:

callitwhatyouwant.Add “yourdatarootnamethatyouwillspecifyinxibo”, jsonItems

Finally you will need to change your writeline command too. You need to replace jsonItems with the name youve given your dictionary item so for example :
jsonFileExport.Writeline (JsonConverter.ConverttoJson (callitwhatyouwant, whitespace:=3))

My whitespace setting is 2 btw but that doesnt mean yours also has to be.

Try those 3 changes and see what happens - make a current back up or make sure you know what youve changed in case you need to revert back.

If you need the whole of my code to look at for an example, please just ask and I will post an example with sensitive name changes etc :slight_smile:

Good luck

1 Like
9

That worked perfect. Thank you, Jimbo. Much appreciated. You are right, by the way. I searched all over the bloody place too and found little to nothing about it.

1 Like
10

Good to hear - use that as your template now for other datasets you might need to set up :slight_smile:

Jimbo