Work flow complete before bottom of second cup of coffee.

[Ocean West]

 

The task at hand was finding an API for one of our clients that could provide a comprehensive list of their clients stores. The goal was to cross-reference this with our database, ensuring each store was accounted for. Despite my efforts, the search came up empty. However, their website presented an intriguing alternative: a detailed directory segmented by state and country.

My initial approach was to examine the website's HTML, yet this only yielded the default data. Attempts to append the URL to load data from different countries proved futile. The website's dropdown appeared to use JavaScript to pull varying data sets. My initial assumption was that each selection triggered a new query. However, upon closer inspection, I discovered a JSON object nestled just before the closing body tag. Rather than querying anew each time, the JavaScript simply drew from different arrays within this pre-loaded object.

Enter ChatGPT, OpenAI's conversational AI. I enlisted its help in creating a Python script to extract the JSON object from the specified tag and write it out, beautified, to a file in the same location as the script. The result was nothing short of exquisite.

The resulting JSON object presented a complex structure with several layers of arrays containing nested objects. Using Beautiful Soup 4, I instructed the script to use pandas to translate the object into a pipe-separated format. This process involved adding extra columns to retain data from each hierarchical level, along with ensuring column names remained unique.

ChatGPT executed this flawlessly, producing a pipe-separated file that was easily ingested by FileMaker (FM). However, breaking down the task into manageable steps was crucial as it prevented overwhelming the AI. Once everything was in place, I requested ChatGPT to combine these steps into a single script. Again, the AI delivered as expected, executing all tasks with just one call.

Looking ahead, I envisioned automating this process to run monthly as a cron job, integrating the data into FM in a truly 'Otto-magical' fashion (pardon the pun!). After a brief setup on my end, I returned to ChatGPT with another request. This time, instead of writing the pipe-separated data to a file, the AI was tasked with setting the data to a JSON object named "payload" and transmitting that payload to a webhook for immediate processing through Otto and SimpleQ.

With a bit of tweaking to preserve foreign characters via UTF-8 encoding, the process was completed. Remarkably, it only took about 30 seconds to run the script, fetch the JSON object (containing over 7k lines of data) from the URL, convert it to a pipe-separated payload, send it to a webhook, and then publish it to my file. This would then truncate the table, write out the payload to the server's document folder, and import the records into the system.

Now, the data is ready for final evaluation and additional business logic processing. It was an intriguing journey, demonstrating the synergy between AI and human ingenuity.

This Entry was enhanced by ChatGPT - original message follows.

Cheers!

Spoiler

I had a task to find an api one of our clients to get a complete full list of their stores so we can compare that they exist in our database. I couldn't find this. However on their web page they have a director by state and even a selector by country. 

I started the task by looking at the html of the site but could only get the page's default data I couldn't just append to the url to load a different country, it would seem the drop down ran js to pull the different set of data. I my mind I thought it was doing another query but after looking further just before the closing body tag there was a json object with ALL the data there so the JS was just pulling different arrays out of the object. 

Enter ChatGPT, I asked it to write me a Python script to look extract the json object from that tag and write it out prettified to a file adjacent to the script. And it did so just beautifully.

After a few rounds of looking at the JSON object which has a few hierarchies of arrays of other objects with arrays of objects.

By using BeautifullSoup4 I had it use pandas to convert the object in to pipe adding extra columns to preserve data from each level of the hierarchies. Also asking to make sure column names are unique. 

It wrote out the file just perfectly I took and dropped it on FM and it converted I had all the data. 

I had ChatGPT do this in to separate tasks as it seems to get overwhelmed with too much all at once. After I got everything working I asked it it could combine these in to just one script - again it did so and with one call it did everything as expected. 

Thinking to myself eventually we want to run this once a month as a cron job and get the data into FM in "Otto-magicaly".   - you see what I did there?

After a few minute setup on my side, I returned to ChatGPT and asked it to take the piped data instead of writing it out to a file just set it to a json object called "payload" and send that payload to a web hook which processes immediately  ( via Otto and SimpleQ ) giving the url to the AI and indeed it did.

Had to play with some encoding on importing to utf-8 to preserve foreign characters. All with in ~30 seconds or less it from running the script, it has gone out and fetch a json object (over 7k lines of data ) from a url converted it to a pipe separated payload and sent it to a webook which then published to my file where it truncates the table and writes out the payload to the servers documents folder then imports the records in to the system. 

Ready for final evaluation and processing extra business logic.