Banish quotation marks from text files exported from Excel

When exporting Excel worksheets to CSV (comma-separated values) or tab-delimited text, it will add quotation marks (“..”) around any fields containing commas or quotes.

While such files can be re-imported into Excel without problems, it can cause other applications to choke. It is possible to open the files in a text editor to remove the problematic characters, or create a Word macro to do the same job automatically, but the simplest solution is to bypass Excel’s own file export filters and use a macro such as the following to perform the export:

Public Sub TextNoModification()
Const DELIMITER As String = "," 'or "|", vbTab, etc.
Dim myRecord As Range
Dim myField As Range
Dim nFileNum As Long
Dim sOut As String

nFileNum = FreeFile
Open "Test.txt" For Output As #nFileNum
For Each myRecord In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With myRecord
For Each myField In Range(.Cells(1), _
Cells(.Row, Columns.Count).End(xlToLeft))
sOut = sOut & DELIMITER & myField.Text
Next myField
Print #nFileNum, Mid(sOut, 2)
sOut = Empty
End With
Next myRecord
Close #nFileNum
End Sub

The delimiter and output filename can be changed by editing the code. The macro above is taken from this very useful page from McGimpsey & Associates which contains a whole host of Excel tips.

Disabling NOD32 update balloons

I’m currently using NOD32 Antivirus (best of breed, according to Virus Bulletin) and the one complaint I have about it is the balloon tip that pops up every time the virus definitions update. It’s much better than having a window pop up and interrupt whatever I’m doing, but it’s still a little distracting.

Fortunately, there’s an easy way to turn it off – simply double-click the NOD32 icon in your system tray, choose NOD32 System Setup (under NOD32 System Tools), click the Setup button and tick Enable silent mode. You’ll still get warning messages when a virus is detected unless you’ve explicitly turned them off, but if you want to be extra sure, you can set up notifications via email and/or Messenger Service* popups through the Notification tab. If you’d like to safely simulate a virus infection, you can download the EICAR standard antivirus test file.

While I’m very happy with NOD32, it’s not the most memory-conservative antivirus solution out there. It uses around 20MB of RAM on my machine (with unnecessary features such as Office document protection, Outlook email protection and “Internet protection”, as well as the custom user interface, disabled), which is still better than a lot of the AV packages aimed at the home user, but still bloated compared to F-Prot which uses around a fifth of that. Having said that, as long as you’re running with more than 512MB of RAM, 20MB is a small price to pay for peace of mind.

* Note that the Messenger Service is nothing to do with MSN Messenger or Windows Messenger.

Getting NuSOAP to work in PHP5

I’ve been playing with NuSOAP (a SOAP toolkit for PHP) and had trouble getting it working under PHP5. I was getting the following error:

Fatal error: Cannot redeclare class soapclient in C:\Program Files\xampp\htdocs\dev\lib\nusoap.php on line 7240

It turns out that PHP5’s own SOAP extension uses soapclient as a class name. The easy solution is to replace all instances of soapclient in the NuSOAP library files (and your own code) with something else – I chose nusoapclient. If you use Notepad++, it’s as simple as opening all 13 source files and running a search/replace.