I export a lot of csv files from our web applications at work into Excel, where I do manipulations and analysis. This is probably 50% of my job.
I enjoy it, but one that’s always drove me crazy–if the web app serves up a .csv, but labels the extension as .txt or serves up a .tsv (ie. tab-seperated) and calls it .txt or .csv or any sort of mismatch like that, you get an annoying error, something like:
The file you are trying to open is in a different format than specified by the file extension…
Here’s how to turn that stupid error off (courtesy of msdn.com):
Basically, you have to add a new key to the registry (open regedit.exe and browse to the right spot, see below. Note: on my computer, I had to put it in …/Excel/14.0/.… instead of 12, because I have the next version up, whatever it is (Office 2011?)
- Location: HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security
- Create a new key:
- type: 32 bit DWORD
- name: ExtensionHardening
- value: 0 = Disable check; 1 = Enable check and prompt; 2 = Enable check, no prompt deny open
By default (ie. if they key doesn’t exist), the value is set to 1. Translation: it’s always going to ask you.
Create that key, restart Excel and you should be good to go!



Replace text with jQuery and Regular Expressions
Today, I wanted to hide some text on a webpage I was working on. It was a lot of text all throughout the document, but each instance was inside a span and within parenthesis inside that.
I could have edited it all out, but it would have been really time consuming–the CMS I’m using had each one in its own element–so I needed a quicker, more programmatic way to deal with it.
I needed to replace text within a bunch of elements using a regular expression.
To do it, I used a bit of jQuery magic:
$('span').html( function (foo,oldhtml) {
var content = oldhtml.replace(/\(.*\)/gi,"");
return content;
});
To walk through this:
$('span').html( function (foo,oldhtml) {
Here we get all the html/code inside each span. Then we open a function with foo as the index (no clue what this means, it’s just what the documentation says) and oldhtml is a variable that lets us manipulate what used to be in there.
var content = oldhtml.replace(/\(.*\)/gi,"");
Here, we make content a new variable and use replace and a regular expression that looks for anything that’s in parenthesis and removes it. The .replace function is a regular Javascript function, not any fancy jQuery nonsense.
Of important note here: the regular expression does NOT go in quotes, but the “replace it with this” string does. Also, you have to escape the parenthesis because if you don’t, you end up grouping.
The rest of the code just dumps out the new, manipulated string; and because we’re using the html() function, the returned string is what ends up as the contents of the original selected span.
Got that? Good.