How to Open a Pipe-Delimited CSV in Excel Without Comma Splitting
Question details
The user needs to open a pipe-delimited CSV file in Excel without commas within the text fields causing incorrect column breaks.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing a CSV file that uses pipe characters (|) as delimiters instead of commas.
- Observed behavior
- Excel automatically parses commas as delimiters when opening the .csv file, which disrupts the intended columns and formatting.
Ensure you have file extensions visible in your operating system settings so you can easily modify the .csv extension before importing the file into Excel.
Rename the File and Use the Text Import Wizard
By changing the file extension from .csv to .txt, you prevent Excel from automatically parsing commas. This forces the Text Import Wizard to open, allowing you to manually define the pipe character as the data separator.
Excel defaults to treating files with a .csv extension as comma-separated values, applying automated formatting immediately upon opening. Renaming the file to a standard text format gives you full control over the import delimiters.
Locate your pipe-delimited .csv file in File Explorer. Right-click the file, choose 'Rename', and change the '.csv' extension to '.txt'. Confirm any prompt that warns you about changing file extensions.
Launch Microsoft Excel. Go to 'File' > 'Open', browse to the folder containing your renamed file, ensure the file type dropdown is set to 'All Files (*.*)' or 'Text Files (*.prn;*.txt;*.csv)', and select your .txt file.
The Text Import Wizard will automatically launch. On Step 1, select 'Delimited' as the original data type and click 'Next'.
On Step 2, uncheck 'Tab' or 'Comma' if they are selected. Check the 'Other' box and type a pipe character (|) into the adjacent field. Verify that the Data Preview window now splits your columns correctly.
Click 'Next' to adjust specific column data formats if necessary, then click 'Finish'. Your data will now be correctly imported without any comma splitting issues.
Easily Import Delimited Data with WPS Spreadsheet
WPS Spreadsheet offers a highly intuitive Data Import tool that allows you to specify custom delimiters, like the pipe character, instantly—without needing to rename file extensions beforehand.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank spreadsheet.
- 2. Access the Data Import feature: Navigate to the 'Data' tab on the top ribbon menu and click on 'Import Data'.
- 3. Select your CSV file: Choose 'Import Data' from the dialog box and browse your computer to select your original pipe-delimited .csv file.
- 4. Specify the custom delimiter: In the Text Import Wizard that appears, select 'Delimited', click 'Next', check 'Other', input the pipe character (|), and click 'Finish' to perfectly format your data.
Frequently Asked Questions
Why does Excel automatically split my CSV by commas?
By default, Excel associates the .csv (Comma Separated Values) extension with comma delimiters. When opening these files directly via a double-click, Excel skips the import wizard and automatically uses commas to create columns, which breaks the layout if your file relies on a different delimiter.
Can I use Power Query to import pipe-delimited CSVs in Excel?
Yes. You can use Power Query by going to Data > Get Data > From File > From Text/CSV. Select your file, and in the preview window, change the 'Delimiter' dropdown to 'Custom', type the pipe character, and click 'Load'. This method also avoids the need to rename the file extension.
Can I set the pipe character as the default list separator in Windows?
Yes. You can change your system's default list separator by going to Windows Control Panel > Region > Additional settings, and changing the 'List separator' field to a pipe (|). Be aware that this change will affect how all applications on your computer handle list formatting.
