An odd request came in from a faculty member the other day. As part of their research they had 60 or so CSVs that they wanted combined horizontally. I have many ways to do it vertically but this was a new one. Each file was around 350KB so they weren’t huge and 60 isn’t a ton but it’d be a hassle to do by hand and ripe for accidents. I Googled a bit. Saw some stuff using PHP and array_merge, saw some people using Pandas in Python.1 I talked to Jeff and he mentioned uploading them to MySQL and then doing a join off the common ids. All good and reasonable paths but they just didn’t seem like things I wanted to do. I then saw that Amit2 had a script for importing CSVs into Google Sheets. His import wasn’t meant to do quite what we were doing but it gave me enough momentum to create the script below. The first function3, just loops through all the files in a folder you indicate through the folder ID. It calls the second function which imports the CSVs. The key was changing the first line to the second. It takes a bit to run. The first time it got through maybe 45 sheets before Google got upset about how many cells I’d […]
flickr photo shared by OSU Special Collections & Archives : Commons with no copyright restriction (Flickr Commons) As part of a future project, I’m looking for easy, automated ways to push/pull CSV files around. Initially I thought I’d just do the =importdata(‘http://theurl.com/data.csv”) function but I realized that had some drawbacks that made it less ideal which lead to the script below. Using Google Script triggers this script could be set to retrieve a CSV file every X amount of time and write it to a Google Sheet. The upper portion is adapted from this answer. In any case, it opens up some decent automatic options and would keep data fresh for easy access charts and graphs in Google.
flickr photo shared by The Library of Congress with no copyright restriction (Flickr Commons) The title just rolls off the tongue, yes? But what it does is kind of interesting. It’s not a drag/drop solution but it starts to show how we can better knit different services together. In this case, I run a Google Script to generate a spreadsheet of files (I can probably make that a straight up API connection in the near future). I publish that spreadsheet as CSV because Google sheets to JSON feels a good bit slower.1 Then the following shortcode (but w/in square brackets) generates the little unordered lists below. It’s taking that list of 464 files and just chopping out the ones where the filename’s first letters match A-1 and the parent folder is Tasks. Not brain surgery, right? But it is a pretty flexible solution to allow flexible access to any chunk of CSV (should work on any CSV on the web- assuming you adjust the elements to reflect the data) text you want to display portions of on the fly. People can do all their work in Google Drive and it seamlessly updates in WordPress. People without WordPress skills can update and change where these lists appear through a simple shortcode. To be clear, this is an integration rather than an […]
flickr photo shared by Thomas Hawk under a Creative Commons ( BY-NC ) license This is in response to something Adam Croom wrote two(?) days ago. I thought it’d be an interesting proof of concept and would let me figure out some things with a purpose. I also like to have a few projects going on at once so I have things to switch between when I get frustrated. I also see this kind of information pushing/pulling as broadly applicable. Some of this stuff is no doubt uglier than it had to be but I’ll try to show some intersections that happened to occur with other projects and how certain steps might be ignored entirely if you want to be all efficient and stuff. The final plugin is here and should be a decent start to any customized import you want to run against a CSV file. Adam had information in Discogs. He wanted that information in WordPress where he could control it. I had never heard of the site, let alone seen its API. But it well documented and it took me a few minutes to realize I could get all the data I needed without even needing to authenticate. The user data was associated with collections and appending 0 would get me the root level stuff. With Adam’s […]
Functional is a kind way to describe this . . . but it’s hard to argue with computers doing tedious work that was previously done by hand. The goal here was to automate the collection of the comments on 300 or so posts from the CDC’s Facebook page so they could be analyzed. Ebola is in the mix which leads to some comments that may very well rival YouTube comments in terms of causing me sadness. We did end up with around 34,000 comments which would have been hand copied in the past. So some karma balance was achieved. wTake all this with a grain or two of salt. It works but may very well be illegal in some states or do things in a way that would make real programmers weep. Consider my programming skills to be like a man trying to swat flies with a broomstick- lots of furious action, panting, cursing, and every so often I will kill a fly and celebrate wildly.1 The Facebook This was my first experience trying to do anything with Facebook. The hardest part here for me was just getting the stupid access token needed to get at the JSON feeds. I eventually found the secretly named Facebook Login Example. Part of my issue was that I thought I could skip the […]