Open CSV files in Excel with the correct seperator/delimiter

👤 Diwas Poudel 🕒 14 Mar 2023 📁 TECH

You might have noticed that when you open your delimited CSV file in Excel, some of the data is not properly arranged or aligned with the excel columns. Excel simply splits the data based on what is set by default in the Excel Settings. It would be preferable if Excel allowed you to select separators, formats, and encoding when opening a CSV file and let the user decide which setting to use.

Also, Microsoft decided to use different separators based on localization, which irritates people who work on an international basis. In some countries, a comma is used as a separator, while in others, a semicolon is used as a separator.

Suppose you CSV file with the data as shown below:

1|2|3|4|5

Now you expect that each pipe(|) separated value will be in a separate column in excel but when you open this CSV file in Excel

There are various methods of solving this:

Method 1: Using SEP Syntax
Method 2: Using Text/CSV settings
Method 3: Using Regional Settings of the Computer
Method 4: Using Text-to-Column Excel Settings
Method 5: Using the Command Line
Method 6: Using Powershell
Conclusion:

Method 1: Using SEP Syntax

We can use SEP= on the first line of a CSV file, and then when you open the same CSV file in Excel, Excel will format the data based on the Delimiter present.

If you have SEP=| then excel will separate each data into columns on the basis of a pipe(|).

Example:

SEP=|
COl1|col2|col3|col4|col5
1|2|3|4|5

When you open this CSV file in Excel, it will scan line by line, and on the first line, it finds SEP syntax with a pipe(|) as a delimiter. Excel then gets the idea to break the data into columns based on the pipe(|), so it breaks each data into a column based on the semicolon.

opening-csv-file-in-excel-with-proper-format-using-SEP-syntax — **fig. Opening CSV File in a proper format in excel using SEP syntax**

Note: You can use any single character (, | # @ ' *), etc as delimiters.

Also, keep in mind that SEP syntax is only a portion of the CSV file, and Excel will not be displayed or written in the spreadsheets regardless of the format you specify in SEP.

When you use this method, Excel will override the system setting ("list separator character") and open the file correctly.

Method 2: Using Text/CSV settings

This is one of my best approaches for splitting all your data on the basis of desired delimiters.

FYI: Tested successfully in Excel 2016 and Excel 2019

The steps are as follows:

1. First open the Empty Excel File

2 Click on the Data tab

3 Under Get & Transform Data, select From Text/CSV option

4 Then Select your desired CSV file

5 Under the Delimiter dropdown select your desired delimiter. If you did not find your desired delimiter then select 'Custom' from the list and put your desire delimiter on the text box.

6 Then click on Transform Data.

custom-settings-delimiter — **fig. Select your desired delimiter, if not exists then select the custom option and set the new delimiter**

You will now receive all of the data, which will be divided according to the delimiter you specified.

Method 3: Using Regional Settings of the Computer

You can make changes to the Excel formatting of the data by changing the regional settings of your computer because Excel makes use of the settings that are present in the Regional Settings of your computer.

Steps to Change Regional Settings

1 Goto run and type "intl.cpl" and press Enter.

This will open the regional setting of your computer.

2 Click on the Additional Settings button

select-additional-settings — **fig. select 'Additional Settings**

3 Under the Number tab, find List Separator and simply change your desired single-character separator.

4 Click on Apply, Ok.

select-list-separator — **fig. select desire list separator**

5 Now reopen the CSV file with Excel.

If this does not work then go forward and perform additional actions like :

Open Excel then clicks on File > Option >Advanced then Make sure "Use system separators" is checked.

Tested on Windows 8, Windows 10, and Windows 11.

Method 4: Using Text-to-Column Excel Settings

Text to Columns helps to split the single column into multiple columns on the basis of the delimiter you have selected.

1 Open CSV file in Excel

2 Click on Data Tab located at the title bar.

3 Select the Desire column you want to split.

4 Then Under Data Tools, Tab to 'Text to Columns'

5 Choose the Delimited checkbox and then click on Next

6 Click on the desired delimiter ( Tab, Semicolon, Comma, Space) from the list, if this is not your desired delimiter then you can manually set the delimiter by checking other options.

7 Click on Next and then the Finish button.

Method 5: Using the Command Line

You can you following command line as a reference:

`start excel.exe "pathtocsv" /c delimiter`

This syntax is used to open a CSV file in Excel with a specific delimiter.

Here, you have to replace pathtocsv with the actual location of the CSV file for eg: "D:\Documents\data.csv" and your delimiter for eg. , for a comma-separated file or ; for a semicolon-separated file.

For example:

`start excel.exe "D:\Documents\data.csv" /c ,`

The command start excel.exe "D:\Documents\data.csv" /c is used to open a CSV file called "data.csv" in Microsoft Excel from the "D:\Documents" folder. The "/c" option specifies the file delimiter, which in this instance is a comma (",").

Method 6: Using Powershell

You can use PowerShell to open a CSV file in Excel with the correct separator/delimiter.

Scripts look like this:

$excel = New-Object -ComObject excel.application

$excel.Visible = $true

$workbook = $excel.Workbooks.Open("C:\path\to\your\file.csv")

$worksheet = $workbook.Sheets.Item(1)

# Set the delimiter to comma (",")

$worksheet.Range("A1").TextToColumns($worksheet.Range("A1").EntireColumn, 1, $null, $true, $null, $null, $true, ",")

$workbook.Save()

$excel.Quit()

In this script, we first create a new instance of Excel using the New-Object cmdlet. We then set the visibility of Excel to true so that we can see the file that is opened.

Next, we open the CSV file using Workbooks.

Open the method and set the worksheet to the first sheet in the workbook using the Sheets.Item property.

To set the delimiter to a comma (","), we use the TextToColumns method on the range "A1" in the worksheet. The first parameter specifies the range to apply the delimiter to, the second parameter specifies the delimiter type (1 for comma), and the rest of the parameters can be set to $null or left as default values.

FAQ:

What is the best way to open a CSV file in Excel and specify the correct separator/delimiter?

The "Text Import Wizard" lets you select the delimiter and other options when opening a CSV file in Excel. "start excel.exe "C:\path\to\your\file.csv" /c delimiter" opens the file directly in Excel with the chosen delimiter.

Can I change the delimiter used in a CSV file after it has been opened in Excel?

You can use the "Text to Columns" feature in Excel as mentioned above, which allows you to split the data in a column into multiple columns based on a delimiter of your choice, to change the delimiter that is used in a CSV file after it has been opened in Excel. This allows you to change the delimiter even after the file has been opened.

How can I fix common issues when viewing a CSV file in Excel with the wrong separator/delimiter?

When Excel opens a CSV file with the wrong separator/delimiter, data may be split into incorrect columns, empty cells may be added between data, and special characters or spaces may be included in the data. The "Text Import Wizard" or "Text to Columns" option or a text editor can fix these issues.