Category Archives: Data Journalism

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

Mac OS X databases for data journalism

Guess what? If you dive into the murky waters of data journalism you are going to have a lot of data to manage. NSS. 

Excel Mac sucks

For my latest project I have been doing the usual Excel thing but it’s not much fun. One reason is because Excel Mac sucks big time. I have seriously considered getting a Windows PC for the sole reason that Excel PC is better.

But I haven’t gone down that road yet.

Whatever your preferred initial data scrubbing tool at some point you are going to end up with lots of data on your Mac that you need to interrogate on a regular basis. So you need a database.

Lots of messy data

My current project uses graph databases for Social Network Analysis (SNA) but way before I get anywhere need nodes and edges I need to consolidate lots of messy data from different sources. So its relational database (RDBMS) first then off into SNA land with Gephi.

Again being a Mac OS X user your choice of RDBMS is limited.

I do have a copy of Bento sitting on a shelf and that would probably do the job for starters but, in true Mac software fashion, it has been discontinued.

After some Duck Duck Go’ing and a little Googling I have come up with these three candidates.

Not sure which one(s) I will try as yet so will report back. My main requirements for a database are:

      • It works
      • The interface doesn’t look like a kidnapper’s ransom note
      • It works
      • Ideally exports JSON (I know there is an add-on for Sequel Pro for this)
      • You don’t have to have a degree in computing to use it (I have a couple but prefer to keep them for emergencies)
      • Donation-ware (if I use it I will pay for it, if I don’t I won’t), ideally Open Source

Gephi can be hooked up to MySQL so that’s a huge advantage for using this sort of software.

For the time being I am off to Data Scrubbing in Excel Land.  I spent Christmas morning there and it wasn’t too bad to be honest.