How to Import a CSV into Separate Columns in Excel
Question details
The user needs to import a CSV file into Excel so that the data is correctly distributed across multiple separate columns rather than grouped into one.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Opening or importing a comma-separated values (CSV) file into an Excel workbook.
- Observed behavior
- All values from the CSV file are incorrectly squeezed into a single column because Excel is failing to recognize the file's specific delimiter or import settings.
Before importing, it is helpful to open the original CSV file in a basic text editor like Notepad to confirm exactly which character (such as a comma, semicolon, or tab) is being used to separate the data.
Use the Text to Columns Feature
If you have already opened the CSV and all the data is clustered in the first column, you can quickly split it using the Text to Columns wizard.
This is the fastest method for fixing data that is already pasted or opened in your worksheet. Excel will parse the single column of text and divide it into adjacent columns based on the delimiter you specify.
Click on the letter of the column (usually Column A) that contains all of your combined CSV data.
Navigate to the 'Data' tab on the Excel ribbon and click on 'Text to Columns' in the Data Tools group.
In the Convert Text to Columns Wizard, select the 'Delimited' option and click 'Next'.
Check the box for the correct separator used in your file (such as Comma, Semicolon, or Tab) so the data preview splits correctly, then click 'Finish'.
Import Data via Power Query (From Text/CSV)
Use Excel's built-in data import tool to correctly identify and preview delimiters before loading the data into your worksheet.
Troubleshoot and Repair Excel Application
If every CSV file you open fails to import correctly regardless of your settings, your Excel application might be experiencing a glitch or add-in conflict.
Easily Import and Split CSV Data with WPS Office
WPS Spreadsheet offers a highly compatible and intuitive interface for handling CSV files. Its built-in data import tools and Text to Columns feature automatically detect delimiters, ensuring your data is perfectly organized into separate columns without complex configurations.
- 1. Open WPS Spreadsheet: Launch WPS Office on your device and open a new or existing blank spreadsheet.
- 2. Import the CSV Data: Navigate to the 'Data' tab, click on 'Import Data', and select your target CSV file from your local storage.
- 3. Select the Delimiter: In the text import wizard, choose 'Delimited' and check the specific character (comma, space, or tab) that separates your data.
- 4. Finish the Import: Review the column preview at the bottom of the wizard and click 'Finish' to instantly populate your spreadsheet with cleanly separated data.
Frequently Asked Questions
Why does my CSV open in one column by default?
This typically happens if the CSV file uses a delimiter (like a semicolon) that differs from your computer's default regional list separator settings, causing Excel to fail at recognizing where to split the data.
How can I change the default delimiter in Windows?
You can change the system's default list separator by opening the Control Panel, navigating to 'Region', clicking 'Additional settings', and modifying the 'List separator' character to match your CSV files.
Can I use Text to Columns for data separated by spaces?
Yes. Select the column containing your data, go to Data > Text to Columns, choose 'Delimited', and check the 'Space' option as your delimiter to successfully split the text.
What if my CSV data has commas inside the text fields?
If text fields containing commas are wrapped in quotation marks, Excel's Text to Columns wizard can handle them. Ensure the 'Text qualifier' dropdown in the wizard is set to the quotation mark (") so Excel ignores commas inside quotes.
