The Men & Mice Blog

Why spend 5 hours entering data manually when you can spend 5 days writing a script that will do it in 5 seconds?

Posted by Sigfus Magnusson on 3/1/10 10:13 AM

Back in the early days of Men & Mice (when Men & Mice SuiteMicrosoft Excel Files wasn't really a "Suite" and was simply called QuickDNS) we used a parser/lexer thingy that was really nice but the syntax was a bit odd.  

When reading about the parser from the author, I saw this great quote which read something like the title of this entry.  The morale of the story was that entering data manually is a tedious process and it is much more fun to write a script that will do it automatically, even if it takes much more time than just doing it the hard way!

I was reminded of this today when I was importing data into our tool at a customer site.  I asked them whether they had a spreadsheet with their subnet data, and the response was "Do we got spreadsheet(s)!!".  They gave me their spreadsheets, all 650 of them.  

There were two choices:

a) Tell them how to do it themselves
b) Write a script that would do this for them

The first option would mean a lot of work for the person maintaining the spreadsheets.  
Open a spreadsheet in Excel, filtering out the appropriate data, exporting to csv and then importing into Men & Mice, or even just copy and paste between the tools.  Not yummy.

So I chose the second option.  

My favorite tools for these imports are Python, bash and of course BBEdit.  I began by searching for something that would allow me to convert the Excel spreadsheets into csv files and I found this wonderful Python library called xlrd (http://www.lexicon.net/sjmachin/xlrd.htm). 

According to the author, this is "[a] Library for developers to extract data from Microsoft Excel (tm) spreadsheet files".  And it worked like a charm.  

I wrote a nice little script that parsed all the Excel files and created two csv files, one with all the subnets and another with all the device data, which I then imported into Men & Mice using the "importdata" command in the command line.  Yummy!

Topics: csv files

Why follow Men & Mice?

The Men & Mice blog publishes educational, informational, as well as product-related material for everyone and anyone interested in IP Address Management, DNS, DHCP, IPv6, DNSSEC and more.

Subscribe to Email Updates

Recent Posts