How to change the delimiter when exporting CSV from Excel?

Reading Time: 4 minutes.

Recently, I needed to find a way to import data from an Excel to a custom-coded system that could import CSV files. However, the data was fairly unsanitized – containing commas, semicolons and other weird characters. I could define the delimiter used in the imported data – however, to do that, I needed to also be able to define it in the export.

What’s a “CSV” -file ?
Stands for “comma-separated values” – A text file that uses a comma to separate values.

Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator (Delimiter) is the source of the name for this file format.

With Excel, it’s not that simple, though. Excel, just like other Microsoft Office applications loves trying its best to respect the regional & localization settings of the machine it’s running on – most often to frustrating results.

However, like with so many other things, you can change the delimiter value used in CSV export by tweaking these settings. And this article describes how!

Note: This guide applies to Windows 10 – see the bottom of the page to find a similar solution for Windows 7!

Background

Table of Contents

Another day, another workaround. Let’s delve into the background of the issue a bit deeper:

I needed to wrangle data stored in an .xlsx file into another form, that another application could read. Normally, that’s simple – just change the file type. However, this time I was posed with an issue: the data included both commas (“,”) and semicolons (“;”), both of which are typical delimiters in CSV files. Additionally, I couldn’t wrap the values inside quotes due to limitations in both the export and the import functionality. So I needed to use another character – such as pipe (“|”).

Instead of being able to configure the delimiter in the export (like in any sensible spreadsheet editor, if you ask me!), Excel is just going to use your regional settings and grab the list separator from there. This means that in English, CSV is going to be values limited by commas, like this:

Value1,Value2,Value3,Value4...

But in Finnish, we’ll be using the semicolon – like so:

Value1;Value2;Value3;Value4...

Simply changing the regional settings isn’t a solution this time, though – the data I had been supplied had both colons and semicolons in cell values. The data would be incorrectly split and parsed due to that.

However, luckily we can override the delimiter used for any regional settings!

Solution

I found a couple of examples on how to do it in Windows 7 – and obviously, that’ll help you eventually find your way in Windows 10 as well. But always useful to also document the exact steps :)

Here are the steps in writing – see some useful screenshots further below!

How to export a CSV with custom delimiter using Microsoft Excel:

Make sure Excel is closed
Open Control Panel
1. This can be done by hitting win+r and writing “Control Panel” in the Run-window
Select “Region“
Click the “Additional Settings” -button
Find the “List separator” and change it to your preferred delimiter such as a pipe (“|”).
Click OK -> OK -> exit Control Panel
Open the Excel file you want to export to a pipe delimited file
Select File, Save As
Change the ‘Save as type’ to ‘CSV (Comma delimited)(*.csv)’
Change the name and file extension if you want, by default stays as csv even though a different delimiter
Click Save -> OK -> Yes

And the same with pictures!

Fire up your Control Panel, and open “Region”.

Select “Additional settings” – and then you have access to “List separator”!

While the dropdown might have some prepopulated values, you can actually just safely ignore them and input whatever delimiter you need to use to the textfield directly.

Even something nonsensical and borderline rude like this should work :)

Note, that there’s at least one, undocumented limitation here: you can’t use the same character as both decimal and list separator! This means that if you’re using regional settings like Finnish, where comma is considered to be decimal separator instead of a full stop or “dot”, you can’t use it as a list delimiter… Before you change the decimal separator to something else, that is :)

With the selection done, just hit “OK” a couple of times and head back to Excel! You should be able to save your file as CSV or use the Export-function – and you should be good this time!

References

Big kudos to Barry Stevens documenting the solution and Sami Laiho (or, like he’s better known, “the dude with the Järjestelmänvalvoja shirt”) for noticing you can’t use the same character as both decimal and list separator 😬

Author
Recent Posts

Antti K. Koskela

Developer / Architect / Consultant at Koskila / Valo Solutions Ltd. / Norppandalotti Software Co / Alter - Experience Ideas Ltd.

Antti Koskela is a proud digital native nomadic millennial full stack developer (is that enough funny buzzwords? That's definitely enough funny buzzwords!), who works as a Solutions Architect for Valo Intranet, the product that will make you fall in love with your intranet.

He's been a developer from 2004 (starting with PHP and Java), and he's been bending and twisting SharePoint into different shapes since MOSS. Nowadays he's not only working on SharePoint, but also on .NET projects, Azure, Office 365 and a lot of other stuff. He's also Microsoft MVP for Office Development.

This is his personal professional (e.g. professional, but definitely personal) blog.

Latest posts by Antti K. Koskela (see all)