While dealing with the ICD 10 codes, we found that the SQL import process was adding spaces to text fields because of the tab delimiters we were using. A naive attempt to use the CSV format was quickly killed because the ICD 10 descriptions include commas, as I already knew from using J.
Notepad++ choked on the massive find-and-replace operations (I should probably see about submitting a bug). While it would complete the operation, somewhere along the way it would corrupt the file.
While something like dnGREP would probably be the right tool for find/replace in massive files, I instead used Excel 2010 to output the CSV using the pipe character (
| ) as the delimiter.
This is a kind of secret, as Excel doesn’t ask you what delimiter you want to use. Instead, you have to globally change the delimiter for Windows:
1. Open the Control Panel and select “Region and Language”.
2. Click on “Additional settings…”
3. Change the “List separator” from a comma (
, ) to the character you want.
And then remember to do this all again when you’re done, because you probably want your default to stay a comma.