Parse JSON

Hi All,

I am trying to create a database of UK companies using publicly available data from the UK Companies house (https://download.companieshouse.gov.uk/en_pscdata.html)

The file is in JSON format and using Download/JSON parse it does not seem to work. The download in particular does not seem to work as the file is in Zip format and it is one of the many

The database is pretty large (over 9 GB) so I have uploaded a small sample.

Any ideas?

psc-snapshot-2024-04-08_26of26.zip

Developer

Developer Tools

Connectors

Preparation

Download

Accepted answers

BS_THE_ANALYST

@ste_demi

I've put something together for you:

I ran the container on the top to unzip the files using python, up to you if you want to do this manually.

I then added the bottom data stream to actually unpack the .txt file. I used "\n" as a delimiter as each row in text file is in JSON form. So parsed the text file into individual rows and then used the JSON parse tool.

Seemed to get the job done. I'll attach my solution. Have a play around with it.

All the best,

communityJSON.yxzp

All comments

BS_THE_ANALYST

@ste_demi if the first issue is at hand is the zip format, then the first process is seeing how to unzip these files before processing them. If the process is repeatable you could use a batch macro if you're worried about processing issues on the machine and drop out a .yxdb file at the end of each run. Afterwards you could union all the .yxdb files together and do one bulk upload to the database (or you could just incrementally load them via each batch's run).

All the best,

BS_THE_ANALYST

communityJSON.yxzp

ste_demi

That was brilliant! many thanks

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703