How to save a pipe-delimited “csv” file from MS Excel on Windows 7

Sometimes when preparing a text file to upload data from an ASCII file to a database table (for example, using Oracle SQL*Loader, or an external table), a comma-separated-value (csv) file format can be a problem – maybe your data fields themselves contain commas, which would obviously confuse the way in which fields are parsed.

So, you may find it useful to save a version of your ASCII data file in, say, a pipe-delimited format – the pipe (“|”) character rarely appears in the source data itself. In fact, you can specify any delimiter character.

But how to save a custom-delimited text file from MS Excel? On Windows 7, this can be achieved as follows:

  1. Start Menu > Control Panel > Region and Language > Additional Settings
  2. Set List Separator to | (i.e. a “pipe” symbol) – or any symbol of your choice
  3. In Excel, choose File > Save As
  4. Save as type = CSV (comma delimited) (*.csv)

Note that other PC applications may be affected by the “List Separator” setting, so it may be necessary to set this back to a comma (“,”) when your work is done.

Advertisements

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 )

w

Connecting to %s