Import json file into Excel

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
Hmesser
Posts: 3
Joined: Fri May 15, 2020 10:14 pm

Hi - I'm using the rpscrape tool to generate a json file containing racecard data from the racing post. I want to then import this data into Excel. I've tried various methods (with my limited knowledge in this area). Have had some success conveting the json into an XLSX file online and then using some VB to strip out the unwanted columns. But this is quite messy/time consuming and was wondering if anyone was already using this tool or if there was a developer I could approach to get a quote for the right power query code for Excel. Grateful for any advice. Rob
User avatar
ODPaul82
Posts: 683
Joined: Sun May 08, 2011 6:32 am
Location: Digswell Herts

I personally use the following class module.
https://github.com/VBA-tools/VBA-JSON

Items get returned as a dictionary object (which is pretty much key/pair)

My approach is to fetch the JSON via HTTP Connection, put that into a string and then process as appropriate.
Hmesser
Posts: 3
Joined: Fri May 15, 2020 10:14 pm

ODPaul82 wrote:
Sat Dec 03, 2022 9:37 am
I personally use the following class module.
https://github.com/VBA-tools/VBA-JSON

Items get returned as a dictionary object (which is pretty much key/pair)

My approach is to fetch the JSON via HTTP Connection, put that into a string and then process as appropriate.
Hi - thanks for this. I did find that and set it up. I think it might be a bit beyond me though as I would need to craft the VBA to transform the data and my scripting capability is quite basic. For output I only really want runners info for each race as a row in a table. I'll have another look at that module today but think I might need to pay a developer for some time to produce it. Appreciate the response. Good to know what I'm after is probably possible.
sionascaig
Posts: 1053
Joined: Fri Nov 20, 2015 9:38 am

Did you try the Import JSON direct to Excel ==> Data/Get Data/From File/From JSON?
Hmesser
Posts: 3
Joined: Fri May 15, 2020 10:14 pm

sionascaig wrote:
Sat Dec 03, 2022 5:29 pm
Did you try the Import JSON direct to Excel ==> Data/Get Data/From File/From JSON?
Hi - I have but as the json file is nested I need to create a powerquery command to import the data correctly. By using the powerquery gui I can import a single runner into a table but don't know how to automatically do this for all. I've tried various online convertors to generate an xlsx with the json data in. I've found one that does a decent job but its mangled the data all over the workbook. At the moment its easier for me to de-mangle the data in Excel which I'm ok with - versus powerquery which I'm a bit clueless at.
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”