Convert multiple Microsoft Excel files to .csv files with 3 clicks

I often get given Microsoft Excel files to import them into a database. Normally we’ll work with the files in the Excel to clean up data (phone numbers, Addresses, Titles, etc) but then save them as .csv files to do the actual import.

On my MacBook this is now an easy task because I’ve setup a ‘Service’ with Automator. In Finder I simply select the files I want converted, right click, select Services and select Convert Excel to csv files. Job done. Heaps quicker than doing them one at a time.

Screenshot of Convert Excel files to csv service in Automator
Screenshot of Convert Excel files to csv service in Automator
Screenshot showing service in use
Screenshot showing service in use

What are EPUB and PDF files, and which one should I use?

EPUB files are one of the most popular ebook formats. They have an advantage over PDF files (discussed below) in that they will reformat their content to the size of your screen. This means that if I view the book or document on a computer screen it will have more words per line, more lines per page, thus it may only have 10 pages. On a smart phone, that same document will have fewer words per line, fewer lines per page and it may have 100 pages. This is an advantage when reading on a smart phone as it means you can increase/decrease the text size to suit your eyes, and only have to turn pages, not scroll horizontally. EPUB files are viewable on almost all devices.

PDF files are one of the most popular format for sharing information. Their primary benefit is that they layout stays the same across all devices. This means that if the author of the book or document want words or pictures to appear in a specific place on a specific page, they will and all those viewing it will see it the way it was intended. This means that on a smart phone you’ll need to scroll horizontally and vertically to see the entire page. PDF files are viewable on almost all devices.

So, to answer the original question, which is best?

If you’re using a smart phone or tablet, I’d tend toward using EPUB files. If your primary use is on a computer/laptop and tablet then I’d tend toward a PDF.

Get SAMBA running on linux

This article here describes how to get SAMBA running on a Raspberry Pi.

The protocol, which is known as the Common Internet File System (CIFS) – but was originally called Server Message Block (SMB), has been implemented on other operating systems including Linux. The most popular implementation is known as Samba and it allows devices like the Raspberry Pi to act as a CIFS file server. To put it another way, it allows a Windows PC to mount a folder on a Raspberry Pi and then copy, delete, read and write files on the it.
Installing and configuring Samba on a Raspberry Pi for basic file sharing is quite simple.

How to fix ssh “Remote host identification has changed” Error

Trying to connect to a host via ssh and I’m getting an error:

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
@ WARNING: REMOTE HOST IDENTIFICATION HAS CHANGED! @
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
IT IS POSSIBLE THAT SOMEONE IS DOING SOMETHING NASTY!
Someone could be eavesdropping on you right now (man-in-the-middle attack)!
It is also possible that a host key has just been changed.
The fingerprint for the RSA key sent by the remote host is
[...].
Please contact your system administrator.
Add correct host key in /Users/benhamilton/.ssh/known_hosts to get rid of this message.
Offending RSA key in /Users/benhamilton/.ssh/known_hosts:2
RSA host key for [...] has changed and you have requested strict checking.
Host key verification failed.

The simplest way to fix this (that I’m aware of) is to edit the /Users/benhamilton/.ssh/known_hosts file and delete the second entry. Why the second entry, because the line in the error above tells us which line is the offending key at the end of the line.:

Offending RSA key in /Users/benhamilton/.ssh/known_hosts:2

I’m using Mac OSX, other flavours of *nix will have a slightly different path to the known_hosts file.

More info re this error can be found at Serverfault, MacWorld, and thegeekstuff.

Formatting of printed addresses

How should you format an address on printed labels and envelopes?

Formatting addresses for printing in Australia:

Typically, the address should be written in three lines:

  • The top line should contain the recipient’s name
  • The second to last line should contain the number and name of the street, PO Box or locked bag number (if applicable)
  • The last line should contain the place name or post office of delivery, state or territory abbreviation and postcode. This line should be printed in capitals without punctuation or underlining, with the postcode put last
  • For international mail, the country name should be in capitals on the bottom line

For the rest of the world, see Wikipedia’s entry on Address (geography).

Download a file on dropbox using wget on linux

Firstly, get the dropbox share link. It will look like this

https://www.dropbox.com/s/ad2arn440pu77si/test.txt

Then add a “?dl=1” to the end of that url and a “-O filename” so that you end up with something like this:

wget https://www.dropbox.com/s/ad2arn440pu77si/test.txt?dl=1 -O test.txt

Now you can easily get files onto your linux box that aren’t available elsewhere.

Sugar logic to calculate days an opportunity has been open

We want to find out how many days an Opportunity in SugarCRM has been open, using a calculated custom integer field in SugarCRM Studio. I’m assuming you’ve already created a new custom integer field, likely called something similar to “Days open”.

The sugar logic to calculate the difference between two dates is this:

abs(subtract(daysUntil($date1),daysUntil($date2)))

and the sugar logic to do an if then else is:

ifElse(test,if_true,if_false)

The first test we’ll do is if the sales stage is “Closed Lost” or “Closed Won”, if it’s true, we’ll calculate the days between the create date and the expected close date. If the sales stage is something other than “Closed Lost” or “Closed Won” then we’ll calculate the days between the create date and today.

This makes our pseudo sugar logic formula look like this:

ifElse(
	or(
		equal($sales_stage,“Closed Lost”),
		equal($sales_stage,”Closed Won”)
	),
	calc_days_create_to_expected_close_date,
	calc_days_create_to_today
)

Which, when we update it with the real sugar logic parts becomes this:

ifElse(
	or(
		equal($sales_stage,"Closed Lost"),
		equal($sales_stage,"Closed Won")
	),
	abs(
		subtract(
			daysUntil($date_entered),
			daysUntil($date_closed)
		)
	),
	abs(
		subtract(
			daysUntil($date_entered),
			daysUntil(
				today()
			)
		)
	)
)

Now, removing all the spaces we get this:

ifElse(or(equal($sales_stage,"Closed Lost"),equal($sales_stage,"Closed Won")),abs(subtract(daysUntil($date_entered),daysUntil($date_closed))),abs(subtract(daysUntil($date_entered),daysUntil(today()))))

This post was written in response to a question on LinkedIn.