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:
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:
Table of Contents
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(|).
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.
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.
The steps are as follows:
. First open the Empty Excel File
Click on the Data tab
Under Get & Transform Data, select From Text/CSV option
Then Select your desired CSV file
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.
Then click on Transform Data.
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
Goto run and type "intl.cpl" and press Enter.
This will open the regional setting of your computer.
Click on the Additional Settings button
Under the Number tab, find List Separator and simply change your desired single-character separator.
Click on Apply, Ok.
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.
Open CSV file in Excel
Click on Data Tab located at the title bar.
Select the Desire column you want to split.
Then Under Data Tools, Tab to 'Text to Columns'
Choose the Delimited checkbox and then click on Next
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.
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.
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:
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.
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.
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.
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.
You can open CSV delimited data in Excel using any of these five methods.