Software dev, tech, mind hacks and the occasional personal bit

Category: RiskAssess

Importing Excel 365 CSVs with Ruby on OSX

Up until 2016, Excel for the Mac provided a handy CSV export format called “Windows CSV”, which used iso-8859-1/Windows-1252 encoding. It was reliable, handled simple extended characters like degree signs, and could be read in Ruby with code like:

CSV.foreach(file, encoding:'iso-8859-1', headers:true, skip_blanks:true) do |row|
    ....
end

Unfortunately, support for this format was dropped in Excel 365. Many RiskAssess data files were in this format, as the earlier versions of Excel did not properly export to valid UTF-8.

Excel 365 now has a Save As option of “CSV UTF-8 (Comma-delimited)”. This is UTF-8.. with a few twists! Sometimes it includes a UTF-8 format first character, and sometimes not. Sometimes it includes the UTF-8 format first character plus a BOM (byte order mark), another invisible character. According to Wikipedia “The Unicode Standard permits the BOM in UTF-8 but does not require or recommend its use”. This makes it trickier to import. Code like this:

CSV.foreach(file, encoding:'UTF-8', headers:true, skip_blanks:true) do |row|
    ....
end

will handle the first 2 possibilities, but not the BOM. The BOM ends up as an invisible character in the data, causing trouble. It is possible to import with encoding ‘bom|utf-8’ which will handle this case. Another option is to run data through the dos2unix command (easily installed with brew) which does general tidying including removing the unnecessary BOM.

Also to watch out for, “Windows CSV” format previously used “\r” to denote new lines inside of cells. The new UTF-8 export uses “\n” for new lines inside of cells.

Doing a Website Re-design or new look

Having recently being updating the look of RiskAssess, I thought I’d share a few important things to remember.

About 4.5% of people are colour blind. This means a lot of your users! Make sure your site has sufficient contrast in the colour choices. Great tools are available online to help:

Stage the changes as much as possible. Do them incrementally rather than all at once. That way people have time to get used to them, and you have the usual incremental software development benefits like earlier releases with lower risk of bugs.

If you have an established user base, make sure the new design is recognisably connected with the old design, so people don’t feel it’s all changed.

Test on the hardware your users use, and design for it. A design with fancy fonts and subtle colours might look good on a big Retina iMac, but how will it look on old low res LCDs and small netbook/laptop screens?

What are the demographics of your users? If they skew older, then heavier fonts, more contrast etc may be vital for readability.

As always, test on target browsers, screen sizes, mobile, tablet etc to ensure all your users have a good experience.

Ask some of your users for feedback! Yes, really! If you’ve got an established site, you really need to make sure you will be delighting people, not annoying them with a new look. Even if it’s only CSS changes, there’s likely a lot you may not have thought of that your users will spot right away that you’ll want to take on board.

Warn all your users that the change will be happening. Provide screenshots, explain why the new look is better for them, and give everyone the chance to check it out and give feedback and get ready for the change.

Encourage people to give feedback once the new design goes live. Take it on board humbly (even if it hurts), and react quickly to fix any accidental losses of functionality or oversights. Much better if users tell you what they don’t like so you can fix it.

RiskAssess – Risk Assessments for Schools

I’ve been developing RiskAssess, an online risk assessment system for schools. RiskAssess helps teachers and lab assistants to quickly and easily produce risk assessments for laboratory experiments which meet Australian standards. RiskAssess should help decrease the number of accidents in school laboratories, and also help schools meet legal requirements.

I’m pleased to say that the site is now well and truly launched and we have schools doing risk assessments every day using RiskAssess. There’s still much to do and many new features to add, but so far we’ve had very positive feedback from the schools using the system.

On the technical side, RiskAssess is a Ruby on Rails site.

Powered by WordPress & Theme by Anders Norén