How to change Excel's CSV file delimiter

If you are performing a database bulk load and the data has been sent to you in excel sheet format, a well known technique is to first convert this data into a CSV file. Excel has in-built feature that does this conversion for you; it delimits fields with a comma so when the file is loaded, you can use the same delimiter to tell the database how to process the CSV file.

This technique almost always works seamlessly except when a field contains a comma as part of its value. In that case, the database loader gets confused and treats the comma as a field delimiter and you end up getting error on that particular row of data.

To avoid this issue, one can use another character than a comma to delimit the fields. That way an existing comma value doesn’t cause any confusion. However, this required additional task of opening up the CSV file in an editor and manually making the modifications that isn’t highly desirable.

Fortunately, comma as a default delimiter is not inbuilt in the Excel program; rather it’s a property at the OS level. All you have to do is modify that property from a comma (,) to let’s say a pipe (|), and bingo. The next time you create a CSV file, Excel automatically uses the pipe as the delimiter, and your converted file is all set for the database loader.

Use the steps below to modify the list delimiter:

Step 1 -> Launch Regional Settings from Control Panel

Step 2 -> Press the Customize button on the ‘Regional Options’ tab

Step 3 -> Set the List Separator and save your settings.