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:
- Start Menu > Control Panel > Region and Language > Additional Settings
- Set List Separator to | (i.e. a “pipe” symbol) – or any symbol of your choice
- In Excel, choose File > Save As
- 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.
