Excel CSV output using something other than commas

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”.

2015-07-09 15_16_16-All Control Panel Items

2. Click on “Additional settings…”

2015-07-09 15_17_03-Region and Language

3. Change the “List separator” from a comma ( , ) to the character you want.

2015-07-09 15_16_44-Customize Format

And then remember to do this all again when you’re done, because you probably want your default to stay a comma.

Excel CSV output using something other than commas

One thought on “Excel CSV output using something other than commas

  1. I just want you to know that I am definitely reading your blog and keeping up on your posts. Given the technical nature of the posts, I understand only pieces, but I am here! I’m glad that someone with technical know-how is handling these issues.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s