StarliteLemming
New Member
- Joined
- Jun 2, 2022
- Messages
- 12
- Office Version
- 2019
- Platform
- Windows
Using JSON files outside whatever their native environment is (JavaScript, presumably) is a major pain in the backside. Rather than trying to fully understand them, I'd just like some help solving one, single, active problem: how to import the JSON file below into Excel.
I have a "public" data source stored as 20-odd JSON files. I've taken one of those and simplified it before pasting below. Save it as test.json or something similar.
I'd like to import the data in this file into Excel in a flattened form that I can then massage into what will become a mail merge data source to automatically build a document. I know Office is the wrong tool to use, but Word does excel at formatting, and that's a critical part of my ultimate goal (the Word side is now working, incidentally).
Here's the sequence of steps I've tried:
1. Open new file in Excel.
2. Data | Get Data | From File | From JSON
3. Select file test.json (included below).
4. Right-click List in centre pane, next to "item" field name. Select Drill Down.
5. Transform | To Table
6. Delimiter = None; Extra Columns = Show as errors.
7. Close and Load.
Unfortunately, the data doesn't flatten properly, so I just get a column of single-field data with the text [Record].
So, what am I doing wrong and why? And how do I get it to work?
I have a "public" data source stored as 20-odd JSON files. I've taken one of those and simplified it before pasting below. Save it as test.json or something similar.
I'd like to import the data in this file into Excel in a flattened form that I can then massage into what will become a mail merge data source to automatically build a document. I know Office is the wrong tool to use, but Word does excel at formatting, and that's a critical part of my ultimate goal (the Word side is now working, incidentally).
Here's the sequence of steps I've tried:
1. Open new file in Excel.
2. Data | Get Data | From File | From JSON
3. Select file test.json (included below).
4. Right-click List in centre pane, next to "item" field name. Select Drill Down.
5. Transform | To Table
6. Delimiter = None; Extra Columns = Show as errors.
7. Close and Load.
Unfortunately, the data doesn't flatten properly, so I just get a column of single-field data with the text [Record].
So, what am I doing wrong and why? And how do I get it to work?
JSON:
{
"item": [
{
"name": "Name 1",
"source": "CET",
"page": 106,
"level": 9,
"scode": "C",
"time": [
{
"number": 1,
"unit": "bonus"
}
],
"range": {
"type": "point",
"distance": {
"type": "feet",
"amount": 60
}
},
"comp": {
"v": true,
"s": true
},
"dtime": [
{
"type": "timed",
"time": {
"type": "minute",
"amount": 1
},
"cconc": true
}
],
"entries": [
"Text 1.1",
"Text 1.2",
"Text 1.3"
],
"dtype": [
"fc"
],
"spatt": [
"M"
],
"miscTags": [
"SGT"
],
"areaTags": [
"MT"
],
"cats": {
"fromCatListVariant": [
{
"name": "Scale",
"source": "PDD",
"definedInSource": "CET"
},
{
"name": "Chant",
"source": "PDD",
"definedInSource": "CET"
},
{
"name": "Create",
"source": "PDD",
"definedInSource": "CET"
}
]
}
},
{
"name": "Name 2",
"source": "CET",
"page": 106,
"level": 0,
"scode": "V",
"time": [
{
"number": 1,
"unit": "action"
}
],
"range": {
"type": "radius",
"distance": {
"type": "feet",
"amount": 5
}
},
"comp": {
"s": true,
"m": {
"text": "thing worth at least 1",
"cost": 10
}
},
"dtime": [
{
"type": "timed",
"time": {
"type": "round",
"amount": 1
}
}
],
"entries": [
"Text 2.1",
"Text 2.2"
],
"dtype": [
"th"
],
"miscTags": [
"SCL"
],
"areaTags": [
"ST"
],
"cats": {
"fromCatList": [
{
"name": "Build",
"source": "CET"
},
{
"name": "Scale",
"source": "PDD",
"definedInSource": "SGA"
},
{
"name": "Chant",
"source": "PDD",
"definedInSource": "SGA"
},
{
"name": "Create",
"source": "PDD",
"definedInSource": "SGA"
}
],
"fromCatListVariant": [
{
"name": "Scale",
"source": "PDD",
"definedInSource": "CET"
},
{
"name": "Chant",
"source": "PDD",
"definedInSource": "CET"
},
{
"name": "Create",
"source": "PDD",
"definedInSource": "CET"
}
]
}
},
{
"name": "Name 3",
"source": "CET",
"page": 106,
"level": 7,
"scode": "C",
"time": [
{
"number": 10,
"unit": "minute"
}
],
"range": {
"type": "point",
"distance": {
"type": "feet",
"amount": 20
}
},
"comp": {
"v": true,
"s": true,
"m": "item from the destination"
},
"dtime": [
{
"type": "timed",
"time": {
"type": "hour",
"amount": 6
}
}
],
"entries": [
"Text 3.1",
"Text 3.2",
"Text 3.3"
],
"condition": [
"unconc"
],
"cats": {
"fromCatListVariant": [
{
"name": "Play",
"source": "PDD",
"definedInSource": "CET"
},
{
"name": "Scale",
"source": "PDD",
"definedInSource": "CET"
},
{
"name": "Chant",
"source": "PDD",
"definedInSource": "CET"
},
{
"name": "Create",
"source": "PDD",
"definedInSource": "CET"
}
]
}
},
{
"name": "Name 4",
"source": "CET",
"page": 107,
"level": 0,
"scode": "V",
"time": [
{
"number": 1,
"unit": "action"
}
],
"range": {
"type": "radius",
"distance": {
"type": "feet",
"amount": 5
}
},
"comp": {
"s": true,
"m": {
"text": "item worth at least 1",
"cost": 10
}
},
"dtime": [
{
"type": "instant"
}
],
"entries": [
"Text 4.1",
"Text 4.2"
],
"dtype": [
"f"
],
"miscTags": [
"SCL",
"SGT"
],
"areaTags": [
"ST",
"MT"
],
"cats": {
"fromCatList": [
{
"name": "Build",
"source": "CET"
},
{
"name": "Scale",
"source": "PDD",
"definedInSource": "SGA"
},
{
"name": "Chant",
"source": "PDD",
"definedInSource": "SGA"
},
{
"name": "Create",
"source": "PDD",
"definedInSource": "SGA"
}
],
"fromCatListVariant": [
{
"name": "Scale",
"source": "PDD",
"definedInSource": "CET"
},
{
"name": "Chant",
"source": "PDD",
"definedInSource": "CET"
},
{
"name": "Create",
"source": "PDD",
"definedInSource": "CET"
}
]
}
},
{
"name": "Name 5",
"source": "CET",
"page": 107,
"level": 3,
"scode": "A",
"time": [
{
"number": 1,
"unit": "action"
}
],
"range": {
"type": "point",
"distance": {
"type": "feet",
"amount": 30
}
},
"comp": {
"v": true
},
"dtime": [
{
"type": "timed",
"time": {
"type": "hour",
"amount": 1
},
"cconc": true
}
],
"entries": [
"Text 5.1"
],
"dRes": [
"psy"
],
"miscTags": [
"SGT"
],
"cats": {
"fromCatList": [
{
"name": "Build",
"source": "CET"
}
],
"fromCatListVariant": [
{
"name": "Play",
"source": "PDD",
"definedInSource": "CET"
},
{
"name": "Scale",
"source": "PDD",
"definedInSource": "CET"
},
{
"name": "Chant",
"source": "PDD",
"definedInSource": "CET"
},
{
"name": "Create",
"source": "PDD",
"definedInSource": "CET"
}
]
}
}
]
}
