Fix Excel CSV File Importing into One Column in RStudio
Question details
The user needs to import a CSV file created in Excel into RStudio, but the data is incorrectly merged into a single column instead of separating into distinct columns.

- Product
- Excel, RStudio
- Device & OS
- not provided
- Scenario
- Importing a spreadsheet dataset into a statistical programming environment for data analysis.
- Observed behavior
- The CSV file displays correctly with multiple columns in Excel, but it loads as one combined column when imported into RStudio.
Open your CSV file in a plain text editor like Notepad or TextEdit before importing it into RStudio. This allows you to visually confirm whether commas or semicolons are being used to separate your data.
Adjust the Delimiter Setting in RStudio
The most common reason for this issue is a mismatch between the delimiter used in the CSV file and the one RStudio expects.
When Excel saves a CSV file, it relies on your computer's regional settings. If your region uses a comma as a decimal separator, Excel will use a semicolon as the column delimiter. By default, RStudio expects a comma, which causes it to read semicolon-separated data as a single column.
Open RStudio and navigate to the Environment pane. Click on 'Import Dataset' and select 'From Text (base)' or 'From Text (readr)'.
Browse your computer and select the CSV file that was created in Excel.
In the Import Options dialog box, locate the 'Delimiter' or 'Separator' dropdown menu. Change it from 'Comma' to 'Semicolon' (or vice versa, depending on what your file uses).
Check the Data Preview window to ensure the columns are now separated correctly, then click 'Import'.

Save and Import as an XLSX File
If adjusting delimiters is problematic, you can bypass CSV formatting issues by saving your data as an Excel Workbook and importing it directly.
Prepare Your Data for RStudio with WPS Spreadsheet
WPS Spreadsheet provides a streamlined way to view, format, and export your datasets as CSV or XLSX files, ensuring seamless compatibility when importing data into RStudio or other analytical tools.
- 1. Open your dataset: Launch WPS Spreadsheet and open your data file.
- 2. Format the data: Ensure your columns are clean, properly labeled, and formatted without merged cells.
- 3. Export the file: Go to 'Menu' > 'Save As', and select either 'CSV (Comma delimited)' or 'Microsoft Excel Workbook (*.xlsx)'.
- 4. Import into RStudio: Use RStudio's import tool to seamlessly load your formatted dataset.

Frequently Asked Questions
Why does Excel save my CSV with semicolons instead of commas?
This happens due to your computer's regional settings. If your region uses a comma for decimal points (common in many European countries), Excel automatically uses a semicolon as the list separator to avoid confusing decimals with column breaks in CSV files.
Can I change the default CSV delimiter in Excel?
Yes, you can change this by modifying your system's regional settings. In Windows, go to the Control Panel, select 'Region', click 'Additional settings', and change the 'List separator' character to a comma.
Is it better to use CSV or XLSX for RStudio imports?
CSV files are plain text and generally load faster, making them ideal for very large datasets. However, XLSX files retain original formatting and bypass delimiter confusion completely, which can be more convenient for smaller or more complex datasets.
How do I fix delimiter issues on a Mac?
On a Mac, you can check your system's region settings by going to System Settings > General > Language & Region. Alternatively, you can open the CSV file in the 'TextEdit' application, use 'Find and Replace' to change semicolons to commas, and then import it into RStudio.




