Text File processed into Excel

[mattalex]

I have a text file (see example) which I want to process using EXCEL VBA and output the information into an excel table. Please see example input and output below.
Any help with this would be appreciated!



***********OUTPUT IN EXCEL:***********



"application":{"id":
2071141


"council_reference:
P21/1902


"date_scraped":
2021-08-06T02:11:05.000Z


"address":
5 Matthew Street Mccrae VIC 3938


"description":
Development of a double storey dwelling



etc., etc.


***********TEXT FILE INPUT:***********
[{"application":{"id":2071141,"council_reference":"P21/1902","date_scraped":"2021-08-06T02:11:05.000Z","address":"5 Matthew Street Mccrae VIC 3938","description":"Development of a double storey dwelling","info_url":"https://epeninsula.mornpen.vic.gov.au/P1PRPROD/P1/eTrack/eTrackApplicationDetails.aspx?ApplicationId=P21%2F1902\u0026f=%24P1.ETR.APP DET.VIW\u0026r=P1.WEBGUEST","comment_url":null,"date_received":"2021-08-04","on_notice_from":null,"on_notice_to":null,"lat":-38.355574,"lng":144.938677,"authority":{"full_name":"Mornington Peninsula Shire Council"}}},{"application":{"id":2068983,"council_reference":"P19/0542.01","date_scraped":"2021-08-05T02:09:51.000Z","address":"13 Poole Street Mccrae VIC 3938","description":"Development of dwelling alterations and additions and associated works in accordance with the endorsed plans","info_url":"https://epeninsula.mornpen.vic.gov.au/P1PRPROD/P1/eTrack/eTrackApplicationDetails.aspx?ApplicationId=P19%2F0542.01\u0026f=%24P1.ETR. APPDET.VIW\u0026r=P1.WEBGUEST","comment_url":null,"date_received":"2021-07-21","on_notice_from":null,"on_notice_to":null,"lat":-38.354865,"lng":144.9361654,"authority":{"full_name":"Mornington Peninsula Shire Council"}}},{"application":{"id":2053341,"council_reference":"P21/1769","date_scraped":"2021-07-26T12:27:57.000Z","address":"16 Parkes Street Mccrae VIC 3938","description":"Dwelling additions","info_url":"https://epeninsula.mornpen.vic.gov.au/P1PRPROD/P1/eTrack/eTrackApplicationDetails.aspx?ApplicationId=P21%2F1769\u0026f=%24P1.ETR.APP DET.VIW\u0026r=P1.WEBGUEST","comment_url":null,"date_received":"2021-07-20","on_notice_from":null,"on_notice_to":null,"lat":-38.35321649999999,"lng":144.9386219,"authority":{"full_name":"Mornington Peninsula Shire Council"}}},

[arnelgp]

it is a json file.
google json parser vba, so you can extract
data you need.

[p45cal]

You can try the attached, it's not very polished but it may work.
In cell J1 (which is a single cell named range called FilePathName) enter the path and file name to your text file.
Then right-click the table at cell A1 and choose Refresh.
That's it.

ps If it is a json file as arnelgp suggests, there is a built-in json parser in Excel, but it didn't work for me, but that's probably because I made the text file from your msg#1 content; if you attach an actual text file (you may have to zip it to upload it) then I could try again.

[mattalex]

Thanks p45cal! I tried it and it kinda worked. It updated the table with correct data, but the table was limited to 43 rows whereas my text file has hundreds of entries. Is there a way to have it auto-resize the table according to the text file data size?

For some reason it won't let me upload the text file (gives an error). If you provide an email address I could email it to you?

Thanks

[p45cal]

but the table was limited to 43 rows whereas my text file has hundreds of entries. Is there a way to have it auto-resize the table according to the text file data size? The result table should resize itself; there is no 'limit' except for the number of rows on a sheet (1 million+).
later edit: Yes there is, I made a mistake. Upoad a file anyway.


For some reason it won't let me upload the text file (gives an error). If you provide an email address I could email it to you?The site doesn't accept text files. As mentioned, you'll have to zip it first.

[snb]

Rather straightforward:


Sub M_snb()
sn = Split(CreateObject("scripting.filesystemobject").opentextfile("G:\OF\p45cal_json.txt").readall, ",")

ReDim sp(UBound(sn), 1)
For j = 0 To UBound(sn) - 1
st = Split(sn(j), ":")
sp(j, 0) = st(0 - (UBound(st) = 2))
sp(j, 1) = st(1 - (UBound(st) = 2))
Next

Cells(1).Resize(UBound(sp), 2) = sp
UsedRange.Replace Chr(34), ""
UsedRange.Replace "{", ""
UsedRange.Replace "}", ""
End Sub

[mattalex]

The result table should resize itself; there is no 'limit' except for the number of rows on a sheet (1 million+).
later edit: Yes there is, I made a mistake. Upoad a file anyway.

The site doesn't accept text files. As mentioned, you'll have to zip it first.


ok please find zip file attached. Thanks

[arnelgp]

put your this workbook to same Folder as your text file.
https://www.dropbox.com/scl/fi/zf007au2gmu3q20wkbj6i/Test-JSon.xlsm?dl=0&rlkey=yjovpck723mr78rqdf8yla38v
goto VBA and run Main sub. make sure you Enable the macro.
make sure you replace "test.txt" on Main sub with the name of your textfile.
the result is in "result" sheet.

[p45cal]

Using the built-in json parser worked perfectly with your test.txt file.
So, as before:
In cell J1 (which is a single cell named range called FilePathName) enter the path and file name to your text file.
Then right-click the table at cell A1 and choose Refresh.
Please note there are no On notice to and on notice from rows because all of them were blank; had any not been blank, the rows would have appeared.

As an aside, this is a Power Query query which contains a few steps, if you look at the steps you'll find it's the very last one which converts the data to as you want it (2 columns of data), but I suggest you explore the possibilty of scrubbing that last step. I did this on a duplicate query on sheet vbax69132b, then I made a pivot table from that table at cell P2. Where you might find this useful is that if you select, say the address cell (P6) then use the drop down in cell P2 to filter for 37 Cook, you can quickly bring up all the 37 Cook Street records next to each other. Useful?