ICD Codes & MS-Excel (Leading Zeroes)

Each year CMS publishes a data set to help users validate their DRG assignments for that year's DRG version.

We use the official CMS test data set to confirm that our DRG assignments are correct. The data set arrives as a text file which we use in a variety of ways:

1. we use the text file as-is to validate DRGFilt
2. we extract & pretty-print random cases to validate our interactive products
1. CGI-DRG under Windows
2. CGI-DRG under Linux
3. Access-DRG under Windows
4. C-callable DLL under Windows
5. Linux DLL under Linux
6. PHP shared object under Linux
7. Perl shared object under Linux
8. C shared object under Linux
3. we reformat the text file as a CSV in order to validate Excel-DRG

(Many of these exercises are being done twice as we validate our ICD10-based code base.)

It is item 3 that gave us fits this for a long-standing reason: MS-Excel's CSV import insists on stripping leading zeroes for values it imports if those values are all-numeric. This wreaks havoc on ICD9 or ICD10 codes, either of which can start with one or more zeroes and be all digits for the rest of the code.

There are tricks for formatting the code so that it appears to have its leading zeroes, but that does not help, because the actual data remains the stripped version, which is just plain wrong.

We have painfully rediscovered the only way we know of to fix this (although we welcome suggestions of better ways to do this)

1. give your CSV the ".txt" extension, eg "testdb.txt" and NOT "testdb.csv"
2. use the Excel Data->Import->text file function
3. choose your text file from the list of available files
4. in the import dialogue, specify that the delimiter is comma
5. at step three, highlight any ICD9 or ICD10 columns and specify that the format is "Text"
6. import and confirm

TECHNIQUES WHICH DO NOT WORK

If you are trying to use the native Excel Import function on a file with the CSV extension, eg "testdb.csv", we tried the following ideas without success. Most of these ideas were found on the Internet, free and worth every penny.

• Encasing the data in double-quotes, eg "0016071"
• Using a single-quote at the start of the code, eg '0016071
• Making the value into a formula, eg ="0016071"
• Using a leading blank and double-quotes, eg " 0016071"