How to open a CSV file in Microsoft Excel with columns

Problem:

When you open a CSV file in Microsoft Excel, data from all columns gets stacked in the first column, which makes it difficult to work with the file (Fig. 1.).

Fig. 1. A CSV file with data stacked in the first column.

Solution:

The problem with incorrectly displayed data usually occurs when the character used to separate values/columns in a CSV file is different than the one set up in your PC’s regional settings. This character, known as a delimiter, can vary across regions. For example, in many European countries, the default delimiter in Windows is a semicolon (;), while in the USA, it is a comma (,).

To fix the problem, first check the region-specific delimiter setting on your PC, then apply one of the suggested solutions.

Check the delimiter setting on your PC

1. Open the Start menu, type control panel and press Enter to open the Control Panel window.
2. Click Change date, time, or number formats or Region, as shown in Fig. 2.

Fig. 2. Accessing regional settings in Control Panel.

3. In the popup that opens, click the Additional settings button.
4. Have a look at the List separator setting (Fig. 3.) – it will show what delimiter is set on your PC (in our example, it’s semicolon).

Fig. 3. Checking the delimiter setting in Windows.

Use a method of your choice to make your data display correctly

Click a link below to learn more about each method:

• Use the Text to Columns feature in Microsoft Excel to transform your CSV
• Use PowerShell to change delimiter in your CSV file
• Change the decimal separator in Microsoft Excel settings

Use PowerShell to change delimiter in your CSV file

If you feel OK working with command prompts and scripts, you can quickly fix all delimiters within a CSV file with a single PowerShell cmdlet.

1. Open the Start menu, type powershell and click Windows PowerShell (Fig. 4.).

Fig. 4. Launching Windows PowerShell.

2. Copy and run the following script:

   Import-Csv -Path 'input_file_path' -Delimiter ',' -Encoding UTF8 | Export-Csv -Path 'output_file_path' -Delimiter ';' -NoTypeInformation -Encoding UTF8

   replacing:

   • input_file_path with the path to your CSV file with incorrectly displayed data, e.g. C:\my-input-CSV-file.csv
   • output_file_path with the path to a new CSV file with the corrected separator you want to generate, e.g. C:\my-output-CSV-file.csv
   • the value after the first -Delimiter parameter with the delimiter currently used in your CSV file that displays incorrectly. In our example, it’s comma (,), as shown in Fig.1.
   • the value after the second -Delimiter parameter with the delimiter you want to apply to your CSV to fix the problem. In principle, it should be the delimiter that’s set up on your PC, as shown in Fig. 3. In our example, it’s a semicolon (;).
3. Open the output CSV file in Microsoft Excel – it should display just fine (Fig. 5.).

Fig. 5. The data displayed correctly in separate columns in Microsoft Excel.

Change the decimal separator in Microsoft Excel options

The decimal separator set in Excel and the list separator (delimiter) set in Control Panel are interrelated, which means the same separating character cannot be used for both. When you change the decimal separator, the delimiter will automatically adjust to a different character, which might fix the problem with incorrectly displayed data.

1. Open your CSV file with incorrectly displayed data in Microsoft Excel.
2. Go to File > Options.
3. In the Excel Options window, navigate to Advanced and clear the Use system separators checkbox. Next, in the Decimal separator box, type the same delimiter that is used in the file that displays incorrectly (see Fig. 1.). In our example, it’s a comma (,). Click OK to save the changes (Fig. 6.).

Fig. 6. Accessing and configuring the decimal separator in Microsoft Excel.

4. Save your file by going to File > Save as. Choose CSV UTF-8 (Comma delimited) (*.csv) as a file type.
5. Open the newly saved file in Microsoft Excel – now the data should display correctly (see Fig. 5.).