Excel typically saves CSV files with using comma (,) or semi-colon (;) as the delimiter. However some programs expect a pipe (|) delimiter.
Trying to convert the delimiter of a CSV file using simple find and replace (e.g. in a text editor) is not advisable, because you might covert a delimiter character inside a value. For example a name value in a CSV file might include a comma:
"Name","Country"
"Smith, John","UK"
If you replace all the commas with pipes, you will get:
"Name"|"Country"
"Smith| John"|"UK"
Which means “Smith, John” has become “Smith| John”, which is almost certainly not what you want.
However it is very easy to convert an Excel file to pipe delimited in Easy Data Transform:
- Install Easy Data Transform on your PC or Mac, if you haven’t done so already. It will only take a minute. There is a fully functional free trial and you don’t have to give us your email or sign up to anything.
- Start Easy Data Transform. Make sure the Auto Run button is pressed in.
- Drag the Excel spreadsheet onto Easy Data Transform. If there is more than one sheet with data, you will be asked which sheets to import - choose the sheet you wish to convert. A pink input item will be added.
- Ensuring the pink input item is selected, click the To File button in the Left pane (scroll to the bottom of the Left pane).
- A window will appear. Choose the file name and location. Choose CSV file for the file type.
- An output item is now added.
- Keep the output item selected. In the Right pane set the Delimiter to Pipe (|). You can also change the file Encoding.
- The CSV file is updated immediately, no need to ‘run’ anything.
If you have a large number of files to convert you can do it using the batch processing feature.
As well as changing file format, Easy Data Transform also allows you to combine 64 transforms to quickly and easily create simple or complex data transformations.
Windows Download
Mac Download