CSV files in Excel: How to convert a comma-delimited file to a semicolon-delimited file, and vice versa

A CSV file is a plain text file used to store and transfer data between different, often incompatible, applications and systems.

In these plain text CSV files, each line of text is a data record. Also, each line of text (i.e. each data record) consists of one or more fields, separated by delimiters.

Those delimiters are originally commas [,], hence the name comma-separated values (CSV). However, data can in principle be delimited with a variety of special characters. Since comas are used as decimal symbols in many countries, data in CSV files in those countries will instead usually be delimited with semicolons [;].

 

Excel can read, edit and create CSV files. Lines of text are being treated as rows, and delimited fields as new cells.

Let’s look at the data in this example:

generate csv step 1

We can save that data in CSV file format by choosing Save As option:

generate csv step 2

This is how that data looks when we open our CSV file with a plain text editor such as Notepad:

generate csv step 3

Note that not only that semicolons are delimiters in this file, comma is also the decimal symbol.

All of this is defined by the Windows regional number format settings:

regional settings

Not only would user which uses comma as a list separator have trouble reading this data because of incompatible List separator, he would most likely also have issues with our Decimal symbol and Digit grouping symbol.

It is than a good practice to avoid using such symbols in CSV files when possible. We can for example, as shown, use hyphen [-] instead of period [.] for formatting dates.

 

On the other hand, CSV file generated by user based in, for example, United States, would contain this data:

unreadable csv

If we open that file in Excel, the data is not displaying properly:

unreadable csv opened in excel

 

First thing we can do in order to display it properly is to change our region and/or number formats in Windows Control Panel.

However, this is potentially problematic solution as we are breaking CSV files from our region by doing this.

 

Our next option is to fix our broken CSV file in Excel with the help of Convert Text to Columns wizard:

text to columns

In our first step we should select Delimited option:

text to columns step 1

We should only select Comma as Delimiter option:

text to columns step 2

We also have to select every problematic column and instruct Excel what kind of data to expect. Specifically, in this example, if columns are expected to contain decimal numbers, we have to tell Excel to expect periods [.] as decimal separators so that Excel could convert those to our decimal symbols:

text to columns step 3

Once the wizard completes, if needed, we can save our modified CSV file:

save as csv new

 

Alternatively, we can manually edit our CSV file by using Notepad’s Replace All function.

In order to convert a comma-delimited file to a semicolon-delimited file, we have to:

  1. Replace all commas [,] with semicolons [;]
  2. Replace all periods [.] with commas [,]

edit csv in notepad

Once we do that, we can open the file in Excel:

edit csv in notepad save&open

Vice versa, if we were to convert a semicolon-delimited file to a comma-delimited file, we would have to:

  1. Replace all commas [,] with periods [.]
  2. Replace all semicolons [;] with commas [,]

 

Dig deeper:

Text in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *