Two Inexplicably Dangerous Characters
Yesterday, as I was writing an e-mail to one of my employer’s clients, I encountered a strange bug with Excel. The e-mail I was writing included instructions that the client should perform, and one of the steps actually involved creating a CSV file, which would then be opened in Excel. Before sending the e-mail, I decided to step through the instructions myself, and make sure everything worked as I expected. When I reached the CSV step, I found that while I could export it fine – I couldn’t open it in Excel. I’ll guide you through what followed using a really simple example CSV:
ID,NAME
1,KARA
2,GAIUS
3,SAUL
As you can see, the above CSV contents are completely valid. However, when I tried to open it in Excel 2000, I received this error message:
When I tried to press the OK button, Excel just gave up loading the CSV. While I didn’t know what they meant by “SYLK“, I assumed something was wrong with the CSV export. Maybe there was an invalid, invisible character in the file, preventing Excel from parsing it – perhaps making it think it was another format. To test this theory, I used one of my favorite features in Notepad++, one that allows me to see all characters, even those which are normally invisible.
Using the feature, I could tell that there were only carriage returns (CR) and line breaks/feeds (LF), which are perfectly valid for a CSV.
With that possibility ruled out, I decided to try the same CSV in a newer version of Excel – the 2007 edition. When I went to open it, I was unfortunately greeted with the same type of errors:
Despite these errors, through the selection of the appropriate dialog options – I was at least still able to view the CSV within Excel:
At this point, however, I was really intrigued – so I googled the original error message I received in Excel 2000. I found a Microsoft article explaining the behavior, while also offering a workaround. When reading the article, I could barely hold in my laughter:
SYMPTOMSWhen you try to open a text file or a comma-separated variable (CSV) file, you may receive the following error message:SYLK: File format is not valid
CAUSEThis problem occurs when you open a text file or CSV file and the first two characters of the file are the uppercase letters “I” and “D”. For example, the text file may contain the following text:
ID, STATUS123, open456, closed
Note This problem does not occur if the first two letters are lowercase “i” and “d”.
WORKAROUNDTo open your file in Excel, open the file in a text editor, and then insert an apostrophe at the beginning of the first line of text.
How to Insert an ApostropheTo add an apostrophe to the beginning of the first line of text in your file, follow these steps:1. Open the text file in a text editor, such as Notepad. Click before the first character in the first line of text. Press the APOSTROPHE key on your keyboard (‘).2. On the File menu, click Save. Quit the text editor. You can now open the file in Excel.
Source: http://support.microsoft.com/kb/323626
I think you need to be a developer to truly appreciate how funny this is. I had to mention this bug to the client, because the likelihood of the ID being the first text in the file was pretty high. Even then, I mentioned they should read the Microsoft article only if they encountered the problem. No way am I going to explain the awkward workaround myself.
So, I confirmed that the bug was still present in Excel 2007. I just hope that Microsoft fired the developer that came up with the SYLK detection logic. Assuming that a file is a particular type based solely on the first two characters (“ID”) is a big risk, especially when those two characters are part of the alphabet, and are also common in IT. I’m guessing there are complexities I don’t see… or at least, I hope so. Either way, their workaround is really unbelievable… they’re actually telling people to add an apostrophe as the first character of the file. I’m aware that Microsoft is a large complicated beast with many different teams involved in releasing bug fixes, but this one is just so ridiculous.
Opera’s Quick Download Feature
There’s a particular feature I’ve always liked in Opera. I’ve seen it implemented in slightly different ways within other browsers. Let’s say I wanted to download my blog’s sitemap.xml file, and I wanted to do this strictly from within the browser. When I visit the link:
http://mattrefghi.com/wordpress/sitemap.xml
My browser doesn’t download the file, it merely displays it. This happens because browsers know how to display XML, and they figure it makes more sense to show you the content – rather than initiate a download. The behavior is different with “.zip” files, for example. Browsers usually initiate a download when they are pointed to a URL that ends in “.zip”. Downloading the physical XML is therefore slightly trickier. In Opera, I can just paste that exact URL into to the Quick Download box at the top of the Downloads page. When I press ENTER, it automatically downloads the physical XML file – rather than trying to display it.
I’ve used this countless times over the years. The same is possible in other browsers, but it usually isn’t as quick as Opera’s implementation. For example, in Google Chrome, I’d first have to view the page, click “Control The Current Page” button, and then “Save Page As”. I would then get a” File Save” dialog, which allows me to change the name, or simply press OK to save the file.
Certainly not as quick as Opera… and what if the XML file was massive? I’d have to wait for the browser to first display it, then I’d be able to download the physical file.
One more reason to keep Opera installed on my computer.
An Endless Wait For IntenseDebate
UPDATE: Soon after I wrote this article, Michael Koenig of IntenseDebate posted a comment with an offer to help me via his personal address. I appreciated the gesture, and made some modifications to the article below to reflect that. I think for the time being I will be keeping the default WordPress comments, but will not completely rule out IntenseDebate for the future.
In the beginning of April, I became aware that my blog was corrupt – no articles were visible, and the visuals weren’t right. I promptly contacted my web host pair Networks, and after an hour of trying different things – their agent (who was extremely helpful), told me it was likely a problem within WordPress. He tried repairing the database, but subsequent browser refreshes just corrupted the WordPress database further. With this knowledge, I thanked the agent for his help and prepared to restore my blog in a fresh copy of WordPress.
By the time April 10th came around, I had mostly everything working – the only problem left was the comments – a good 90% of them were not appearing in the articles. You see, I’ve always used IntenseDebate for my comments. It had a good feature set, and did exactly what I expected it to do. When I installed a fresh copy of WordPress, I naturally installed IntenseDebate very early on – and yet, it didn’t restore my comments properly… the vast majority were simply missing from their respective pages. Concerned, I contacted their support address, providing plenty of detail, and offering URLs to illustrate my point.
I decided to wait for a response before I restored my blog fully – comments were too important to me.
Two weeks passed, and I still hadn’t heard back… so I submitted a support ticket via their website, including the same information I sent via e-mail. Considering that e-mail (as a medium) isn’t flawless, I also asked whether they received my e-mail or not. I realized that it was even possible that Gmail’s spam blocker was eating their message. To cover that possibility, I started regularly checking and deleting my spam. But time passed, and my site remained down – all without the slightest word from IntenseDebate. I started to get annoyed… especially when I noticed that they were happily telling people to contact their support address on their Twitter page. Yet, no help available for me. So, as you can imagine, I logged into Twitter, and posted the following message:
As I’m writing this, they have not replied to me. They still continue to reply to people on their Twitter page, but they have not acknowledged me in the slightest.
On May 2nd, I decided to drop IntenseDebate. I had to manually make corrections in my WordPress database, but after waiting so long, it was an attractive option.
Could it simply be an isolated incident? Perhaps… but I still consider it difficult to forgive.
UPDATE: Soon after this was posted, they replied to me. See the update at the top of this post for details.