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.

Pixmania review

I recently ordered a printer from Pixmania, something I would normally buy from Ebuyer but for some reason they kept cancelling my order.

The site is nicely designed, but it has a number of quirks. Firstly, every time I go to pixmania.co.uk or pixmania.com, I’m asked for my country. I’d expect to go straight to the UK site when using the .co.uk domain, and perhaps to be asked once when using the .com. Ebuyer looks at my IP address and automatically sends me to the UK site; kelkoo.com asks me my country every time but at least kelkoo.co.uk takes me straight to the UK site.

Anyway the shopping process seemed OK – their “Zen Engagement” extended warranty and “VIPix” loyalty card was automatically added to the basket but thankfully I was able to remove them. I have no need for an extended warranty on a disposable printer (I’m replacing my old Samsung ML-1510 simply because I need a new cartridge, and it’s almost as cheap to buy a whole new printer) and while the loyalty card scheme – £14 for a year’s free delivery and 7% discount off certain products – might be useful for regular buyers, I don’t expect to buy from Pixmania again in the near future.

It becomes obvious that Pixmania is a French site, hastily translated to suit other markets, because of the examples of broken English scattered through the site: “Your delivery mode”, for example.

Order tracking is a bit strange too: my printer was dispatched 4 days after placing my order, but I didn’t get an email advising me of this; I had to keep checking the order tracking page on the website, which on one occasion appeared in French! Even when it did display in English, I clicked on the parcel number to go to the Parcelforce online track & trace service as instructed, but then had to enter the tracking ID manually; I’d have expected it to go straight to the details for my parcel as happens with countless other online suppliers. The Parcelforce site isn’t particularly helpful either – I’m told that “Progress of the parcel you’ve enquired about is displayed below” but there’s nothing there!

I must say I’m slightly regretting ordering from a site that has so many annoyances. Ebuyer and even Amazon aren’t perfect, but in the case of Pixmania, it just seems like the effort to make the shopping experience “as fluid as possible” (from their own About Us section) just hasn’t been made.

Anyway, the order worked out slightly cheaper than Ebuyer would have, and although it hasn’t arrived yet, I’m not in a huge rush, although it would be nice to have it by Saturday (a week after I placed the order).

Update: It arrived eventually, and the printer is a pretty good. Interestingly, the Dell 1100 printer is built around the same chassis and print engine, although the Samsung is faster.

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.

Amazon filler item finder

I just bought a couple of DVDs from Amazon.co.uk and my order came to £14.94 – 6p short of the free Super Saver Delivery threshold. If you’re ever in a similar situation, try the Amazon Filler Item Finder. It produces a list of cheap items that you can add to your basket in order to qualify for free delivery. You never know – you might even end up with something you wanted!

There is an alternative site here, which isn’t quite as user-friendly but sometimes finds cheaper items. I ended up with 25 Easter animals stickers for 52p, which no doubt I’ll never use but it saved me £2.23 on delivery.

Update: I was able to remove the stickers from my order without being charged for delivery (screenshot), but there’s no guarantee that Amazon will honour the order.

Update 2: I had to cancel one of the DVDs, bringing my order total down to £7.97. After cancelling the item, I was required to choose another delivery option, so it seems that their system will only let you off if you’re close to the £15 threshold.