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 by a variety of special characters. Since commas 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:
We can save that data in CSV file format by choosing the Save As option:
This is how that data looks when we open our CSV file with a plain text editor such as Notepad:
Note that, not only are semicolons delimiters in this file, commas are decimal symbols.
All of this is defined by the Windows regional number format settings:
Not only would the user who uses a comma as a List separator have trouble reading this data because of the incompatible List separator, he would most likely also have issues with our Decimal symbol and Digit grouping symbol.
It is then a good practice to avoid using such symbols in CSV files when possible. For example, as shown, we can use a hyphen – instead of a period . for formatting dates.
On the other hand, a CSV file generated by a user based in, for example, the United States would contain this data:
If we open that file in Excel, the data is not displaying properly:
One of the things we could do in order to display it properly is to change our region and/or number formats in the Windows Control Panel.
However, this is a potentially problematic solution, as we are breaking CSV files from our region by doing this.
Our first option is to fix our broken CSV file in Excel with the help of the Convert Text to Columns wizard:
In our first step, we should select the Delimited option:
We should only select the Comma as a Delimiter option:
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:
Once the wizard completes, if needed, we can save our modified CSV file:
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:
- Replace all commas , with semicolons ;
- Replace all periods . with commas ,
Once we do that, we can open the file in Excel:
Vice versa, if we were to convert a semicolon-delimited file to a comma-delimited file, we would have to:
- Replace all commas , with periods .
- Replace all semicolons ; with commas ,
Dig deeper:
One thought on “CSV files in Excel: How to convert a comma-delimited file to a semicolon-delimited file, and vice versa”