The other day I got an XLS file exported from a client’s Microsoft SQL Server. I had to import the data into MySQL, and I thought using CSV would be the right way. But there was a problem – the XLS cells had newlines in them, and exporting to CSV use to mess up, and the only option was to remove the newlines in the XLS before exporting.
So I needed to remove the newlines in the XLS file. I was at a friend’s place. So I used his computer to open up the XLS file in Microsoft Excel XP, and looked at the search and replace function. “Now how the hell do I specify a newline?” . I looked through the clippity help, but couldnt figure it out. Googled, but found that there are “add-on” products available for a price which lets you do that.
Disgusted, I came back home, opened the file in OpenOffice.org, used its search-and-replace Regular Expressions (regex, in short) which allowed me to specify a newline , and cleaned up the XLS before exporting.
This got me thinking. Even in its rather new version of Office, Microsoft(M$ in LUG parlance) doesnt provide a regex replacement function. What is it with this company? Does it find its user too dumb for its products? That they can figure out pivot-tables, but cant figure out simple regexes?
When will M$ learn, and start trusting the intelligence of its “power” users, and start putting features in its products, which have been a given in the FLOSS world?
Interestingly, search-and-replace in OpenOffice.org spreadsheet can be done on all the sheets at once, but again in Excel, you have to buy an add-on.