I was going to start a discussion and solicit feedback, then I realized I could just as easily create a blog post and have the discussion in the comments section. So here we are.
What's this about?
Part of staying on top of my work is keeping up with Known Issues in Blackboard. A lot of times, I don't have time to log in to BtBb and look things up, so what I'll do is keep a copy of the Known Issues spreadsheet on my workstation for quick reference. This blog post explains the hows and the whys, and also goes into what I do with the data once I've got it.
Getting the Data from BtBb
Once the Known Issues page is loaded, you'll want to narrow down the results a bit. To do this, click the "Release" dropdown and select your environment.
NOTE: There are only two options available, 9.1 and SaaS. If you don't know which of these is applicable to your institution, you probably shouldn't be logging into BtBb in the first place.
So as the image to the right will attest, I've selected my release (SaaS), and my Product (Learn) and Article Type (Known Issues) were selected by default when I clicked on the "Known Issues" link from the BtBb main page.
So once I've selected my Release, I click on "Export to Excel" to download the data. (The "Export to Excel" icon is on the upper region of the page, on the right-hand side, hidden in plain sight, as it were.)
Your browser will download the file, and depending on your browser and system configuration, you may or may not be prompted to do something. My machine is so accustomed to me doing this that it doesn't even bother me when I go to download pretty much anything from Bb.
We've got data, 'cause we've got a band.1
Now that the XLS file has been downloaded to your workstation, you're ready to get to work and do all sorts of magical things. So locate that file on your machine and open it in Excel.
When you try to open the file, Excel will return an error, indicating that the file format and extension don't match and asks if you want to open it anyway. Click "yes" to ignore the error and open the file.
That, my friends, is something they didn't teach you at Monument University.
When you download the .XLS file, there's a surprise hidden inside.
The spreadsheet from BtBb is NOT an XLS file at all, but rather an HTML document with a .XLS extension. Excel can open it and present the data because it’s HTML and it’s laid out in tables, so Excel says “oh. Ok. I can figure this out.” and it goes from there. But because it’s not “real” Excel data, it’s hard to work with (and that’s also why it’s always got that big ugly line of code in the top row).
To see for yourself, download the file from BtBb, change the extension to .HTM (or .HTML), then open the renamed file in your favorite text editor or browser. It will look okay, but it’s still not quite right. If you copy and paste that text into Excel, you’ll have a lot of blank rows and your sorts will be wonky as a result.
The next section shows here’s how to fix that.
Making Sense of the Weird Exported Data
Open the HTML document in a text editor (There are tons of them out there. I'm a fan of Atom because it's cross-platform. But I confess, if I'm on a PC, I'm partial to Notepad++).
PART ONE - CLEAR YOUR HEAD
Once you've opened the document, delete everything before the <table> tag. There's a lot. Most of it is in the <script>, which is actually the only thing you need to get rid of, but it's simpler just to get rid of everything rather than look for the start and end of the <script>, especially if you're not used to working with HTML code in a text editor.
Now we are left with a document with no HTML heading information. This would be a terrible practice for actual HTML coding, but we're not doing actual HTML coding for publication, we're just cleaning up a mess. Your browser will still read it without issue (browsers are smart like that).
PART TWO - ELIMINATE THE EMPTY PARAGRAPHS
If you saved the file at this point and opened it with your browser (or with Excel), you'd see that the garbage at the top was gone. But we want to do something else before we save it. What you can't see in the browser is that there are several <p> elements in the code that would cause Excel to render the data as a blank row. This isn't cool. So you want to eradicate all of these.
So... do a find/replace for <p>, replacing each instance of <p> with a delimiter of your choice (I like ; in this instance, but that’s just me).
Save the file.
PART THREE - GETTING USABLE DATA INTO EXCEL
We're not quite done. Now that you've saved the HTML file, open it in your web browser of choice. Magically, all of the extraneous rows are now gone. Now, copy all of that data in the table (the one in your browser) into a blank Excel document. Depending on your computer, it may take a bit, as there is a lot of data there.
However, if you check out the Excel spreadsheet, you'll notice that the data is clean and much easier to work with.
So save the Excel file, and you now have a copy of the Known Issues saved locally. YAY!!
Wrapping it up
This isn't the end, but it gives you something to work with. The real tricky part comes when you want to make those dates something you can actually use, because at the moment, Excel is treating that data as generic text because it doesn't know any better.
If there's interest, I'll write a follow-up piece to this about how to clean up the date data so that it actually means something.
I will confess, this is a clunky, labor-intensive method, and I’ll come up with a more elegant method eventually, but for the moment, it gets the job done.
1Okay, it should be noted that while I'm writing this, I'm watching a live Phish concert (on the web, not in person), hence the oddball headers and occasionally goofy tone of the article).