Help - data problem

One of the national NHS organisation codes is "5E1" Stockton on Tees PCT. The code 5E1 aslo happens to be an internal error code for MS Excel. Every time I open the CSV data file with Excel,  Excel parses the value "5E1" into

5.00E+01

Anyone have any ideas on how to stop this from happening?

Comments

An explanation (and solution)

This isn't due to an error, but is happening because '5E1' happens to conform to a standard 'exponential notation' for numbers, commonly used with computers. To put it more clearly, 5E1 means '5 followed by 1 zero', i.e. 50. This might sound pointless, but is very useful when numbers are extremely large or small, e.g. the number of atoms in a gram of water is about 33400000000000000000000, or 3.34E22.

The problem is that CSV (Comma Separated Values) files contain absolutely no information about what the fields represent, only the raw values. It's left up to Excel to guess what these are, and although it usually gets it right, in this case there's just no way for it to know this isn't meant to be a number. Ideally the solution would be to export the data in another format. What is the source of the data?

As a practical workaround, I suggest you open the file in a plain text editor like Wordpad, press Ctrl-H to open the 'Replace' dialog, then replace all instances of 5E1 with something safe like FFFish. Save a copy of the file, and open that in Excel. Now press Ctrl-H in Excel, and replace all FFFish with '5E1 . Note the single apostrophe preceding it - this is essential, and there must NOT be a 2nd quote closing it - this will tell Excel that the cell should be formatted as text and not a number. (Note also that you can't just put the apostrophe into the CSV file itself, since Excel's CSV loader doesn't use it as a hint, and will just print it in the cell instead.)

Anyway, if this is a regular problem, and solving it would improve efficiency in some small part of the public sector, I'd be happy to write a program to automatically detect these things and convert the file into XML or similar so Excel can import it properly. Let me know how it goes.

 Flag as offensive