Forum Discussion
Solved
Complicated Automation Help - 11.5 million records to process!
Hi, I’ve got a ton of data to process ie. 23 files with 500,000 rows each - the whole public Companies House People with Significant Control database basically. The files are .txt files where all...
Please check if there is confidential data in your post and remove all confidential data.
=MID(A2,SEARCH("company_number",A2)+17,8)=MID(A2,SEARCH("year",A2)+6,4)If all the records have the same pattern you can try these formulas which are in cells B2 and C2 in the screenshot. The formulas can be filled down as required.
{"company_number":"07857645","data":{"etag":"b4f8ad62cab44de86dda35f25e1f255ca5b2d401","kind":"persons-with-significant-control-statement","links":{"self":"/company/07857645/persons-with-significant-control-statements/7NFOepiXAjxP9TYyVby08mBrdL8"},"notified_on":"2016-11-23","statement":"steps-to-find-psc-not-yet-completed"}} {"company_number":"08830194","data":{"ceased_on":"2017-09-19","etag":"104f8f1fd728ef1298c88b4f2fa1e64fa0f9b4f2","kind":"persons-with-significant-control-statement","links":{"self":"/company/08830194/persons-with-significant-control-statements/Q-AFtiQypQ9iPRE_shKFTRm-RVQ"},"notified_on":"2017-01-02","statement":"psc-exists-but-not-identified"}} {"company_number":"01470777","data":{"etag":"9675c30cef3c1b414f9b481953b01a24981cfc7e","kind":"persons-with-significant-control-statement","links":{"self":"/company/01470777/persons-with-significant-control-statements/KrHX0ZLhI4mXVeT1eUMTCh3FZgo"},"notified_on":"2016-12-31","statement":"no-individual-or-entity-with-signficant-control"}}
Above is the text from http://download.companieshouse.gov.uk/en_pscdata.html.
no "date_of_birth" found from psc-snapshot-2023-05-02_23of23.txt which downloaded from http://download.companieshouse.gov.uk/en_pscdata.html.
can you share a file and show your expected result?
Above is the text from http://download.companieshouse.gov.uk/en_pscdata.html.
no "date_of_birth" found from psc-snapshot-2023-05-02_23of23.txt which downloaded from http://download.companieshouse.gov.uk/en_pscdata.html.
can you share a file and show your expected result?
- Thanks for your reply, it's now solved with the first comment formulas.
