Tuesday, 4 February 2020

How to extract a table from html and save to csv (web to spreadsheet)

WebScraper users have sometimes asked about extracting data contained in tables on multiple pages.

Tables on multiple pages

That's fine if the table is for layout, or if there's just one bit of info that you want to grab from each, identifiable using a class or id.

But to take the whole table raises some questions - how do you map the web table to your output file? It may work if you can identify a similar table on all pages (matching columns) so that each one can be appended and match up, and if the first row is always headings (or marked up as th) and can be ignored, except for maybe the first one.

It's a scenario with a lot of ifs and buts, which means that it may be one of those problems that's best dealt on a case-by-case basis rather than trying to make a configurable app handle it. (if you do have this requirement, please do get in touch.)

Table from a single page

But this week someone asked about extracting a table from a single web page. It's pretty simple to copy the source from the web page, paste it into an online tool, or copy the table from the web page and paste into a spreadsheet app like Numbers or Excel and that was my answer.

But this set me thinking about the job of parsing html and extracting the table data ready for saving in whatever format.

At the core of this is a cocoa class for parsing the html and extracting the table (or tables if there are more than one on the page). With a view to possibly building this into WebScraper to allow it to do the 'tables on multiple pages' task, or for having this ready, should the need arise to use this in a custom app for a one-off job, I've now written that parser and built a small free app around it.

That app is the imaginatively-titled HTMLTabletoCSV which is now available here.

Wednesday, 29 January 2020

Upgrading the hard disk in a G4 iMac

I bought this iMac new in 2003 and used it as my main machine for some time. I was writing software at the time, had a number of products out, but it wasn't my full-time business then. Eventually it was packed away to make room for newer machines.
Isn't it beautiful? OSX 10.4 is a really great version of the operating system, and this is the time when iTunes was at its height. I'd long had it in mind that it would make a great jukebox for kitchen or bedroom, with its great Harmon Kardon speakers with optional subwoofer. 

To be useful again, mine needed:

  • Wireless card (I never did fit one)
  • Hard drive upgrade* (the standard 80Gb one isn't big enough for half of my music collection)
  • Replacement CD drive** (mine had broken somewhere along the line)
  • New battery (small alkaline battery for keeping the time and date)

The parts I expected to be hard were easy, and the parts that should have been easy were hard. I'm documenting my experiences here because there are some things that may be useful to others.

I'd already collected some of these parts, intending to do the work one day. Last week something reminded me of Cro-Mag Rally. It's a great game and the Mac version is now free, thank you Pangea. The OSX version needs a PowerPC mac, and my clamshell iBook proved too slow. so out came the G4 iMac.
 It wasn't remembering the time, which of course means that the battery is dead, and a battery inside an almost 20-year old computer is bad news. So perhaps the time was right to carry out those other upgrades at the same time.
The wireless card and adding RAM are pretty much a trapdoor job (well, behind the stainless steel cover, which has 4 captive crosshead screws. Very simple.) No worries if that's all you have to do.

NB, 10.4 (Tiger) allows connection with WPA2, which is what I needed at home. and the Airport Extreme is working fine. 10.3 and earlier don't have the WPA options, only WEP. Whether those earlier systems can be made to work with a modern router, I can't say.
I had to go deeper. You need to go one layer further even to replace that battery.  This is the new hard drive and replacement CD drive. The latter came from an eBay seller, used but working (allegedly). The HD and my new battery are from the BookYard, I recommend them (as a UK buyer), their website makes it easy find the right parts for your particular machine. The service was efficient and swift.
I have read that delving more deeply into the machine is an advanced task and I was expecting it to be harder than it really was. It's actually very simple. I'm not going to give instructions, there are plenty elsewhere. That's the old CD/HD assembly coming out, note the horrible amount of dust in there. With those out, it's possible to vacuum out the fan and all of the nooks and crannies, taking anti-static precautions.

One point of uncertainty for me was about the jumper settings for the new HD and CD drives. The replacement CD came with a single ribbon cable with a 'tap' halfway for the HD. The existing ones each had their own cables (there are two sockets on my board). I suspect that the CD drive came from a different model (there are several variations and three with 17" screens - 800Ghz, 1Ghz and 1.25Ghz.) Besides the difference with the cable, the little metal screen at the front was slightly different and I had to nab the that part from the old old drive.

Back to the jumper settings. The replacement CD drive came jumpered as a slave, which may be consistent with it being on the same ribbon cable as its HD. With my existing drives, each had their own cable and each was jumpered for 'cable select'. I decided to use my existing cables and jumper both drives for cable select as before. That worked.

NB - if you're going further than the trapdoor, there's some thermal paste that you must clean up and apply new. See the iFixit article for details.

 There's the replacement battery in and with everything back together, it chimed and as expected there's the icon for 'no startup volume'.
This is where the games really started. The new CD drive worked and I expected it to be easy to make a fresh OS install on the new blank drive, but I had a very frustrating time.

I have numerous discs from various computers. However, not all were readable and some were upgrade discs rather than install. Those that I downloaded from the web and burned to CD were either not bootable or simply would not boot. (If you're reading this and know why, please leave the answer in the comments.)

I still had the drive that I'd taken out, and 'restoring' or cloning an existing volume to a new one should be very easy using Disk Utility, which comes on every system and every bootable install disc.

At first I opened up the computer again and plugged in the old drive in the CD drive's socket on the motherboard. This worked, I could boot from it and perform the 'restore'.

However, it didn't work first time - the clone on the new drive wouldn't boot. I had to try again and again. Because my first method was a little makeshift, I devised this - it's a firewire  'caddy' made from an old LaCie firewire hard drive. I opened it up, unplugged its own HD and plugged in my computer's HD and voila! It plugs into the reassembled computer and can boot the computer (you can boot from a firewire external drive but not a USB one).
The old drive had three different versions of OSX installed on different partitions. Back in the day this allowed me to boot into  OSX 10.2, 10.3 or 10.4. Very useful for testing my software.

My first attempts at cloning the 10.4 partition failed a couple of times. The first time I thought it was because I hadn't checked 'erase' before doing the copy. But I don't know why the second attempt wasn't successful.

I'd read mixed opinions about the maximum size for the boot volume with this machine. I thought this might be the problem for a little while but I can tell you that I have a 500GB drive with a single partition / volume, and that it now boots.

For my final attempt I tried booting into 10.3 (Panther) and using its version of Disk Utility. (This I think being the system that came with the machine). I don't know whether this made the difference, or whether it was because I reformatted the new drive at that point. (It had come apparently formatted, so I hadn't bothered before). That new clone of the 10.3 volume did successfully boot. After that I successfully used an upgrade disc to take it to 10.4, and then a multipart update downloaded from the Apple developer site to take it to 10.4.11

Now I have a mac with a system that really doesn't feel out of date, a version of iTunes (7) which is a joy to use, a 500GB drive, wireless (10.4 allows me to connect with WPA2). A useful and usable machine, and one which looks amazing.

* Why not a SSD? I had intended to do this for some time. But a ATA/IDE/PATA  SSD isn't so easy to find, and I read that the limitations of the ATA system mean that the speed benefit is lost. A brand new spinning disc is cheaper and still quiet. So that's what I went for.
** I refer to the drive as a CD drive in this post. Actually it's a combined CD reader / writer and DVD reader, known as a 'superdrive'.