Macro to export all sheets in a Mac Excel file to CSVs

I found the below code and it is a life saver. Saves time too! No more spending hours with the hated Microsoft ‘Are you sure you want to do what you want to do?’ dialogue box loop. Grrrrrr.

Enjoy.

Sub AllSheetsToCSV()
For Each s In Sheets
s.Activate
ActiveWorkbook.SaveAs Filename:=s.Name, FileFormat:=xlCSV
Next s
End Sub

To use: Open VBA editor. Create new module. Paste code into module. Hit play.

And it does just work. Many thanks to ‘pompomtom’ who posted it in ask.metafilter.com

Original link posted by pompomtom at 5:01 PM on July 22, 2013 in ask.metafilter.com

An example of Tower Hamlet’s council’s approach to Open Government

Real life is always stranger than fiction.

Today I finally managed to get myself to sunny Shoreditch for an Open Data Institute lunchtime lecture.

This Friday the talk was “How can local government deliver better services with fewer resources using data?” given by Ben Unsworth of Socrata.

Talk was great, attendance was great, met some interesting people.

I think I may have briefly mentioned that the possibility of Tower Hamlet’s council in any way embracing the notion of Open Data let alone Open Government were approximately zero.

And I may have outlined my strategy which is ‘guerilla open data’ and is in the fine traditions of the Open Data movement. Use the law to rip Open Data from the hands of those who want to keep it Closed Data and publish it for everyone to see.

After buying a new mouse I returned home to sunny Wapping and the first tweet I saw when checking my timeline was this:

Ted Jeory@TedJeory Tower Hamlets council’s new commitment to transparency in action (scroll through the document) modgov.towerhamlets.gov.uk/documents/g541… >>must be the date”

Click on the link to that PDF document.

Page one is grandly titled ‘MAYOR’S EXECUTIVE DECISION MAKING’, Mayor’s Decision Log No.62. The subject is ‘POPLAR BATHS & DAME COLET HOUSE – FINANCIAL CLOSE’ and there are 38 pages in all.

Popcorn ready? Sitting comfortably? Here’s a precis of the document if you couldn’t be bothered to click on the link.

  • This page is intentionally left blank
  • Agenda Item 1. By virtue of paragraph(s) 3 of Part 1 of Schedule 12A of the Local Government Act 1972. Document is Restricted
  • This page is intentionally left blank
  • Document is Restricted
  • This page is intentionally left blank
  • Document is Restricted
  • This page is intentionally left blank
  • Document is Restricted
  • This page is intentionally left blank
  • Document is Restricted
  • This page is intentionally left blank
  • Document is Restricted
  • This page is intentionally left blank

See the pattern here? 38 pages of whiteness that a polar bear would feel at home in.

And that ladies and gentlemen of the Open Data and Open Government communities is what Tower Hamlet’s council thinks of you.

But don’t worry, it treats residents of our great borough with even more contempt.

Note: I should emphasise that the document was discovered by @TedJeory, not me. Well done Ted. Will there be a sequel? 

 

 

 

Tower Hamlets Council payments to suppliers over £500

Open data? Open government? How is that relevant to me?

Simple. It’s your money. If you lost a tenner you would want to know where it went. Same principle applies to local councils. You want to know where the cash goes.

Below you can find links to files that contain details of payments over £500 by Tower Hamlets Council to its suppliers. That’s around 158,000 transactions between 2010 and 2013.

Handy, huh?

Scrubbed data for download (CSV)

(Updated Wednesday 16th April 2014. These datasets will be migrated to Openspending in future. )

Notice: These files contain public sector information licensed under the Open Government Licence v2.0.

You can find all the original spreadsheets and Comma Separated Value (CSV) files on the Tower Hamlets Council website. (A word of warning. The original data is held in 23 Excel spreadsheets. And 177 CSV files. )

But how is this (a) legal and (b) possible? Quite simple really.

“The public should be able to hold local councils to account about the services they provide. To do this, people need information about what decisions local councils are taking, and how local councils are spending public money.”

That’s what the Department for Communities and Local Government (DCLG) wants and who am I to argue?

Indeed our very own Council reinforces the fact that we as residents should have a poke around. This is what Tower Hamlets says:

“One of the benefits to the community in publishing this data is that it means that our spending will come under greater scrutiny by local people. We hope it will inform people about what we do, and encourage people to challenge how we spend money. It is your money and we welcome comments.”

So in December 2013 I thought I would have a look at the data Tower Hamlets Council published and see if there was anything interesting in it.

And I am still wondering.

Because as with many things in life having a look at this ‘open data’ was not quite as straightforward as it could be.

Scrub a dub dub

Those with a leaning towards geekishness will know all too well what I mean when I say I spent a huge amount of time ‘data scrubbing’.

For those without pen pocket protectors, data scrubbing means taking raw data (the stuff in the Council’s files) and going through every cell in every file of all 119,000 + records and getting it into a basic clean format that is usable.

Sometimes you can write programs to do this, other times you use Excel, sometimes it comes down to a very boring manual task. Bottom line is everything needs to be nice and tidy.

That’s why you will find links to only seven files on this web page compared to 200 on the Tower Hamlets Council site.

The only think I have added to the files is a unique id number. I would have used the transaction number but there seems to be some duplication in the original data.

Fido! Breakfast time!

In fact the original data is a mess. The proverbial dogs breakfast.

Different fields. Different names for fields. Varying file names. No consistency in file names. Data values transposed. Descriptions truncated. Nasty.

A less charitable person than myself might suspect that Tower Hamlets Council was trying to conceal something. But obviously that is not the case. I mean, what on earth would they have to hide?

I think the exceptionally poor quality of the original 200 data files is just down to attitude.

The Council might publicly state that they”encourage people to challenge how we spend money” but my experience of trying to do just that indicates the opposite.

Before I started to write this I was going to sum this up as Tower Hamlets Council only abiding by the spirit of open data. But that is not correct.

Just by dumping a couple of hundred files onto a website does not comply with either the spirit or the letter of the guidelines to Local Councils for publishing data.

Yes Tower Hamlets have published details of all spending to suppliers over £500. But the only people who can make use of it are those people like me who have the experience, skills and lack of a social life to spend the time sorting the data out and turning into information.

And that is why these files are published here.

Spot the problem

Just to add insult to injury, even once the data has been scrubbed and turn into nice clean files that can be imported into a MySQL database the information in them is somewhat opaque.


A random example of Tower Hamlets spending data

Directorate Service Division Responsible Unit Expense Type Payment Date Transaction Number Net Amount Supplier
Communities & Localities Cultural and Related Services Culture & Heritage Community Services Grants Voluntary Associations 24/12/2012 5990563 £3420 OSMANI TRUST

Spot the problem with this? The transaction record tells you everything apart from what the Osmani Trust was paid £3,420 for. Eggs? Rockets? Squirrel hats? Who knows. Not us.

We can at least see who has been paid. We do not know what they have been paid for.

The Council must know. But for some reason have decided not to reveal this. Remember that all this data is just a report from some big and nasty computer system in the Town Hall that spends all its time just tracking money.

So it should know what it’s suppliers have been paid for.

But this record – and the other 118,999 records published – does not have the fundamental information Tower Hamlets residents need.

What does Tower Hamlets Council spend our money on? We still don’t know. It seems that other Councils only publish spending transaction to this level of detail which is not much use. 

So get challenging people!

Of course having converted this spending data to an accessible format and published it for you all to enjoy I thought I might do some digging and challenging myself. More on this in future posts.

Fortunately for me I have the Wapping Mole to help me out.

For more information:



This material is Open Data