Importing JSOn file into Excel

Question

I have a JSON file. I tried to import using Get Data in Excel. Excel says "succeeded" but it only show 5 rows. See screenshot.

Somehow it is not parsing the entire Json file? please help. I am completely new to JSON. Thanks.

Answer 1

import using Get Data in Excel.

I guess no general build-in function for import json.

Json files have different formats,so that you have to parse string by yourself.

Answer 2

Hi

From the screen shot it looks like there could be an empty row in the data set. An empty row signifies the end of the data range. The original JSON file should be checked and empty rows be removed.

Answer 3

Thanks makes sense, what is the easiest way for a newbie to remove those rows? I tried this https://codebeautify.org/remove-empty-lines

which converted to a CSV but the output is still unstructured.

Here is the json file image (partial). I used microsoft Azure to extract data from a form but I don't see how to use it from json.

Thanks.

here is the result.

Answer 4

Hi

Although I am an Expert in Excel, I am not an expert with JSON. You might explore https://stackoverflow.com/ and see if you can find an app that lets you edit the file.

Answer 5

// ?s=1115

//with javascript

o={"status":"success","dateTime":"2024-05-18"}

arr=new Array;

arr.push(o.status)

arr.push(o.dateTime)

arr2=new Array

arr2.push(arr)

console.table(arr2)