JSON parsing from txt file to Excel-CodePudding

I have a data that was identified as having JSON format. The data is in a *.txt file and the following is a snapshot from the text:

{"company_number":"09145694","data":{"address":{"address_line_1":"St. Andrews Road","country":"England","locality":"Henley-On-Thames","postal_code":"RG9 1HP","premises":"2"},"ceased_on":"2018-05-14","country_of_residence":"England","date_of_birth":{"month":2,"year":1977},"etag":"3b8caf795c03af63921e381f7bb8300a51ebb73d","kind":"individual-person-with-significant-control","links":{"self":"/company/09145694/persons-with-significant-control/individual/bIhuKnMFctSnjrDjUG8n3NgOrlU"},"name":"Mrs Nga Thanh Wildman","name_elements":{"forename":"Nga","middle_name":"Thanh","surname":"Wildman","title":"Mrs"},"nationality":"Vietnamese","natures_of_control":["ownership-of-shares-50-to-75-percent"],"notified_on":"2016-04-06"}}
    {"company_number":"08581893","data":{"address":{"address_line_1":"High Street","address_line_2":"Wendover","country":"England","locality":"Aylesbury","postal_code":"HP22 6EA","premises":"14a","region":"Buckinghamshire"},"ceased_on":"2016-07-01","country_of_residence":"England","date_of_birth":{"month":9,"year":1947},"etag":"45f9c9e5494b574eb52abc3990a49bd96fe09df3","kind":"individual-person-with-significant-control","links":{"self":"/company/08581893/persons-with-significant-control/individual/RgR9Zhc7yGhV0SBys8_WJ6H9O1o"},"name":"Mr Stephen Robert Charles Davies","name_elements":{"forename":"Stephen","middle_name":"Robert Charles","surname":"Davies","title":"Mr"},"nationality":"British","natures_of_control":["ownership-of-shares-25-to-50-percent","ownership-of-shares-25-to-50-percent-as-firm"],"notified_on":"2016-06-30"}}
{"company_number":"08581893","data":{"address":{"address_line_1":"High Street","address_line_2":"Wendover","country":"England","locality":"Aylesbury","postal_code":"HP22 6EA","premises":"14a","region":"Buckinghamshire"},"ceased_on":"2016-07-01","country_of_residence":"England","date_of_birth":{"month":6,"year":1965},"etag":"d55168c49f85ab1ef38a12ed76238d68f79f5a01","kind":"individual-person-with-significant-control","links":{"self":"/company/08581893/persons-with-significant-control/individual/-6HQmkhiomEBXJI2rgHccU67fpM"},"name":"Mr Quentin Colin Maxwell Solt","name_elements":{"forename":"Quentin","middle_name":"Colin Maxwell","surname":"Solt","title":"Mr"},"nationality":"British","natures_of_control":["ownership-of-shares-25-to-50-percent","voting-rights-25-to-50-percent"],"notified_on":"2016-06-30"}}

How do I transfer this to a normal excel table with appropriate headings please?

The code i Have tried is from the suggestion in the comments and i have added the dictionary to the excel file as described here, suggested by @skin.

github.com/VBA-tools/VBA-JSON

I am getting a 424 error on the line

Set Parsed = JsonConverter.ParseJson(JsonText)

Here is the code:

Sub jsonchik()


Dim FSO As New FileSystemObject
Dim JsonTS As TextStream
Dim JsonText As String
Dim Parsed As Dictionary

' Read .json file
Set JsonTS = FSO.OpenTextFile("psc-snapshot-2022-11-12_1of22.txt", ForReading)
JsonText = JsonTS.ReadAll
JsonTS.Close

' Parse json to Dictionary
' "values" is parsed as Collection
' each item in "values" is parsed as Dictionary
Set Parsed = JsonConverter.ParseJson(JsonText)

' Prepare and write values to sheet
Dim Values As Variant
ReDim Values(Parsed("values").Count, 3)

Dim Value As Dictionary
Dim i As Long

i = 0
For Each Value In Parsed("values")
  Values(i, 0) = Value("a")
  Values(i, 1) = Value("b")
  Values(i, 2) = Value("c")
  i = i   1
Next Value

Sheets("example").Range(Cells(1, 1), Cells(Parsed("values").Count, 3)) = Values


End Sub

Many thanks,

Suren

CodePudding user response：

You get this because you haven't loaded the code for JsonConverter.

Download the zip file with the code

Unzip the content
Right-click on your project and Import the file named JsonConverter.bas

Now, JsonConverter will refer to the module you just imported and ParseJson to the method in that module.

Remark: I'm suspecting that you don't have Option Explicit set at the top of your module. If you had, you would get a "Variable Undefined Error" with ParseJson highlighted. That would certainly be more informative in terms error messaging. And that's just one of the good reasons to use Option Explicit!

CodePudding user response：

I have found a solution, using Microsfot Query, which has a JSON parser.

First, my data had some problems, so i had to validate it here: [https://jsonlint.com/][1]

second, I use MS Excel/Get & Transform Data/from Text/CSV to import my data.

In Importing Wisard, I clicked Transform Data, instead of Load to:

Under Transform Menu/Text Column/Parse/Json.

Works like a charm!

Thank you all.