Data Import & Export

How to Save an Excel Worksheet as UTF-8 CSV with a Custom Delimiter

Muhammad Talha Oct 23, 2026 868 views

Question details

The user needs to export worksheet data as a UTF-8 encoded CSV file using a specific custom delimiter, such as a pipe character, bypassing default application behaviors.

Save an Excel Worksheet as UTF-8 CSV with a Custom Delimiter

Product: Microsoft Excel
Device & OS: not provided
Scenario: Exporting a data set for external systems that strictly require UTF-8 encoding and a non-standard delimiter to avoid conflicts with data containing commas.
Observed behavior: Standard CSV export functionalities rely on the system's regional list separator and often default to ANSI encoding, failing to apply the custom delimiter and UTF-8 encoding simultaneously without changing global Windows settings.

Before you start

Ensure that your data does not contain the character you intend to use as your custom delimiter (e.g., the pipe character) to avoid shifting columns during import into other systems. Also, make sure the Developer tab is enabled in your ribbon to access the VBA editor.

Solution 1Recommended

Use an ADODB Stream via VBA to Export Data

Create a custom VBA macro that reads the worksheet, joins the data using your chosen delimiter, and writes the output as a UTF-8 encoded file via an ADODB stream.

This method is highly recommended because it bypasses Windows regional settings entirely. It ensures that the exact delimiter you specify is used and forces the text stream to save in UTF-8 format, preserving special characters.

Open the VBA Editor

Press Alt + F11 to open the Microsoft Visual Basic for Applications (VBA) editor, then click 'Insert' > 'Module' to create a new blank script.

Write the data extraction loop

Write a script that loops through the UsedRange of your active worksheet. For each row, loop through the columns and concatenate the cell values using your desired custom delimiter, such as a pipe '|'.

Configure the ADODB Stream

In your script, initialize an 'ADODB.Stream' object. Set its 'Type' property to 2 (adTypeText) and its 'Charset' property to 'UTF-8'. Open the stream.

Write and save the file

Write the concatenated row strings into the stream using the 'WriteText' method. Finally, use the 'SaveToFile' method to output the stream as a CSV file to your designated local folder path.

Use an ADODB Stream via VBA to Export Data

Independent of Regional Settings: Because the ADODB stream handles the encoding and the VBA loop handles the delimiters, you do not need to modify your computer's Control Panel regional settings.

Solution 2

Temporarily Change Windows Regional Settings

If you are unfamiliar with VBA, you can force the standard CSV export to use a custom delimiter by altering your global Windows list separator temporarily.

Advanced Data Export with WPS Office

Manage and Export CSV Files Seamlessly in WPS Office

WPS Spreadsheet provides high compatibility with Excel files and full support for advanced VBA macros. You can easily execute custom export scripts or utilize built-in tools to handle diverse data encoding and delimiter requirements.

1. Open your data file in WPS Spreadsheet: Launch WPS Office and open the worksheet you want to export as a custom CSV.
2. Access the Macros menu: Navigate to the 'Tools' tab and click on 'Macros' to open the VBA editor.
3. Insert your export script: Paste your custom ADODB stream VBA script into a new module.
4. Execute the macro: Run the macro to instantly generate and save your UTF-8 encoded CSV file with the custom delimiter.

100% compatible with Microsoft Excel format standards (.xlsx, .csv)Fully supports VBA macros for custom data export streamsLightweight, fast, and completely free to downloadIntuitive tabbed interface for managing multiple large data sets

microsoft office alternative - wps office

Frequently Asked Questions

Why does standard CSV export use commas or semicolons by default?

Spreadsheet applications typically rely on the 'List separator' defined in your Windows Regional Settings. In regions like the US, this is a comma, while in many European countries, it defaults to a semicolon.

Can I use special characters like a caret (^) or a tab as a custom delimiter?

Yes. When utilizing the VBA and ADODB stream method, you can define any string or character variable in your script as the row separator, granting complete control over the output format.

Does the ADODB stream add a BOM (Byte Order Mark) to the UTF-8 file?

By default, an ADODB.Stream object will write a UTF-8 BOM at the beginning of the file. If the system you are exporting to requires a BOM-less UTF-8 file, you will need to add specific VBA lines to read the stream as binary and skip the first 3 bytes before saving.

Why do special characters look scrambled when I save as a standard CSV?

Older standard CSV export formats often use ANSI encoding by default, which does not support special characters or non-Latin alphabets. Saving explicitly with UTF-8 encoding prevents this data loss.

Excel & Data Problems